ListView Control Tutorial.
Continued from last week's ActiveX ListView Control Tutorial-01.
In this session, we will learn how to search for specific rows and column values in the ListView control and display the search results in a Label control on the form. This capability is particularly useful when working with large volumes of data. We will also examine several important ListView property settings.
To begin, we will see how easily columns can be rearranged in the ListView control, similar to the column arrangement available in the Access Datasheet View. To support this demonstration, additional controls—including TextBoxes, ComboBoxes, CommandButtons, and Labels—were added to the Form to facilitate search parameter selection and search results.
For this demonstration, I have made a slight modification to last week's sample data. The values in the first column are now sourced from the Employees table in the Northwind sample database. A query was created to combine the LastName and FirstName fields into a single field, aliased as Student, while the EmployeeID field is used as the Key (for example, X01, X02, and so on).
Before moving on to search operations, let us first explore how to rearrange columns in the ListView control using the drag-and-drop feature.
Note: If you have not completed the previous tutorial and would like to continue directly with this session, download the demo database from the ListView Control Tutorial-01 page. Extract the ZIP file, open the database, and you will find the demo form ready in Normal View.
Open the database containing the demo form from the previous session (or the one you created) and switch the form to Normal View.
Now, let us rearrange a column by dragging it from the middle of the list. For example, drag the Weight column and drop it in the position of the Age column. The expected result is that the Age column shifts one position to the right, making room for the Weight column.
Move the mouse pointer over the Weight column header, then press and hold the left mouse button. You will notice that when the button is depressed, the column header moves slightly downward. While holding the mouse button, drag the column to the left and drop it onto the Age column header.
At this point, you will notice that nothing happens. This is because the required property setting has not been enabled. In fact, enabling a single property is all that is required for this feature to work.
To enable it:
-
Switch the form to Design View.
-
Right-click on the ListView control, highlight the ListViewCtrl Object option, and select Properties.
On the Properties window, you will find the option AllowColumnReorder on the right side. Put a check mark to enable it, then click Apply, and select the OK button to close the Properties window.
Now, repeat the drag-and-drop steps explained earlier and observe the result. This simple setting is all that’s required to enable column reordering in the ListView control.
You may be asking: What about rearranging rows?
Unlike columns, row reordering requires additional programming using event procedures—similar to the drag-and-drop techniques we implemented earlier in the TreeView control. We will cover that part later in this tutorial series.For now, feel free to experiment by moving any column, even the first column, to any position you like.
Note: Before dropping the source column, ensure that the target column is fully covered by the highlighted frame of the incoming column. If not, the column may shift to the next position on the right instead of replacing the intended target.
Next: Searching for Information in the ListView
Now, let’s move on to learning how to quickly search for information within the ListView—especially useful when working with large volumes of data.
For this purpose, we have added a subroutine to the Tutorial-01 module. This subroutine loads the column header names into a ComboBox on the form, which is displayed with a red background for visibility. The selected column name is used to search and retrieve specific values (such as Age, Height, Weight, or Class) for a student.
New VBA Code Added to the Form’s Class Module
A new VBA procedure has been added to the Class Module of last week’s Tutorial Form.
This procedure populates the txtColCombo ComboBox with a list of column header labels (field names). These labels correspond to the data fields in the ListView, such as Age, Height, Weight, or Class.
During the search-and-find operation, one of these column values can be selected to retrieve the corresponding detail for a student, along with the student’s name.
Private Sub txtColCombo()
'Column Header List Combo
Dim lvwColHead As MSComctlLib.ColumnHeader
Dim cboName As ComboBox
Set cboName = Me.txtCol
cboName.RowSourceType = "Value List"
For Each lvwColHead In lvwList.ColumnHeaders
If lvwColHead.Index = 1 Then
'Nothing
Else
cboName.AddItem lvwColHead.Text
End If
Next
'cboName.DefaultValue = "=txtCol.Column(0, 0)"
Set lvwColHead = Nothing
Set cboName = Nothing
End Sub
The ComboBox will not have a default value for the column header name. If a column is selected, the corresponding value for the student will be displayed in the large Label below the student’s name. If left blank, the search operation will return only the student’s name.
The search operation is flexible and fast, supporting two main methods:
-
Search by providing text – The search text can come from any column, either as a full match or as a partial string from the left. Since each row in the ListView control has two types of object members—ListItem (first column) and ListSubItems (remaining columns)—the search operation treats these separately.
-
Search options via an option group – Next to the search-text input TextBox, an option group with two checkboxes allows you to choose where to search:
-
First option (default): Searches the first column (ListItem) for the given text.
-
Second option: Searches within the ListSubItem columns.
-
Note: Rearranging columns affects only their visual position, not the object type. Dragging a ListSubItem into the first column does not convert it into a ListItem.
To retrieve a value from a specific column, select the column name from the ComboBox located below the search-text TextBox. For example, to find a student’s Height, select Height from the ComboBox.
After setting the search criteria, click the Find Item Command Button. If the search is successful, the result will appear in the large Label control below the button.
The [Find Item] Command Button Click.
Calls the SearchAndFind() Procedure.
Private Sub SearchAndFind()
'Find by Student Name
Dim lstItem As MSComctlLib.ListItem
Dim strFind As String
Dim strColName As String
Dim strColVal As String
Dim j As Integer
Dim intOpt As Integer
Dim msgText As String
Me.Refresh
intOpt = Me.Opts
strFind = Nz(Me![txtFind], "")
strColName = Nz(Me![txtCol], "")
Select Case intOpt
Case 1
Set lstItem = lvwList.FindItem(strFind, , , lvwPartial)
If Not lstItem Is Nothing Then
j = lstItem.Index
'format the display text
msgText = lvwList.ColumnHeaders.Item(1).Text
msgText = msgText & " : " & lstItem.Text & vbCr & vbCrLf
Else
MsgBox "Text '" & strFind & "' Not Found!", vbOKOnly + vbCritical, "cmdFind_Click()"
Exit Sub
End If
Case 2
Set lstItem = lvwList.FindItem(strFind, lvwSubItem, , lvwPartial)
If Not lstItem Is Nothing Then
'format the display text
j = lstItem.Index
msgText = lvwList.ColumnHeaders.Item(1).Text
msgText = msgText & ": " & lstItem.Text & vbCr & vbCrLf
Else
MsgBox strFind & " Not Found!", vbOK + vbCritical, "cmdFind_Click()"
Exit Sub
End If
End Select
If Len(strColName) = 0 Then 'If column name is not selected
GoTo nextStep
Else
'Get the column value
strColVal = GetColVal(lstItem, strColName)
msgText = msgText & String(8 - (Len(strColName)), " ") & _
strColName & ": " & Nz(strColVal, "")
End If
nextStep:
If Len(msgText) > 0 Then 'assign to form label
lvwList.ListItems.Item(j).Selected = True
lblMsg.caption = msgText
End If
End Sub
At the beginning of the program, the Student Name and the optional Column Name are copied from their respective TextBoxes into the variables strFind and strColName, following validation checks.
Note: The Column Name ComboBox has its Not-in-List property set to Yes. This means you may either select a valid value from the list, type it in, or leave it blank. However, if you type in a value that does not exist in the list, it will not be accepted.
Depending on the selected search option (1 – ListItem or 2 – ListSubItem), the scan process is moved to the appropriate object(s).
Using either method, the program locates the ListItem object (row) that contains the search text. The ListItem Index value is then saved in the variable J for later use in the program.
Note: The ListView control automatically assigns index numbers when items are first populated.
Once found, the ListItem.Text value is retrieved. This value is combined with the first column header’s text (for example, Student: Robert King) and stored in the MsgText string, which is then displayed in the Label control on the form.
If a column header name is selected in the ComboBox, the program calls the GetColVal() function, passing the ListItem object and the selected column header text as parameters. This feature is especially useful for retrieving additional details about a student, such as their Height, directly from the record.
The GetColVal() Function VBA Code.
Private Function GetColVal(lvwItem As MSComctlLib.ListItem, ByVal colName As String) As String
Dim i As Integer
Dim strVal As String
'first column is student name
'check for column value from 2nd column onwards
For i = 2 To lvwList.ColumnHeaders.Count
If lvwList.ColumnHeaders(i).Text = colName Then 'if col name matches
strVal = lvwItem.ListSubItems.Item(i - 1).Text 'get column value
Exit For 'No further scanning required
End If
Next
GetColVal = strVal 'return the retrieved the value
End Function
The GetColVal() function requires two parameters:
-
The ListItem object, which contains the student’s name.
-
The Column Name to be retrieved.
The student’s details—such as Age, Height, Weight, and Class—are stored in the ListItem.ListSubItems collection. The function scans through the lvwList.ColumnHeader values to locate the matching column name.
Once a match is found, the corresponding column index is used to retrieve the value from the ListSubItems object and returned to the calling procedure.
The [Find By Key] Command Button Click Event Procedure.
We have introduced another method to find a Student’s Name (or related information) by using the Unique Key Value assigned to a list item at the time of creating the list.
Although assigning a Key is optional, add a Unique Key String Value (note: the key must begin with an alphabet character). This approach makes searching more efficient.
For example, when dealing with personal identification records, the following can serve as the Key:
-
Social Security Number
-
National Identity Card Number
-
Passport Number
-
Driving License Number
Using identifiers as the ListItem Key makes it much faster and easier to locate a record compared to the conventional search-by-text method.
The cmdKey_Click() Event Procedure.
Calls FindByKey() Subroutine.
Private Sub FindByKey()
Dim colHeader As MSComctlLib.ColumnHeader
Dim lvItem As MSComctlLib.ListItem
Dim lvKeyVal As String
Dim lvColName As String
Dim txt As String
Dim msgText As String
Dim varcolVal As Variant
lvKeyVal = UCase(Nz(Me!txtKey, ""))
lvColName = Nz(Me!txtCol, "")
If len(lvKeyVal) > 0 then
On Error Resume Next
Set lvItem = lvwList.ListItems.Item(lvKeyVal) 'get the item by Key
If Err > 0 Then
Err.Clear
MsgBox "Key Value: '" & lvKeyVal & "' Not Found!", vbOKOnly + vbCritical, "cmdKey_Click()"
On Error GoTo 0
Exit Sub
End If
Else
MsgBox "Please Provide a Valid Key-Value!",vbOKOnly + vbCritical, "cmdKey_Click()"
Exit Sub
End If
txt = lvItem.Text 'get the student name
'format message text
msgText = lvwList.ColumnHeaders.Item(1).Text & " : "
msgText = msgText & txt & vbCr & vbCrLf
If Len(lvColName) > 0 Then 'if column name is given
varcolVal = GetColVal(lvItem, lvColName) 'get column val of student
msgText = msgText & String(8 - Len(lvColName), " ") & lvColName & ": " & varcolVal ' add it to display
End If
lvItem.Selected = True 'highlight the item on form
Me.lblMsg.caption = msgText 'assign details to form Label
End Sub
As shown in the subroutine, we can directly locate the ListItem containing the Student’s name by using the Key value in a single statement:
The next line retrieves the ListItem.Text (the Student’s name) into the variable txt. The following two lines then build the message string by inserting the Student’s name into the variable msgText.
Next, an If…Then statement checks whether a Column Name has been entered in the ComboBox control. If a valid column is found, the program calls the GetColVal() function with the appropriate parameters to retrieve the value from that column. The returned value is stored in the variable varColVal and passed back to the calling procedure.
Finally, the Column Name and the retrieved value are appended to the msgText string, which is then displayed in the Label control on the Form.
The next statement highlights the Student’s record row as a visual cue that the searched item has been found. At the same time, the value stored in msgText is displayed in the Label control by setting its Caption property on the Form.
The Full VBA code on the Form Module.
Option Compare Database
Option Explicit
Dim lvwList As MSComctlLib.ListView 'ListView Control
Dim lvwItem As MSComctlLib.ListItem '
Dim ObjImgList As MSComctlLib.ImageList
Const prfx As String = "K"
Private Sub Form_Load()
Call LoadListView
Call txtColCombo
End Sub
Private Function LoadListView()
'Populate the ListView control with Student Details
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim intCounter As Integer
Dim strKey As String
'Assign ListView Control on Form to lvwList Object
Set lvwList = Me.ListView1.Object
With lvwList
.AllowColumnReorder = True
.Enabled = True
.Font = "Verdana"
.Font.Bold = True
.Font.Size = 9
.ForeColor = vbBlack
.BackColor = vbWhite
End With
'Create Column Headers for ListView
With lvwList
.ColumnHeaders.Clear 'initialize header area
'Syntax: .ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
.ColumnHeaders.Add , , "Student", 2500
.ColumnHeaders.Add , , "Age", 1200
.ColumnHeaders.Add , , "Height", 1200
.ColumnHeaders.Add , , "weight", 1200
.ColumnHeaders.Add , , "Class", 1200
End With
'Initialize ListView Control
While lvwList.ListItems.Count > 0
lvwList.ListItems.Remove (1)
Wend
'Student Names and Ids are taken from Employees Table
'through the StudentQ Query.
Set db = CurrentDb
Set rst = db.OpenRecordset("StudentQ", dbOpenDynaset)
With lvwList
Do While Not rst.EOF And Not rst.BOF
intCounter = rst![EmployeeID]
strKey = "X" & Format(intCounter, "00") 'Key Value sample: X01
'Syntax: .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
Set lvwItem = .ListItems.Add(, strKey, rst![Student])
With lvwItem
'Syntax: .Add Index,Key,Text,Report Icon,TooltipText
.ListSubItems.Add , strKey & CStr(intCounter), CStr(5 + intCounter)
.ListSubItems.Add , strKey & CStr(intCounter + 1), CStr(135 + intCounter)
.ListSubItems.Add , strKey & CStr(intCounter + 2), CStr(40 + intCounter)
.ListSubItems.Add , strKey & CStr(intCounter + 3), ("Class:" & Format(intCounter, "00"))
End With
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
Set lvwItem = Nothing
End With
lvwList.Refresh
End Function
Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Name
End Sub
Private Sub cmdFind_Click()
Call SearchAndFind
End Sub
Private Sub cmdKey_Click()
Call FindByKey
End Sub
Private Function GetColVal(lvwItem As MSComctlLib.ListItem, ByVal colName As String) As String
Dim i As Integer
Dim strVal As String
'first column is student name
'check for column value from 2nd column onwards
For i = 2 To lvwList.ColumnHeaders.Count
If lvwList.ColumnHeaders(i).Text = colName Then 'if col name matches
strVal = lvwItem.ListSubItems.Item(i - 1).Text 'get column value
Exit For 'No further scanning required
End If
Next
GetColVal = strVal 'return the retrieved the value
End Function
Private Sub txtColCombo()
'Column Header List Combo
Dim lvwColHead As MSComctlLib.ColumnHeader
Dim cboName As ComboBox
Set cboName = Me.txtCol
cboName.RowSourceType = "Value List"
For Each lvwColHead In lvwList.ColumnHeaders
If lvwColHead.Index = 1 Then
'Nothing
Else
cboName.AddItem lvwColHead.Text
End If
Next
'cboName.DefaultValue = "=txtCol.Column(0, 0)"
Set lvwColHead = Nothing
Set cboName = Nothing
End Sub
Public Sub SearchAndFind()
'Find by Student Name
Dim lstItem As MSComctlLib.ListItem
Dim strFind As String
Dim strColName As String
Dim strColVal As String
Dim j As Integer
Dim intOpt As Integer
Dim msgText As String
Me.Refresh
intOpt = Me.Opts
strFind = Nz(Me![txtFind], "")
strColName = Nz(Me![txtCol], "")
Select Case intOpt
Case 1
Set lstItem = lvwList.FindItem(strFind, , , lvwPartial)
If Not lstItem Is Nothing Then
j = lstItem.Index
'format the display text
msgText = lvwList.ColumnHeaders.Item(1).Text
msgText = msgText & " : " & lstItem.Text & vbCr & vbCrLf
Else
MsgBox "Text '" & strFind & "' Not Found in the List!", vbOKOnly + vbCritical, "cmdFind_Click()"
Exit Sub
End If
Case 2
Set lstItem = lvwList.FindItem(strFind, lvwSubItem, , lvwPartial)
If Not lstItem Is Nothing Then
'format the display text
j = lstItem.Index
msgText = lvwList.ColumnHeaders.Item(1).Text
msgText = msgText & ": " & lstItem.Text & vbCr & vbCrLf
Else
MsgBox strFind & " Not Found!", vbOK + vbCritical, "cmdFind_Click()"
Exit Sub
End If
End Select
If Len(strColName) = 0 Then 'If column name is not selected
GoTo nextStep
Else
'Get the column value
strColVal = GetColVal(lstItem, strColName)
msgText = msgText & String(8 - (Len(strColName)), " ") & _
strColName & ": " & Nz(strColVal, "")
End If
nextStep:
If Len(msgText) > 0 Then 'assign to form label
lblMsg.caption = msgText
lvwList.ListItems.Item(j).Selected = True
End If
End Sub
Public Sub FindByKey()
Dim colHeader As MSComctlLib.ColumnHeader
Dim lvItem As MSComctlLib.ListItem
Dim lvKeyVal As String
Dim lvColName As String
Dim txt As String
Dim msgText As String
Dim varcolVal As Variant
lvKeyVal = UCase(Nz(Me!txtKey, ""))
lvColName = Nz(Me!txtCol, "")
On Error Resume Next
If Len(lvKeyVal) > 0 Then
Set lvItem = lvwList.ListItems.Item(lvKeyVal) 'get the item by Key
If Err > 0 Then
Err.Clear
MsgBox "Key Value: '" & lvKeyVal & "' Not Found!", vbOKOnly + vbCritical, "cmdKey_Click()"
On Error GoTo 0
Exit Sub
End If
Else
MsgBox "Please Provide a Valid Key-Value!", vbOKOnly + vbCritical, "cmdKey_Click()"
Exit Sub
End If
txt = lvItem.Text 'get the student name
'format message text
msgText = lvwList.ColumnHeaders.Item(1).Text & " : "
msgText = msgText & txt & vbCr & vbCrLf
If Len(lvColName) > 0 Then 'if column name is given
varcolVal = GetColVal(lvItem, lvColName) 'get column val of student
msgText = msgText & String(8 - Len(lvColName), " ") & lvColName & ": " & varcolVal ' add it to display
End If
lvItem.Selected = True 'highlight the item on form
Me.lblMsg.caption = msgText 'assign details to form Label
End Sub
Download the Demo Database from the following Link:
LVTutorial02.zip- Microsoft TreeView Control Tutorial
- Creating an Access Menu with a TreeView Control
- Assigning Images to TreeView Nodes
- Assigning Images to TreeView Nodes-2
- TreeView Control Checkmark Add Delete
- TreeView ImageCombo Drop-down Access
- Rearrange TreeView Nodes By Drag and Drop
- ListView Control with MS-Access TreeView
- ListView Control Drag-and-Drop Events
- TreeView Control With Sub-Forms
Download DeskShortcut.zip
