When I originally designed the Order form, I was simply using Text Boxes to display as well as update the data. However, I wanted to make the form more professional looking and user-friendly. I also wanted to be able to bring up a list of Customers; and in choosing a Customer from the list, display all of that Customer's Orders. Subsequently, selecting an Order from the second list, I wanted to bring up the Order Detail information for that particular Order. The ListView Control afforded that functionality with a professional look and continuity of format.
To accomplish this task, I am using three ListView Controls on the Orders Form. Each of the ListView Controls' View Properties is set to Report (lvwReport). The only other properties (3) which I've set are the Enabled, FullRowSelect and Grid Lines, which are all set to True.
For Example:
ListViewCustomers.ColumnHeaders.Add , , "ID", ListViewCustomers.Width / 3, lvwColumnCenter
After adding the Column Headers, I create a Key for the ListView to uniquely identify each record in the Recordset.
NOTE: The Key must be a STRING Datatype; and the first character must be Alpha. As I will use the CustomerID, which is an Integer, as the Key, I will simply concatenate an "A" onto the beginning of the CustomerID to insure that it is of a String Datatype to retrieve its value. If I choose to subsequently use the Key relative to retrieving another recordset (i.e., the Orders for a particular Customer), it will be necessary to strip off the "A" at that point.
First, I create a variable to hold the CustomerID from the Recordset, add the "A" to it and use it as the Key for the ListView. Based on this Key, I will add the other data from the Recordset to the ListView.
NOTE that I will use the Text Property of the Key, or CustomerID, to display in the first column as a ListItem. The remaining items in the Recordset are added to the ListView as SubItems.
Lastly, as I have created a Key, it will enable movement through the items in the ListView. Subsequently, the Recordset object is no longer needed and can be closed.
The FillLVCustomers Sub Procedure described above is listed below in its entirety.
Public Sub FillLVCustomers()ListViewCustomers.ColumnHeaders.Clear
ListViewCustomers.ListItems.Clear
ListViewCustomers.Visible = True
ListViewCustomers.Enabled = True
With rsCustListLV
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CacheSize = 50
.Source = "Select CustomerID, LastName, FirstName from Customer"
.ActiveConnection = conn
.Open
End With
ListViewCustomers.ColumnHeaders.Add , , "ID", 903
ListViewCustomers.ColumnHeaders.Add , , "Last Name", 2300, lvwColumnCenter
ListViewCustomers.ColumnHeaders.Add , , "First Name", 2301, lvwColumnCenter
Dim i As Integer
For i = 0 To rsCustListLV.RecordCount - 1
Set LICustomerID = ListViewCustomers.ListItems.Add(, "A" & rsCustListLV!CustomerID, rsCustListLV!CustomerID)
If Not IsNull(rsCustListLV!CustomerID) Then
LICustomerID.SubItems(1) = rsCustListLV!LastNameLICustomerID.SubItems(2) = rsCustListLV!FirstName
End If
rsCustListLV.MoveNext
Next i
rsCustListLV.Close
Set rsCustListLV = Nothing
End Sub
I have provided functionality for viewing the orders for a particular customer in the Double Click Event of the Customer ListView. When a customer is selected, I make the original ListView Invisible and subsequently the OrderNumbers ListView is made Visible:
Private Sub ListViewCustomers_DblClick()ListViewCustomers.Visible = False
ListViewCustomers.Enabled = False
ListViewOrderNumbers.Visible = True
ListViewOrderNumbers.Enabled = True
To determine which Customer was selected from the Customer ListView, I will use the Key. It is necessary at this point to strip off the "A" which was originally concatenated onto the CustomerID. This value will be assigned to a variable and subsequently used to select only the Orders for a particular Customer.
Dim m As Integerm = Mid(ListViewCustomers.SelectedItem.Key, 2, Val(Len(ListViewCustomers.SelectedItem.Key)))
With rsCustOrdersLV
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.CacheSize = 50
.Source = "Select Customer.CustomerID, Customer.LastName, " & _
"Customer.FirstName, Orders.OrderID, Orders.Status from " & _
"Customer, Orders where Customer.CustomerID = " & m & " and Orders.customerID = " & m & " Order By CustomerID"
.ActiveConnection = conn
.Open
End With
This ListView showing the Orders for a particular Customer will be populated in the same manner as the previous ListView:
ListViewOrderNumbers.ColumnHeaders.Add , , "ID", 900ListViewOrderNumbers.ColumnHeaders.Add , , "Last Name", 1500, lvwColumnCenter
ListViewOrderNumbers.ColumnHeaders.Add , , "First Name", 1500, lvwColumnCenter
ListViewOrderNumbers.ColumnHeaders.Add , , "Order ID", 1000, lvwColumnCenter
ListViewOrderNumbers.ColumnHeaders.Add , , "Status", 900, lvwColumnCenter
Dim i As Integer
For i = 0 To rsCustOrdersLV.RecordCount - 1
Set LICustID = ListViewOrderNumbers.ListItems.Add(, "A" &
rsCustOrdersLV!OrderID, rsCustOrdersLV!CustomerID)
If Not IsNull(rsCustOrdersLV!CustomerID) Then
LICustID.SubItems(1) = rsCustOrdersLV!LastNameLICustID.SubItems(2) = rsCustOrdersLV!FirstName
LICustID.SubItems(3) = rsCustOrdersLV!OrderID
LICustID.SubItems(4) = rsCustOrdersLV!Status
End If
rsCustOrdersLV.MoveNext
Next i
If rsCustOrdersLV.RecordCount = 0 Then
MsgBox "There are no orders for this customer"End If
rsCustOrdersLV.Close
Set rsCustOrdersLV = Nothing
End Sub