Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

ListView Control Drag-Drop Sort Events

Rearranging Rows of Data in ListView Control.

In the earlier episode of this Tutorial, we have learned how to rearrange the Columns by enabling this feature: AllowColumnReorder option on the Property Sheet. But, relocating a row is done by dragging and placing it on another row. For rearranging the ListView control rows, the ListItem Drag and Drop action requires enabling this feature on the Property Sheet. But this alone will not work, needs VBA Code to rearrange the item to its required order.

Let us create a sample Access Form with controls and VBA Code in our database for this exercise. The sample image of the Form with ListBox and ListView Controls is given below.

We have created a list of Tables and Queries (not Action Queries) in the List Box. Selecting one of the list items will display the records instantly on the ListView control, as we see them in the DataSheet view.

The Design Task.

  1. Create a new Table with a single Text field, with the field name DataList.

  2. Save the Table with the name lvTables (lv stands for ListView).

  3. Open the Table in Datasheet View.

  4. Add a few table names and Select Query names from your database into the Table. I have imported Tables from the Northwind sample database for my list.

    Note: The Attachment Field is not valid in ListView Control. Create Select Queries for tables with attachment field and select all fields except Attachment field. 

  5. Create and open a new Form in Design View.

  6. Insert a ListBox Control on the form, display the Property Sheet and change its Name property value to List0.

  7. Change its child label Caption value to Tables.

  8. Display the Property Sheet of ListBox control and set the Row Source property value to the lvTables name.

  9. Check whether the Row Source Type is set as Table/Query and the Bound Column property value is 1.  If different then change.

  10. Insert a ListView Control from the ActiveX Controls List and change its Name Property Value to ListView1.

  11. Resize both the controls as shown on the demo Form image given above.

  12. Insert a Label above the Controls and change its Name and Caption Property Values to Heading.  The Caption value will be changed from vba code when a Table or Query is selected from the ListBox.

  13. Create a Command Button below the Controls and change its Name property value to cmdClose and the Caption property value to Close.

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

  15. Change the property settings to match with the settings in the General Tab image given below.

  16. ListView Control Property Sheet image - General tab view is given below:

    ListView Control Property Sheet

    Some of these options we have already set in the earlier sessions. Here we need the following options for our Drag Drop action:

    • OLEDragAutomatic - 1

    • OLEDropManual - 1

    • FullRowSelect - True

    • HotTracking - True

Ensure that the above settings match with your property sheet then save the Form.

Display the VBA Module of the Form.

The Form Module VBA Code.

Copy and Paste the following VBA Code into the Module, overwriting the existing Code Lines, if any:

Option Compare Database
Option Explicit

Dim lvwList As MSComctlLib.ListView
Dim strTable As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Private Sub Form_Load()
    Set lvwList = Me.ListView1.Object

End Sub


Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Form_Unload_Err
Dim lvItem As ListItem
Dim tmp As Long
Dim criteria As String
Dim strfield As String
Dim flag As Boolean
Dim fld As String

If strTable = "" Then
Set lvwList = Nothing
    Exit Sub
End If

Set db = CurrentDb
Set rst = db.OpenRecordset(strTable, dbOpenDynaset)
flag = False
For Each lvItem In lvwList.ListItems
    tmp = lvItem.Index
    strfield = lvwList.ColumnHeaders(1).Text
    criteria = strfield & " = " & Chr(34) & lvItem.Text & Chr(34)
    
    rst.FindFirst criteria
  
    If Not rst.NoMatch Then
       If (rst.Fields(strfield).Value = lvItem.Text) _
       And (rst.Fields(1).Value = tmp) Then
         'GoTo nextitem
       Else
            rst.Edit
            rst.Fields(1).Value = tmp
            rst.Update
       End If
    Else
        MsgBox "Item: " & tmp & " Not Found!"
    End If
Next
rst.Close

Set lvwList = Nothing
Set lvItem = Nothing
Set rst = Nothing
Set db = Nothing

Form_Unload_Exit:
Exit Sub

Form_Unload_Err:
MsgBox Err & " : " & Err.Description, , "Form_Unload()"
Resume Form_Unload_Exit

End Sub

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As Object)
' When a ColumnHeader object is clicked, the ListView control
' sorts the data of that column. On the first Click on the Column
'will sort in Ascending Order, second Click will sort in Descending
With Me.ListView1
    ' Set the SortKey to the Index of the ColumnHeader - 1
    .SortKey = ColumnHeader.Index - 1
    
' Set Sorted to True to sort the list.
 If .SortOrder = lvwAscending Then
    .SortOrder = lvwDescending
 Else
    .SortOrder = lvwAscending
 End If
 
    .Sorted = True
End With

End Sub

Private Sub List0_Click()

strTable = List0.Value

Call LoadListView(strTable)

End Sub

Private Sub LoadListView(ByVal s_Datasource As String)
On Error GoTo LoadListView_Err
    Dim j As Integer
    Dim tmpLItem As MSComctlLib.ListItem
    Dim strHeading As String
    
    strHeading = UCase(s_Datasource)
    With Me.Heading
        .caption = strHeading
        .FontName = "Courier New"
        .FontSize = 20
        .FontItalic = True
        .FontBold = True
    End With
    
   'Initialize ListView Control
    lvwList.ColumnHeaders.Clear
    lvwList.ListItems.Clear
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(s_Datasource, dbOpenSnapshot)
       
    'Initialize ListView & Column Headers Property Values
     With lvwList
        .Font.Size = 10
        .Font.Name = "Verdana"
        .Font.Bold = False
        .GridLines = True
    End With
    
    With lvwList
        'Syntax: .ColumnHeaders.Add Index, Key, Text, Width in Pixels, Alignment, Icon
       For j = 0 To rst.Fields.Count - 1
        .ColumnHeaders.Add , , rst.Fields(j).Name, IIf(j = 0, 3000, 1400), 0
       Next
    End With
   Dim I As Long
    rst.MoveFirst
    Do While Not rst.BOF And Not rst.EOF
    'Syntax: lvwList.ListItems.Add Index, Key, Text, Icon, SmallIcon
        Set tmpLItem = lvwList.ListItems.Add(, , rst.Fields(0).Value) 'Name column
        
         'Syntax: tmpLItem.ListSubItems.Add Index, Key, Text, ReportIcon, ToolTipText
          With tmpLItem
                For j = 1 To rst.Fields.Count - 1
                    .ListSubItems.Add , , Nz(rst.Fields(j).Value, "")
                Next
          End With
        rst.MoveNext
    Loop
    rst.Close
    
    With lvwList
        If .ListItems.Count > 0 Then
            .ListItems(1).Selected = True
        End If
    End With
  
    Set db = Nothing
    Set rst = Nothing
    
LoadListView_Exit:
Exit Sub

LoadListView_Err:
MsgBox Err & " : " & Err.Description, , "LoadListView()"
Resume LoadListView_Exit
End Sub


Private Sub ListView1_OLEDragOver(data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single, State As Integer)
    'Highlight the item when draged over it
    Set ListView1.DropHighlight = ListView1.HitTest(x, y)

End Sub

Private Sub ListView1_OLEDragDrop(data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)

'Item being dropped
Dim lvwDrag As ListItem
'Item being dropped on
Dim lvwDrop As ListItem
'Item being readded to the list
Dim lvwTarget As ListItem
'Subitem reference in dropped item
Dim lvwSub As ListSubItem
'Drop position
Dim intTgtIndex As Integer
Dim j As Integer

Set lvwDrop = lvwList.HitTest(x, y)
Set lvwDrag = lvwList.SelectedItem 'save a copy of draged item

'Ignore overlapping drag or drop Item actions
If (lvwDrop Is Nothing) Or (lvwDrag Is Nothing) Or (lvwDrop = lvwDrag) Then
    Set lvwList.DropHighlight = Nothing
    Set lvwDrop = Nothing
    Set lvwDrag = Nothing
    Exit Sub
End If

'Save the droped position Index Number
intTgtIndex = lvwDrop.Index
'Remove Dragged Item from its old position
lvwList.ListItems.Remove lvwDrag.Index

'For j = intTgtIndex To ListItems.Count
    
'Creates a new Item in the Target Item position
'with the Dropped Item Index Number and Dragged Item.Text.
'Saves the new Item reference in lvwTarget Item.

'* The original Droped-on Target) Item will be moved down
'* by incrementing its original Index Number
Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text)

'Copy the original Draged Item's subitems to the new item
If lvwDrag.ListSubItems.Count > 0 Then
    For Each lvwSub In lvwDrag.ListSubItems
        lvwTarget.ListSubItems.Add , lvwSub.Key, lvwSub.Text
    Next
End If

'Highlight the draged item in its new position
lvwTarget.Selected = True

'Destroy all objects
Set lvwTarget = Nothing
Set lvwDrag = Nothing
Set lvwDrop = Nothing
Set lvwList.DropHighlight = Nothing

End Sub

Private Sub cmdClose_Click()
    DoCmd.Close acForm, Me.Name
End Sub

You are familiar with the above VBA Code except the newly added subroutines: ListView1_OLEDragOver(), ListView1_OLEDragDrop(), Form_Unload(), and the ListView1_ColumnClik() procedures. The first two procedures will help us drag an Item (row) and Drop it over another Item to insert it into a new location. The procedures Form_Unload() and ListView1_ColumnClick() will sort the Items.

The following Images show the Drag and Drop action in sequences of its execution

The first image below shows the drag and drop action sequence. The ListItem, with EmployeeID 7, is dragged up by the user and drops over the ListItem with ID 3.

The second image shows the move of ListItem in reverse order.

When the mouse pointer moves over a row with the dragged item, between the source and target rows,  will highlight one after the other on the way up.

The Drag and Drop Action in Pictures.

Row on the Move
The row with Employee ID 7 is dropped on the Item with Employee ID 3 above.
Record Moved up and Dropped

The VBA Code Segment-wise Analysis.

  An item selection from the ListBox the List0_Click() event procedure runs and loads the records into the ListView Control.

Private Sub List0_Click()
Dim strTable As String

strTable = List0.Value

  Call LoadListView(strTable)

End Sub

The selected Table/Query name saves in the strTable string variable. The LoadListView() subroutine runs with the strTable variable as the parameter. We have gone through this Code more than once in earlier sessions, and you may visit those Pages using the Links provided at the bottom of this page for details. You may find a few minor changes I have made in this Code.

We have not used the ImageList Control in this episode the Icon, SmallIcon Parameter values in the ListItems.Add() Method and ReportIcon, TooltipText parameter values in the ListSubItems.Add() method also not used.

Let us look at what is happening in the ListView1_OLEDragOver() and ListView1_OLEDragDrop() VBA Code Segments.

The ListView1_OLEDragOver() Procedure.

Private Sub ListView1_OLEDragOver(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single, State As Integer)
    'Highlight the item when draged over it
    Set ListView1.DropHighlight = ListView1.HitTest(x, y)
End Sub 

This procedure automatically executes when you attempt to click and hold on a row, start to drag and move over other rows on the way towards the target row. The drag action moves over another row it will get highlighted.

The ListView1.HitTest(x, y) function reads the x, y coordinates that determines the row position on the ListView Control and highlights that row. This process continues when you are over other rows till you drop it on the target row by releasing the mouse button. The drop action triggers the ListView1_OLEDragDrop() procedure and executes the source row's change over procedures.

The ListView1_OLEDragDrop Procedure.

Private Sub ListView1_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)

'Item being dragged
Dim lvwDrag As ListItem
'Item being dropped on
Dim lvwDrop As ListItem
'Item being added to the list
Dim lvwTarget As ListItem
'Subitem reference used in For . . .Next loop
Dim lvwSub As ListSubItem 'Drop position index Dim intTgtIndex As Integer Set lvwDrop = lvwList.HitTest(x, y) 'save the source item Set lvwDrag = lvwList.SelectedItem 'save a copy of draged item 'Ignore overlapping drag or drop Item actions If (lvwDrop Is Nothing) Or (lvwDrag Is Nothing) Or (lvwDrop = lvwDrag) Then Set lvwList.DropHighlight = Nothing Set lvwDrop = Nothing Set lvwDrag = Nothing Exit Sub End If 'Save the droped position Index Number intTgtIndex = lvwDrop.Index 'Remove Dragged Item from its old position lvwList.ListItems.Remove lvwDrag.Index 'Creates a new Item in the Target Item position 'with the Dropped Item Index Number and Dragged Item.Text. 'Saves the new Item reference in lvwTarget Item. '* The original Droped-on Target) Item will be moved down '* by incrementing its original Index Number Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text) 'Copy the original Draged Item's subitems to the new item If lvwDrag.ListSubItems.Count > 0 Then For Each lvwSub In lvwDrag.ListSubItems lvwTarget.ListSubItems.Add , lvwSub.Key, lvwSub.Text Next End If 'Highlight the draged item in its new position lvwTarget.Selected = True 'Destroy all objects Set lvwTarget = Nothing Set lvwDrag = Nothing Set lvwDrop = Nothing Set lvwList.DropHighlight = Nothing End Sub

Let us review this procedure part-by-part and understand what is happening there. The following Code Segment declares necessary Object Variables to handle the Drag and Drop action:

'Item being dragged
Dim lvwDrag As ListItem
'Item being dropped on
Dim lvwDrop As ListItem
'Reference of the Item being added to the list
Dim lvwTarget As ListItem
'Subitem reference used in For . . .Next loop
Dim lvwSub As ListSubItem
'Drop position index
Dim intTgtIndex As Integer

Set lvwDrop = lvwList.HitTest(x, y)
Set lvwDrag = lvwList.SelectedItem 'save a copy of draged item

The first three ListItem temporary Objects declares with different names.

The lvwDrag ListItem object will hold the copy of the row we pick to drag to a new location.

The lvwDrop ListItem Object will save the reference of the row on which we drop the dragged list item.

During the change-over of ListItems action, we will delete the Source item from its original location, then creates it in the target location, with the source ListItem Index number. The references of this new ListItem saves in the lvwTarget ListItem object variable.

The lvwSub Variable declared as a sequencing Object Variable in the For . . .Next Loop. This looping requires to sequence through the ListSubItems, (2nd Column onwards) one-by-one, from the lvwDrag object. Even though we have deleted the original ListItem we have saved a copy of it in the lvwDrag ListItem object.

The lvwDrop ListItem Index number is saved in the intTgtIndex Variable.

The lvwList.HitTest(x, y) Function reads the x, y coordinates of the ListView Control and identifies the target ListItem where we have dropped the source ListItem and makes a copy of it in lvwDrop Object.

We will select a ListItem first before we drag it to the new position.

The lvwList.SelectedItem Property will be set as True. With the help of this property status, we make a copy of the selected ListItem into the lvwDrag ListItem Object. The next Code segment validates both Source and Target ListItem Objects.

Validation Checks on the Drag-Drop Action.

'Ignore overlapping drag or drop Item actions, 
'OR drag and drop happens on the same ListItem.
If (lvwDrop Is Nothing) Or (lvwDrag Is Nothing)  Or (lvwDrop = lvwDrag) Then
    Set lvwList.DropHighlight = Nothing
    Set lvwDrop = Nothing
    Set lvwDrag = Nothing
    Exit Sub
End If

The above code segment validates the drag and drops action. If these actions did not start or end on a valid item then the lvwDrop or lvwDrag Objects or both of them will be empty. Or another invalid move can happen as the user moves a row up or down but may change his mind and drop it back on the same row.  Detection of these kinds of wrong moves will terminate the program.

If the above test proves valid then the program will continue executing the next procedure to rearrange the Rows.

'Save the dropped position ListItem Index Number
intTgtIndex = lvwDrop.Index

'Remove Dragged Item from its old position
lvwList.ListItems.Remove lvwDrag.Index

'Creates a new Item in the Target Item position
'with the Dropped Item Index Number and Dragged Item.Text.
'Saves the new Item reference in lvwTarget Item.

'* The original Droped-on Target) Item will be moved down
'* by incrementing its original Index Number
Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text)

'Copy the original Draged Item's subitems to the new item
If lvwDrag.ListSubItems.Count > 0 Then
    For Each lvwSub In lvwDrag.ListSubItems
        lvwTarget.ListSubItems.Add , lvwSub.Key, lvwSub.Text
    Next
End If

'Highlight the draged item in its new position
lvwTarget.Selected = True

The above nine lines of executable Code (other lines are comments) actions are somewhat straightforward.

The intTgtIndex = lvwDrop.Index statement saves the target ListItem's Index number in intTgtIndex Variable.

Since we have already saved the Source Row listItem in the temporary Object lvwDrag, the next step is to remove the source ListItem from the ListView Control. The ListItems.Remove() procedure is called, with the statement lvwList.ListItems.Remove lvwDrag.Index.

In short, the Drag Drop action is to delete a ListItem from its original location and create it again at the target location with the target row index number.

The statement Set lvwTarget = lvwList.ListItems.Add(intTgtIndex, , lvwDrag.Text) creates the new ListItem with its target location index number intTgtIndex and the Text value of Source ListItem saved in the lvwDrag Object earlier.

While creating the ListItem for the first time, we have used only these two values, the index, and the Text parameter values. We have not used the other parameter options Key, Icon, and SmallIcon otherwise we must include those parameter values also from the lvwDrag object.

As per our Drag Drop example Images shown above we have moved the 7th ListItem and dropped it on the 3rd ListItem. After that, we have deleted the 7th Item (or source ListItem) from the ListView Control. Created a new ListItem with the target index number 3.

Now, there are two items with the same Index number 3, the existing one with the index number 3 and the new one we have created with index number 3. All other information is taken from the lvwDrag Object (or the 7th ListItem saved in lvwDrag Object earlier).

The System automatically increments the existing ListItem 3 onwards to the next sequence numbers 3,4,5 . . . to 4,5,6 . . . and moves them forward to give space for the incoming item to insert in between.

The Impact of Delete a Row and Create it elsewhere. 

Assume that we make that move in the reverse order, like drag ListItem number 3 from above and drop it on item number 7 then what happens?

Naturally, we will delete the 3rd Item and will attempt to create a new item with index number 7 in the new location. When Item number 3 is deleted item number 4 onwards will shift up or 4,5,6,7,8,9 will become 3,4,5,6,7,8 (to make all the items in sequence) or the earlier item with index number 7 will become 6.

When we create the new Item with index number 7 the existing 7,8 will become 8,9 again. When we watch the movement of rows while row deletion and creation time the first example will move the target row down to make way for the incoming item. In the second example explained (moving 3 to 7) the destination row will move up.

Note: Watch the Employees ID value for its placing as a clue for ListItem shifting down or moving up when we rearrange ListItem.

I have mentioned ListItem everywhere in drag-drop operations. The ListItem refers to the first Column of the ListView Row only. Other column values are ListSubItems or the child-items of the ListItem. That means you will be able to Drag and Drop the first Column only. Other Columns or ListSubItems will be moved under the ListItem with VBA Code.

ListItem Column Drag and Drop

This is true if you have not enabled the FullRowSelection on the ListView Control Property Sheet on the General Tab.

ListItem Column Dropped

If enabled you can select any column, but the System refers to the ListItem Index for Rows re-order purposes.  Compare the above two images with another set of two sample images, the third and fourth images from the top of this page.

The Drag and Drop action will not work if the following two property values are not Set on the ListView Control Property Sheet on the General Tab.:

  • ccOLEDragAutomatic = 1
  • ccOLEDropManual = 1

The next five statements will move the ListSubItems, if any, to the ListItem newly created in the new location.

Next, the newly created ListItem is highlighted.

Next, all temporary objects created are cleared from memory.

Note: Another important point to note here is that this arrangement is a temporary one and is lost when you close the Form or load another Table/Query on the ListView Control. 

If we want the changed order of ListItems to remain permanent, or until the order is changed next time, then we must be able to update the current indexed order number on the table itself.  We have added a new Integer field with the field-name ID on the Employees Table. 

The sample screen with the Employees data rearranged in Alphabetical order is given below:

Since the Employees ID field is an AutoNumber field and linked with other related tables we have added a new Number Field with the field name ID. This field value is set initially with the same sequence numbers from the Employees ID manually.  This field value will be initially in this order.  But, the ListView Rows data may change their order when you rearrange the data on the ListView Control due to Drag and Drop action.

Look at the EmployeesQ Query SQL given below:

SELECT [FirstName] & " " & [LastName] AS EmployeeName, 
Employees.ID, 
Employees.EmployeeID, 
Employees.TitleOfCourtesy, 
Employees.Title, 
Employees.Address, 
Employees.City, 
Employees.Region, 
Employees.PostalCode, 
Employees.Country, 
Employees.HomePhone, 
Employees.Extension, 
Employees.Notes
FROM Employees
ORDER BY Employees.ID;

The above Query is used as Data Source for the ListView Control and they are sorted on the ID Field. The ID field is updated with the changed order of Index Numbers on the ListView Control.  The updating process runs from the Form_Unload() Event Procedure when you close the Form.  This method ensures that when you open the ListView Control next time the data will be in the order you reordered last time.

The Form_Unload() Event Procedure VBA Code.

Private Sub Form_Unload(Cancel As Integer)
Dim lvItem As ListItem
Dim tmp As Long
Dim criteria As String
Dim strfield As String
Dim fld As String

If strTable = "" Then
Set lvwList = Nothing
    Exit Sub
End If

Set db = CurrentDb
Set rst = db.OpenRecordset(strTable, dbOpenDynaset)

For Each lvItem In lvwList.ListItems
    tmp = lvItem.Index
    strfield = lvwList.ColumnHeaders(1).Text 'EmployeeName
    criteria = strfield & " = " & Chr(34) & lvItem.Text & Chr(34)
    
    rst.FindFirst criteria
  
    If Not rst.NoMatch Then
       If (rst.Fields(strfield).Value = lvItem.Text) And (rst.Fields(1).Value = tmp) Then
         'GoTo nextitem
       Else
            rst.Edit
            rst.Fields(1).Value = tmp 'replace ID number
            rst.Update
       End If
    Else
        MsgBox "Item: " & tmp & " Not Found!"
    End If
Next
rst.Close

Set lvwList = Nothing
Set lvItem = Nothing
Set rst = Nothing
Set db = Nothing

End Sub

Check the EmployeeName Field Value in the above image. They are arranged in Alphabetical order.  The new ID field value on the Employees Table will be updated with their current ListView Control ListItem index number sequence.

If you note the following points you can easily understand what we do with the above code:

  1. The ListItem's (first column) Text parameter value is the employee name and arranged in Alphabetical Order.

  2. The ListItems on the ListView Control has index numbers from 1 to 9 in the order it is shown on the screen, i.e. the first item's index number is 1 and the last one is 9. The original data on the Employees Table ID field value is not in this order.

  3. We take the Text Value (Employee Name) of the first ListItem and search for the name on the table.

  4. When the record is found the current ListItem's Index number is updated (replaced) on the ID field on the table. 

  5. This process was repeated for all the remaining records on the table. 

Let us go through the VBA Code. In the beginning, we check whether the Source data Table/Query was loaded into the ListView Control or not? 

If the strTable Variable is not initialized with the Query name then the ListView Control is empty. If this is the case then the user opened the Form and closed it without selecting the Query name to load the data into the ListView control.  The Form_Unload Event Procedure is aborted at this point and closes the form.

If the ListView control has data then the next step is executed and opens the Source data query  EmployeesQ to update. 

The next step is to go through each ListItem and update the index number in the ID field of the Employees record.

First, the current row index number is saved in the tmp Variable.

The first lvwList.ColumnHeader name EmployeeName and the employee's name is taken from the ListItem.Text into an expression in the Criteria string variable, like EmployeeName = "Andrew Fuller".

The rst.FindFirst Criteria command searches the Source data table to find the record with the given name.  When the record is found the current ListItem Index number is updated on the ID Field.

This process is repeated for all the rows on the ListView Control and when finished the Form is closed.

Next time you load the records from this Query into the ListView Control they will be displayed in the same order when you closed the form last time.

Note: The Query became necessary here to sort the data on the ID field and display them in the changed order on the ListView Control.  

All this work was for saving the data in the last sorted order so that next time you open the Form the data on the ListView Control will be in that order.

Windows Explorer Like Sorting Method.

In Windows Explorer, you can sort the displayed list in Ascending or Descending Order by clicking on any Column Heading. The Column Header will work like a Toggle Button. Repeated clicks on the Column Header will Sort the column data in Ascending/Descending Order by the following ListView1_ColumnClick() Event Procedure:

Private Sub ListView1_ColumnClick(ByVal ColumnHeader As Object)
' When a ColumnHeader object is clicked, the ListView control is
' sorted by the subitems of that column.

With Me.ListView1
' Set the SortKey to the Index of the ColumnHeader - 1
    .SortKey = ColumnHeader.Index - 1
    
 If .SortOrder = lvwAscending Then
    .SortOrder = lvwDescending
 Else
    .SortOrder = lvwAscending
 End If
 ' Set Sorted to True to sort the list.
     .Sorted = True
End With
End Sub

Note: The Sorting of all data is in text compare mode only.  The ListItems and ListSubItems Add() method's third Parameter, the displayed information on the ListView Control is Text type. Date and Numeric Values are all treated as Text only.

Windows Explorer saves the last sorted order of items in the folder.  When we open that folder again the list will be displayed in the earlier sorted order. 

With the Form_Unload() Event Procedure this feature of Windows Explorer become possible on the Employees Table.  When you close the Form after sorting on any column that indexed order sequence will be saved on the Employees Table in the ID field.  The EmployeesQ Query always sorts the data on the ID field when opened.

The Demo database is attached for Downloading.  There are two demo forms in the Database.  The first Form demonstrates the opening of Tables and Queries in the ListView Control to view the data in Datasheet View. The second form uses only the EmployeesQ Query alone for Drag, Drop, Sort, and saving of last sort order of data for future use.



  1. ActiveX ListView Control Tutorial-01.
  2. ListView Control Tutorial-02.
  3. Assigning Images To ListView Items.
  4. ListView Control Drag-Drop Sort Events
  5. ListView Control With MS-Access TreeView
  6. TreeView/ListView Controls Drag-Drop Events
Share:

Assigning Images To ListView Items Tutorial-03

The ImageList ActiveX Control.

For Assigning Images to the ListView control, we need the help of another ActiveX Control: the ImageList Control.  We have seen Icons in Windows Explorer view, Folder-like images in the folder-closed state, when you click on the folder it displays an open Folder image and different types of images based on the file type. We don't have that much flexibility here, but we can display Icon images in the ListView Control's ColumnHeaders, ListItems, and ListSubItems object members while populating their contents. 

The Sample Demo Images.

The sample image of NorthWind Trading Employees listing with their Photos (small images) in the ListView Control:

Larger image sizes will increase the row height of records, but the photos can be larger than this.

A sample Image of ListView Control is given below (in the right-side Panel) that we have used along with the TreeView Control. The TreeView ActiveX Control was introduced to you in an earlier Series of Tutorials on TreeView Control.  You can find the List of TreeView Control Tutorial Series links at the bottom of this page.

In the above picture, I have used Icon images in all data columns and on column header labels to demonstrate the possibility of image display on ListView Control. 

The folder close and open states are displayed in the left panel on TreeView Control Nodes and they work differently than on ListView Items. The folder-open image is displayed when the TreeView Node receives a Click. A second click on the same Node displays the folder-closed image.

The ListView Control Programming Tutorial Series.

Hope you have gone through the ListView Control's earlier Tutorial Sessions-1 and 2 and are ready to take up this new Episode on the usage of ImageList Control along with the ListView Control.  The earlier Tutorial links are given below for review and to get ready to continue with this Session. Some of the basics of ListView Control are already presented and explained there with VBA Code and you will be in a better position to continue here and understand what is happening here.

  1. ListView Control Tutorial-01.

  2. ListView Control Tutorial-02.

Source Data and Demo Form.

Let us start with a new Form and the Employees Table for our new ListView Control Demo Project.  Import the Employees Table from the NorthWind.accdb sample Database.  

  1. Create a new SELECT Query with the SQL given below and save it with the name EmployeesQ.

    SELECT [TitleOfCourtesy] & " " & [FirstName] & " " & [LastName] AS [Employee Name], 
    Employees.EmployeeID, 
    Employees.Title, 
    Employees.HireDate, 
    Employees.Address, 
    Employees.City, 
    Employees.Region, 
    Employees.PostalCode, 
    Employees.Country, 
    Employees.HomePhone, 
    Employees.Extension, 
    Employees.Notes
    FROM Employees;
    
  2. If your Employees Table structure is different doesn't matter.  For the first Column value only I have combined three column values together to form the [Employees Name] as the first Column. Other column name values you can take as you have them and in any order, all of them or fewer as you please.

  3. Create a new Form and open it in Design View.

  4. Insert a Microsoft ListView Control from the ActiveX Controls List.

  5. Insert a Microsoft ImageList Control also from the ActiveX Controls List.

  6. Resize the ListView control like the sample image on the Form given below.  Move the ImageList Control and place it at the top right corner of the ListView control as shown in the image. You can place it anywhere in a convenient location on the Form.  It will not appear on the Form when the Form is in Normal view.

    • Select the ListView Control and display the Property Sheet.

    •  Change the Name Property Value to ListView1. 

    • Select the ImageList Control, display its Property Sheet, and change the Name Property value to ImageList0.

    • Note: Both the above controls have their own dedicated Property Sheets.  Their property names and values may appear in Access Property Sheet also.  If we make some changes in the Access Property Sheet then all of them may not update on the ListView and ImageList Controls. We have to make changes to the Control's own Property Sheet.

      ListView Control Property Sheet.

  7. Right-Click on the ListView Control, highlight the ListViewCtrl Object option in the displayed list, and select Properties.  The General Tab of the ListView Control Property Sheet will look like the Image given below.  

  8. Change the Property Values on the General Tab as shown in the Image above.

    First of all, we will load the Employees' data in the ListView Control.

    The Form Module VBA Code

  9. Copy and Paste the following VBA Code into the Form's Class Module:

    Option Compare Database
    Option Explicit
    
    Dim lvwList As MSComctlLib.ListView
    Dim lvwItem As MSComctlLib.ListItem
    Dim ObjImgList As MSComctlLib.ImageList
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    
    Private Sub cmdClose_Click()
       DoCmd.Close acForm, Me.Name
    End Sub
    
    Private Sub Form_Load()
     Call LoadListView("EmployeesQ")
    End Sub
    
    Private Sub LoadListView(ByVal tblName As String)
        Dim strFldName As String
        Dim intCounter As Integer
        Dim j As Integer
        Dim strLabel As String
    
    'Assign ListView Control on Form to lvwList Object
    Set lvwList = Me.ListView1.Object
    'Set ObjImgList = Me.ImageList0.Object
        
    'Assign Form Header labels Caption Text
     strLabel = UCase(tblName) & " " & "IN LISTVIEW CONTROL - TUTORIAL-03"
     Me.Label8.caption = strLabel
     Me.Label9.caption = strLabel
     
     With lvwList
        '.Icons = ObjImgList
        '.SmallIcons = ObjImgList
        '.ColumnHeaderIcons = ObjImgList
        .Font = "Verdana"
        .Font.Size = 10
        .Font.Bold = True
     End With
     
     Set db = CurrentDb
     Set rst = db.OpenRecordset(tblName, dbOpenSnapshot)
     
     'Create Column Headers for ListView
     With lvwList
        .ColumnHeaders.Clear 'initialize header area
        For j = 0 To rst.Fields.Count - 1
            strFldName = rst.Fields(j).Name
       'Syntax:
       '.ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
            .ColumnHeaders.Add , , strFldName, iif(j=0,3200,2000)
        Next
     End With
     
     'Initialize ListView Control
      While lvwList.ListItems.Count > 0
            lvwList.ListItems.Remove (1)
      Wend
    
     With lvwList
     Do While Not rst.EOF And Not rst.BOF
    
       'Syntax  .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
            Set lvwItem = .ListItems.Add(, , CStr(Nz(rst.Fields(0).Value, "")))
            
       'Add next columns of data as sub-items of ListItem
            With lvwItem
       'Syntax     .Add Index,Key,Text,Report Icon,TooltipText
             For j = 1 To rst.Fields.Count - 1
                .ListSubItems.Add , , CStr(Nz(rst.Fields(j).Value, ""))
             Next
    
           End With
           rst.MoveNext
    Loop
    rst.Close
        'reset lvwItem object
        Set lvwItem = Nothing
    End With
    
    Set rst = Nothing
    Set db = Nothing
    End Sub
    
    

    Note:  The Red-Colored VBA lines of ImageList control are Commented out from executing for the time being and we will enable them shortly.

  10. Save your Form with the Name frmEmployees.

  11. Open the Form in Normal View.

    The EmployeesQ Query Records Listing will look like the following Image:

Review of the VBA Code

We have already gone through the above VBA Code in the earlier ListView Control Tutorial-01 and 02  Sessions, except for a few lines on ImageList Control declaration, initialization, and a few lines for the Font-Name, Font-Size, and Font-Style settings.  Another change we have made in the earlier VBA Code here is the LoadListView() program, it needs a Table/Query Name as a parameter.  All Query Types, except Action Queries, Access Tables, and Linked Table Names are Valid. The Table or Query name is passed when the program is called from the Form_Load() Event Procedure.

All the Table/Query Field Names are used as ColumnHeader Label Text (the third parameter) in the ColumnHeaders.Add() method. The first parameter Index and second parameter Key Values are not used. The Index sequence numbers will be inserted by the system automatically. 

The fourth parameter is the Column-Width value in pixels and we have arbitrarily assigned the first column width value of 3200 pixels and for all other columns 2000 pixels.  The first column displays the Employee Name and it needs more width to display it properly.

The Alignment and Icon parameter values for Column Headers we have not used here.  By default, the left Alignment is assumed. The available Alignment options are given below.

  • 0 - lvwColumnLeft
  • 1 - lvwColumnRight
  • 2 - lvwColumnCenter

You can view the above options on the Column Headers Tab on the ListView Control Property Sheet. To view the above options:

  • Click on the Insert Column Button, enter some temporary column-name in the Text Box below.

  • Click on the Alignment Property and view the above Options.  

  • Click on the Remove Column Button to Delete the temporary column name. 

  • Note: If you would like to add Column Header Labels manually, rather than loading field names through VBA Code, you can type them one by one here. They will appear as Column Header Labels when you display data.

The sample view of the Icon Image at the left side of the Header Column Names can be seen in the right-side panel in the second demo image on the top of this page.

EmployeesQ first Column (Employee Name) is taken as the ListItems.Text in its Add Method. In this method also we have omitted the Index and Key Parameter values. Index numbers will be added automatically by the system as serial numbers.

From the second field onwards all column values are loaded through the ListSubItems.Add() method  of the ListView Control.

Note: All the values are added to the ListItems.Text and in ListSubItems.Text parameter as Text data type only, irrespective of its original data type in the source Table/Query.  In the Code, we are performing a validation check on Field Values, just in case any of them contains a Null Value, and convert it into text value with the CStr() built-in function.

The ImageList control.

The ImageList Control initializing statements we have commented out in the Main Program are shown in red color in the Code segment given below. We will explain and enable them when we are ready with our preparations for uploading Images into the ImageList Control.

'Assign ListView Control on Form to lvwList Object
Set lvwList = Me.ListView1.Object
'Set ObjImgList = Me.ImageList0.Object
    
'Assign Form Header labels Caption Text
 strLabel = UCase(tblName) & " " & "IN LISTVIEW CONTROL - TUTORIAL-03"
 Me.Label8.caption = strLabel
 Me.Label9.caption = strLabel
 
 With lvwList
    '.Icons = ObjImgList
    '.SmallIcons = ObjImgList
    '.ColumnHeaderIcons = ObjImgList
    .Font = "Verdana"
    .Font.Size = 10
    .Font.Bold = True
 End With
 

The first statement with red color above initializes the ObjImgList Object with ImageList control ImageList0 on the Form frmEmployees. Before making changes to the Code let us see what options we have for uploading some images into the ImageList Control.

About Uploading Images.

The next step is to upload some sample images into the ImageList Control.  This can be done in one of two ways. 

Before attempting this step, please create or get at least two small images (any of the popular image types like .jpg, jpeg, .bmp, .png, etc.), preferably .bmp type. The Image size options available on the ImageList Control, on the General tab of the Property Sheet, are 16 x 16, 32 x 3248 x 48 pixels, or Custom size.  

Right-Click on the ImageList Control, highlight the option ImageListCtrl Object, and select Properties. Before selecting any image for uploading select one of the above image sizes on the General Tab.  

  • If you have big Images and want to retain the original Image-Size then select Custom Option. 
  • Selecting any of the other options will reduce the Image to the selected size. This may reduce the image quality. Using very large images may occupy more space on the ListView Control when displayed.  
  • Icon-type images will be more ideal to use.  
  • Experiment with big, small, and very small images and with different options to get some experience on the correct Image/Option selection for your needs.

You can use one of two ways to upload the Images into ImageList Control:

1. Upload Images from disk through VBA Procedure.

The sample VBA Procedure will look like the Code Segment given below, taken from TreeView Control Tutorial:

  
  Set objImgList = Me.ImageList0.Object
  objImgList.ListImages.Clear
  
strFolder = "D:\Access\TreeView\"
With objImgList
    With .ListImages
         .Add Index:=1, Key:="FolderClose", Picture:=LoadPicture(strFolder & "folderclose2.bmp")
         .Add Index:=2, Key:="FolderOpen", Picture:=LoadPicture(strFolder & "folderopen2.bmp")
         .Add Index:=3, Key:="ArrowHead", Picture:=LoadPicture(strFolder & "arrowhead.bmp")
    End With
End With

With tvw 'TreeView Control
    .ImageList = objImgList 'assign imagelist Object to TreeView Imagelist Property
End With

The first statement initializes the objImgList Object with ImageList0 control on the Form.

The next statement ensures that the existing images in the image list control, if any, are cleared in preparation for uploading from the disk. For this approach to work every time, the images must be always available on the disk.

The objImgList.ListImages.Add() method is called to upload images from disk using the named parameters.  When parameter names are used in the Add() method the parameter values can be given in any order like the Index:=1 can be given at the end of the line or Key:="FolderClose" as the first item and so on. Without the parameter names the Add() method parameters order will be as follows:

         .Add 1, "FolderClose", LoadPicture(strFolder & "folderclose2.bmp")

To display the Image on our listView control we can either use the Image Index Number 1  or the Key value "FolderClose" Text as the Icon or SmallIcon parameter values in the ListItems.Add() method.

We have used the above method in the TreeView Control Tutorial earlier.  You may visit that Page and download the Demo Database.

This method loads the Images into the ImageList Object Instance in memory and the physical object on the form is not changed.  The Source Images on the Disk must be always available every time the frmEmployees is open.

2. Uploading Images from disk Manually.

This is a one-time exercise, finding the images on disk and uploading them into the ImageList Control.  

The main advantage is that once the images are uploaded into the ImageList Control they stay intact.  The ImageList control with Images can be copy-pasted for other Projects if the same images are required for more than one Project. Not necessary to load the images from Disk again. The ImgeList Control with Images can be shared with friends as well.

So, let us go for the better method of manual uploading of Images.  Create two .bmp images of 50 x 50 pixels resolution (image1.bmp, image2.bmp) and keep it ready in your folder, say D:\Access\ for reference.

  1. Open frmEmployees in design view.

  2. Right-Click on the ImageList Control, highlight ImageListCtrl Object option and select Properties.

  3. On the General tab select the Custom Option to upload images with the original resolution.

    The General tab view of ImageList Control.

    The Images tab View of the ImageList Control

    Note: After trying out the uploaded images on the ListView control if you would like to try out other options 48 x 48, 32 x 32, 16 x 16 you must Remove all the uploaded images first then go to the General tab, select the required option and then upload the images again.  The selected images will be reduced to the selected image size.

    As you can see in the sample images tab I have uploaded two images by selecting Insert Picture Command Button and picking the images from my disk.  

    The first image is in the selected state and slightly in the raised position.  The Index control shows the value 1 and the Key textbox shows the text First. The Index value will appear automatically but the Key value (some meaningful value that you can easily memorize and relate the image to the data) may enter manually.

    We can use either the Index number or the Key text value in the Icon and in the SmallIcon Parameter of ListItems.Add() method.

    Even if you plan to use the index number sequence then the Image uploading sequence must Sync with the data you plan to upload in the ListView Control, like Employees Name should match with their photos in the correct sequence. 

    A better method in Employees' case their First-name can be used as Key Text and very easy to relate to the record. Generalized images don't need to match with this kind of relationship checking but their Key names will help to indicate what they do, like folder_closed or folder_opened.

  4. Click on the Images tab.

  5. Click on Insert Picture and find your D:\Access\Image1.bmp image and select it, click the Open Button to upload the image into the ImageList Control.

  6. Type some text value in the Key textbox (the Key Values must be unique).

  7. Repeat steps 5 and 6 for the second image, type the Key-value.

    We are ready with our ImageList Control with sample images and ready to display them on the ListView Control.

    Assigning ImageList Object to ListView Object Properties.

    The following ListView Object Properties must be assigned with the ImageList Object in order to use the Image references on the ListView Control:

    1. ListView.ColumnHeaderIcons
    2. ListView.Icons
    3. ListView.SmallIcons

    The next step is to assign the ImageList Object to the required ListView Object in VBA Code through the Object Properties: lvwList.ColumnHeaderIcons, lvwList.Icons, lvwList.smallIcons before we are able to use the image references (Index or Key values) in the ColumnHeaders.Add(), ListItems.Add(), and ListSubItems.Add() methods. We have already added the required VBA Code in the main program and kept them disabled.  All we have to do is to enable those lines of Code by removing the Comment symbol from them and adding the required image references in the above Add method's parameters.

  8. Remove the comment symbols ( ' ) from all the four VBA Code lines shown above with red color in the LoadListView() Procedure. 
  9. Modify the following statements, shown with red-color in the main program LoadListView() as shown with Icon Index number 1 & 2 in the Icon and SmallIcon parameter positions respectively like in the Code segment with bold black letters given below:
     With lvwList
     Do While Not rst.EOF And Not rst.BOF
    
       'Syntax  .ListItems.Add(Index, Key, Text, Icon, SmallIcon)
           ' Set lvwItem = .ListItems.Add(, , CStr(Nz(rst.Fields(0).Value,"")))
           'Change to 
             Set lvwItem = .ListItems.Add(, , CStr(Nz(rst.Fields(0).Value,"")), 1, 2)
            
       'Add next columns of data as sub-items of ListItem
            With lvwItem
       'Syntax     .Add Index,Key,Text,Report Icon,TooltipText
             For j = 1 To rst.Fields.Count - 1
               ' .ListSubItems.Add , , CStr(Nz(rst.Fields(j).Value, ""))
               'Change to           
                 .ListSubItems.Add , , CStr(Nz(rst.Fields(j).Value, "")),,"Click"
             Next
    
           End With
           rst.MoveNext
    Loop
    rst.Close

Since you have only two images the First Image with index number 1 is used as the Icon Parameter and 2 is in the SmallIcon parameter position. The Icon Image is displayed only when you change the ListView display Option to 0 - lvwIcon.  In the ListSubItems.Add() method we have not added an image reference and for the next parameter Tool-Tip text "Click" is added. The Click Text will display when the mouse pointer rests on any of the columns, on the second column onwards.

After making the above changes in the VBA Code Save the Form frmEmployees with the changes.

Open the Form in Normal View.  The view should look like the sample Image on the top of this page.

The smallIcon will be visible in all other ListView Options. Check the sample ListView Images of Employees data given below.

0 - lvwIcon View

ListView Icon View

2 - lvwList View

The first Image on the top of this page is the 03 - lvwReport View. Only in this view, all column values are displayed in the DataSheet like display.

Change the Form into Design View.  Display the Property Sheet of the ListView Control. Change the View options and try out each view and find out how different views looks like.

Download the Demo Database.

 

  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
Share:

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, like 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.accdb sample database. Created a Query to join the LastName and FirstName Values with the field name Student and EmployeeID used as Key (X01, X02 ...).

ListView Tutorial-02 Screen View

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, 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, 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 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 Column Header name. If selected that column value of the Student is displayed in the Large Label below the student 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, but only their display position. Dragging a ListSubItem column and bringing it in 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, 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 in 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, 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 (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 is 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:



  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
Share:

Translate

PageRank

MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web

Newsletter


Feed

Subscribe in a reader
Your email address:

Delivered by FeedBurner

Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts ListView Control Query VBA msaccessQuery Calculation Event ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Android App Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code