Make your own free website on


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.

The First ListView:

When the form is initially loaded, all three ListView controls are made invisible. I then call the FillLVCustomers Sub Procedure which I've created to clear the ListView Control to insure that only fresh data will be displayed and then bring in a Recordset to list some Customer information. The Customers ListView is then made Visible. Column Headers for the ListView are then added for each Field in the Recordset.
NOTE that the first column in the ListView must be Left Aligned. You may align subsequent columns as you wish. Also, the Width of the Columns can be specified as a numeric value, which is the option I chose, or you can simply divide the ListView Control Width by the number of Columns you desire.
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.Visible = True

ListViewCustomers.Enabled = True

With rsCustListLV

.CursorLocation = adUseClient

.CursorType = adOpenKeyset

.LockType = adLockOptimistic

.CacheSize = 50

.Source = "Select CustomerID, LastName, FirstName from Customer"

.ActiveConnection = conn


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!LastName

LICustomerID.SubItems(2) = rsCustListLV!FirstName

End If


Next i


Set rsCustListLV = Nothing

End Sub

The Second ListView:

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 Integer

m = 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


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", 900

ListViewOrderNumbers.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!LastName

LICustID.SubItems(2) = rsCustOrdersLV!FirstName

LICustID.SubItems(3) = rsCustOrdersLV!OrderID

LICustID.SubItems(4) = rsCustOrdersLV!Status

End If


Next i

If rsCustOrdersLV.RecordCount = 0 Then

MsgBox "There are no orders for this customer"

End If


Set rsCustOrdersLV = Nothing

End Sub

The Third ListView:

ListViewOrderNumbers.Visible = False ListViewOrderNumbers.Enabled = False 'To Populate ListViewOrders with OrderItems 'based on an Order chosen from the ListViewOrderNumbers: ListViewOrders.Visible = True ListViewOrders.Enabled = True