ListView Control Tutorial-02

Introduction.

Continued from last week's ActiveX ListView Control Tutorial-01.

In this session of the Tutorial, we will learn how to search and find the particular row and column values and display them on a Label Control on Form. This is very useful when we have a large volume of data in the ListView control. We will also learn the usage of some ListView property settings.

First of all, we will see how easy is to rearrange the columns, as we do with Access Datasheet View the way we want them to be on the ListView Control. We have added some TextBoxes, ComboBox, Command Buttons, and Label for easier selection of search parameters and display of search results. 

I have made some changes to last week's demo data. The first column values I have taken from the Employees Table of Northwind sample database. Created a Query to join the LastName and FirstName Values with the field name Student and EmployeeID used as Key (X01, X02, and so on).

Before going for the search operations, we will check how to re-arrange columns by the drag and drop method.

Note: If you have not gone through the earlier Tutorial Page and would like to continue with this session, then go to the ListView Control Tutorial-01 Page and download the demo database from the bottom of that Page.

Unzip the file and open the Database. The Demo Form will be in Normal View.

1. Open your Database, with the last session's Demo Form, or the Form that you have created, and open it in Normal View.

   Now, we will try to drag and move a column from the middle of the list (say the Weight column), and drop it to the Age column and see what happens. What is expected to happen is that the Age column should shift to the right and insert the incoming column in its place.

2. Move the mouse pointer on the Column Header with the name Weight, and click and hold the left mouse button. When you depress the left mouse button the column header will move slightly down.

3. Now, try to drag the column to the left and drop it on the column Age.

   Nothing will happen, because we have not enabled this feature in the Property Sheet and that is the only setting, we need to change for this feature to work.

4. Change the Form in Design View.

5. Right-Click on the ListView Control and highlight the option ListViewCtrl Object and select Properties.

   

6. There is an option 'AllowColumnReorder' on the right side. Put the check mark to select it, then click Apply button followed by the OK button to close the Property View.

7. Now, try to repeat the above steps 2 and 3 above and see what happens. 

   That is the only setting you need to enable this feature on the ListView Control.  Perhaps you may be thinking, what about rearranging the rows?

   That function needs programming some Event Procedures as we did earlier in TreeView Control Drag-Drop Events. That part we will do after some time.

8. You may experiment with any column to move anywhere you like, including the first column as well.

Note: Before you drop the source column see that the target column is covered by the incoming column frame before attempting to drop.  Otherwise, the incoming column may shift to the next column position on the right side.

Next, we will learn how to find some information from the ListView quickly, assuming that we have a large volume of data in it.  

We have added a subroutine to Tutorial-01 Module to load the Column header Names into a Combo Box on the form with the red background color. The Column Name will be used to find the column value (Age, Height, Weight, or Class) of a student.

New VBA Code Added to the Form Class Module.

The following new VBA procedure is added to last week's Tutorial Form's Class Module: 

The txtColCombo creates the list of Column Header Labels (field names) in the ComboBox.  One of these details of the Student's Age, Height, Weight, or Class can be found along with the student's name as part of the search-and-find operation. 

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 be loaded with a default value of the Column Header name. If selected that column value of the Student is displayed in the Large Label below the student's Name. If it is left blank, the search operation will find the student's name only.

The search operation method is very flexible and quick.  We have two methods to find a record.

Find the record by providing the search text.  The search text can be from any of the columns either the text in full or partial few characters from the left. Since we have two categories of object members in a row in the ListView control: ListItem - the first column and other columns are ListSubItems.  The Text search operation on these objects is performed separately. 

An option group with two CheckBoxes is provided next to the search-text input TextBox on the Form to select the search-and-find options. The first option is selected by default and the search is performed on the first Column (ListItem) to look for the given text.

Select the second option to search the text in the ListSubItem columns.  

Note: Re-arranging the columns will not change the objects, only their display position. Dragging a ListSubItem column and bringing it into the first column, will not change it into a ListItem object.

If you want to retrieve an unknown value from a particular column, select a column name from the ComboBox given below the first TextBox on the Form for the search text. For example, if you don't know the Height measurement of a student and would like to find out, select the column name Height from the ComboBox. 

After setting the above value(s) click on the Find Item Command Button to go for the search operation.  If the search was successful, then the result will be displayed in the large Label control below the Command 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, both the Student Name and Column Name (0ptional), are copied from the TextBoxes into the Variables strFind and strColName respectively after validation checks.

Note: The column name Combo Box's Not-in-List Property is set to Yes.  You can select a valid Value from the list or type it in or leave the combo box blank. If you type in a different value that is not on the list, it will not be accepted.

Based on the search Option selected (1 - ListItem or 2 - ListSubItem) the scan method is directed to the specified Object(s). 

Using either one of these search methods will find the ListItem Object or row that contains the search text. The Index Value of the ListItem is saved in Variable J for later use in the program. 

Note: The system creates the index auto-numbers automatically at the time ListView control items are populated. 

The ListItem.Text value is retrieved.   This information is joined with the first ColumnHeader. Text (like Student: Robert King) and added into the Msgtext string to display in the Label control on the Form.

If the column Header Name is selected in the ComboBox, then the GetColVal() Function is called with the ListItem Object and the Column Header Text value as parameters. This option is good for retrieving unknown information about a Student, like the Height of the student, 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 above function asks for two parameters. The first parameter is the ListItem, where the Student's name is found.  The second parameter is the Column Name. The selected student's Age, Height, Weight, and Class values are stored in the ListItem.ListSubItems Objects.  The function looks through the lvwList.ColumnHeader values to find the matching column name, when found that column index number is used for retrieving column value from the ListSubItems Object and returns the value to the calling program.

The [Find By Key] Command Button Click Event Procedure.

We have another method added to find the Student's Name using the Unique Key-Value of ListItem if used while creating the ListItem List. Even though it is optional, it is better to add Unique Key String Value (which should start with an alphabet character) rather than ignore it.

For example, if we have to find somebody's information by their identification number like Social Security Number, National Identity Card Number, Passport Number or Driving License Number and so on, one of this information can be used as the Key value to the ListItem. Finding a record with this Unique Value is very easy and swifter rather than the above 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 you can see in the above subroutine we could directly find the ListItem where the Student's name is, with the use of the Key-value, with a single statement: Set lvItem = lvwList.ListItems.Item(xKeyVal).  

The Next line reads the ListItem Text (or name of the Student) into the Variable txt. The next two lines create the message text with the Student's Name in the msgText string variable.

The next If . . .Then statement checks whether a Column Name Value is entered in the combo box control. If it is found, then calls the GetColVal() Function with the required parameters to find the column value and retrieve it in varColVal Variable and returns to the calling program.  The Column Name and its value retrieved are added to the msgText string variable to display on the Label control on the Form.

The next statement highlights the record Row of the Student as a visual indication that the searched item is found in the row.  The msgText value is displayed in the Label's 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

1. Microsoft TreeView Control Tutorial
2. Creating Access Menu with TreeView Control
3. Assigning Images to TreeView Nodes
4. Assigning Images to TreeView Nodes-2
5. TreeView Control Checkmark Add Delete
6. TreeView ImageCombo Drop-down Access
7. Re-arrange TreeView Nodes By Drag and Drop
8. ListView Control with MS-Access TreeView
9. ListView Control Drag Drop Events
10. TreeView Control With Sub-Forms