USING THE VB LISTVIEW CONTROL


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

LICustomerID.SubItems(2) = rsCustListLV!FirstName

End If


rsCustListLV.MoveNext


Next i


rsCustListLV.Close

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

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


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



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

RETURN TO HOME PAGE