ListView Control Drag Drop Events Handling ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

We are already familiar with the drag-and-drop operations of the TreeView control, where nodes can be rearranged in MS Access. All base records for TreeView nodes come from a single Access table. When a node is moved, we simply update the ParentID field of the source record with the ID value of the target node. This will change the position of the node in the TreeView, without physically moving the record.

In this project, we extend the idea by introducing the ListView control to the right side of the TreeView. Here, we work with two different Access tables:

lvCategory – Stores category codes and descriptions.
lvProducts – Stores products under each category.

This approach helps to visualize the relationship between the two tables and to understand what needs to be changed when a product item (ListView entry) is moved from one category to another in the TreeView.

The lvCategory table contains 20 records representing the TreeView nodes, while the lvProducts table has 45 product records for the ListView. Each product record is linked to its category through the Category ID (CID), stored in the ParentID field of the product table. When a product is reassigned to a new category, this link updates immediately, and the ListView reflects the change.

The demo data used here was adapted from Microsoft Access’s sample Northwind database and split into two related tables.

Based on the ParentID field of the lvProducts table, we can filter and display all related products in the ListView whenever a category node is selected in the TreeView.

Topics Covered So Far

The main topics we have explored on the TreeView, ImageList, ImageCombo, and ListView controls in MS Access are listed below:

The ListView Drag-Drop Task.

When it comes to the ListView’s drag-and-drop operation, the process is much simpler compared to performing the same action entirely within the TreeView control. Since this action involves both the TreeView and the ListView controls, we can handle it using the TreeView0_OLEDragDrop() event with a small amount of VBA code.

Here’s how it works:

The ListView displays product items that belong to the currently selected TreeView category.
When a product needs to be shifted to another category, the user just drags it from the ListView and places it onto the target category node in the TreeView.
When this happens, the product record’s ParentID field is updated with the Category ID (CID) of the target node.
The product item is then automatically displayed under the new category’s list of products in the ListView.

This operation is designed as a one-way action—product items move from the ListView to a different TreeView category node. They are not dragged back in the reverse direction.

The screenshot below shows a trial run of this feature in the demo form frmListViewDrag:

In the above Image, the Beverages Category on the TreeView has been selected. The products belonging to the Beverages category have been listed in the ListView Control.

The ListView Control In Design View.

The List of Control names on the Form is given below:

TreeView Control: TreeView0
ListView Control: ListView0
ImageList Control: ImageList3
Command Button: cmdClose

The VBA Code on the frmListViewDrag’s Class Module:

Option Compare Database
Option Explicit

Dim tv As MSComctlLib.TreeView
Dim lvList As MSComctlLib.ListView
Dim imgList As MSComctlLib.ImageList
Const Prfx As String = "X"

Private Sub Form_Load()
Dim db As DAO.Database
Dim tbldef As TableDef

    Set tv = Me.TreeView0.Object
    tv.Nodes.Clear
    
    Set imgList = Me.ImageList3.Object
    
With tv
    .Font.Size = 9
    .Font.Name = "Verdana"
    .ImageList = imgList 'assign preloaded imagelist control
 End With
    
    Set lvList = Me.ListView0.Object
    lvList.ColumnHeaders.Clear
    lvList.ListItems.Clear
    lvList.Icons = imgList
    
    Set db = CurrentDb
    Set tbldef = db.TableDefs("lvProducts")
    
    'Initialize ListView & Column Headers Property Values
     With lvList
        .ColumnHeaderIcons = imgList
        .Font.Size = 9
        .Font.Name = "Verdana"
        .Font.Bold = False
        
        'ColumnHeaders.Add() Syntax:
        'lvList.ColumnHeaders.Add Index, Key, Text, Width, Alignment, Icon
        'Alignment: 0 - Left, 1 - Right, 2 - Center
        .ColumnHeaders.Add 1, , tbldef.Fields(1).Name, 2600, 0, 5
        .ColumnHeaders.Add 2, , tbldef.Fields(3).Name, 2600, 0, 5
        .ColumnHeaders.Add 3, , tbldef.Fields(4).Name, 1440, 1, 5
    End With
    
    Set db = Nothing
    Set tbldef = Nothing

    
   LoadTreeView 'Create TreeView Nodes

End Sub

Private Sub LoadTreeView()
    Dim Nod As MSComctlLib.Node
    Dim firstCatID As Long
    Dim strCategory As String
    Dim strCatKey As String
    Dim strBelongsTo As String
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    
    'Initialize treeview nodes
     tv.Nodes.Clear
     
    'Initialize Listview nodes
    While lvList.ListItems.Count > 0
          lvList.ListItems.Remove (1)
    Wend
    
    strSQL = "SELECT lvCategory.CID, lvCategory.Category, "
    strSQL = strSQL & "lvcategory.BelongsTo FROM lvCategory ORDER BY lvCategory.CID;"
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    If Not rst.BOF And Not rst.EOF Then
        rst.MoveFirst
        firstCatID = rst!CID
    Else
        Exit Sub
    End If
    ' Populate all Records as Rootlevel Nodes
    Do While Not rst.BOF And Not rst.EOF
            strCatKey = Prfx & CStr(rst!CID)
            strCategory = rst!Category
            
            Set Nod = tv.Nodes.Add(, , strCatKey, strCategory, 1, 2)
            Nod.Tag = rst!CID
        rst.MoveNext
    Loop
    
    'In the second pass of the the same set of records
    'Move Child Nodes under their Parent Nodes
    rst.MoveFirst
    Do While Not rst.BOF And Not rst.EOF
        strBelongsTo = Nz(rst!BelongsTo, "")
        If Len(strBelongsTo) > 0 Then
            strCatKey = Prfx & CStr(rst!CID)
            strBelongsTo = Prfx & strBelongsTo
            strCategory = rst!Category
            
            Set tv.Nodes.Item(strCatKey).Parent = tv.Nodes.Item(strBelongsTo)
        End If
        rst.MoveNext
    Loop
    rst.Close
    
    ' Populate ListView Control with Product details
    ' of the first Category Item
    LoadListView firstCatID
    
End Sub


Private Sub LoadListView(ByVal CatID)
    Dim strProduct As String
    Dim strPKey As String
    Dim intcount As Integer
    Dim tmpLItem As MSComctlLib.ListItem
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    ' Initialize ListView Control
    While lvList.ListItems.Count > 0
        lvList.ListItems.Remove (1)
    Wend
   
     strSQL = "SELECT lvProducts.* FROM lvProducts "
     strSQL = strSQL & "WHERE (lvProducts.ParentID = " & CatID & ") "
     strSQL = strSQL & "ORDER BY lvProducts.[Product Name];"
    
    'Open filtered Products List for selected category
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    Do While Not rst.BOF And Not rst.EOF
        intcount = intcount + 1
        strProduct = rst![Product Name]
        strPKey = Prfx & CStr(rst!PID)
        
        'List Item Add() Syntax:
        'lvList.ListItems.Add Index,Key,Text,Icon,SmallIcon
        Set tmpLItem = lvList.ListItems.Add(, strPKey, strProduct, , 3) 'first column
            lvList.ForeColor = vbBlue
            
            'List second column sub-item Syntax:
            'tmpLItem.ListSubItems.Add Column - Index, Key, Text, ReportIcon, ToolTipText
            tmpLItem.ListSubItems.Add 1, strPKey & CStr(intcount), Nz(rst![Quantity Per Unit], ""), 6
            
            'List third column sub-item
            tmpLItem.ListSubItems.Add 2, strPKey & CStr(intcount + 1), Format(rst![list Price], "0.00"), 6, "In Local Currency."
        rst.MoveNext
    Loop
    
    Set db = Nothing
    Set rst = Nothing
    
    If intcount > 0 Then lvList.ListItems(1).Selected = True
    
End Sub

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim Cat_ID As String
Cat_ID = Node.Tag

LoadListView Cat_ID

End Sub

Private Sub TreeView0_OLEStartDrag(Data As Object, AllowedEffects As Long)
    Set tv.SelectedItem = Nothing
End Sub

Private Sub TreeView0_OLEDragOver(Data As Object, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single, State As Integer)
On Error GoTo TreeView0_OLEDragOver_Err

    Dim nodSelected As MSComctlLib.Node
    Dim nodOver As MSComctlLib.Node
    
    If tv.SelectedItem Is Nothing Then
        'Select a node if one is not selected
        Set nodSelected = tv.HitTest(X, Y)
        If Not nodSelected Is Nothing Then
            nodSelected.Selected = True
        End If
    Else
        If tv.HitTest(X, Y) Is Nothing Then
        'do nothing
        Else
            'Highlight the node the mouse is over
            Set nodOver = tv.HitTest(X, Y)
            Set tv.DropHighlight = nodOver
        End If
    End If
    
TreeView0_OLEDragOver_Exit:
Exit Sub

TreeView0_OLEDragOver_Err:
MsgBox Err & " : " & Err.Description, vbInformation, "TreeView0_OLEDragOver()"
Resume TreeView0_OLEDragOver_Exit
End Sub


Private Sub TreeView0_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single)

    Dim tv_nodSource As Node
    Dim tv_nodTarget As Node
    
    Dim strtv_ParentKey As String
    Dim strtv_TargetKey As String
    Dim strListItemKey As String
    Dim strSQL As String
    
    Dim vCatID As Long
    Dim lngPID As Long
    
    On Error GoTo TreeView0_OLEDragDrop_Err
    
    'Get the source/destination Nodes
    Set tv_nodSource = tv.SelectedItem
    Set tv_nodTarget = tv.HitTest(X, Y)
    
        If Not tv_nodTarget Is Nothing Then
            strtv_ParentKey = tv_nodSource.Key
            strtv_TargetKey = tv_nodTarget.Key
                
            If strtv_ParentKey = strtv_TargetKey Then Exit Sub

            'Extract ListItem Key
            strListItemKey = lvList.SelectedItem.Key
                
            'extract Category Record CID Value
            'and ListItem Product ID Key
            vCatID = Val(Mid(tv_nodTarget.Key, 2))
            lngPID = Val(Mid(strListItemKey, 2))
    
            'UPDATE lvProducts Table
            strSQL = "UPDATE lvProducts SET ParentID = " & vCatID & _
            " WHERE PID = " & lngPID
             
            CurrentDb.Execute strSQL, dbFailOnError
                
            Set tv.DropHighlight = Nothing
            tv_nodSource.Selected = True
                
            'Rebuild ListView Nodes
            TreeView0_NodeClick tv_nodSource
                
        Else ' Invalid Target location
            MsgBox "The destination is invalid!", vbInformation
        End If
    
TreeView0_OLEDragDrop_Exit:
Exit Sub

TreeView0_OLEDragDrop_Err:
MsgBox Err & " : " & Err.Description, vbInformation, "TreeView0_OLEDragDrop()"
Resume TreeView0_OLEDragDrop_Exit
End Sub

Private Sub TreeView0_OLECompleteDrag(Effect As Long)
    Set tv.DropHighlight = Nothing
End Sub

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

The familiar VBA Code Segments.

In the Form_Load() event procedure, we initialize the TreeView, ListView, and ImageList controls. During this process, the column headings of the ListView control are created, followed by populating its list items. Once these steps are completed, the LoadTreeView() subroutine is executed.

The LoadTreeView() subroutine loads the product category nodes into the TreeView control using records from the lvCategory table. This is done in two stages rather than in a single pass. The reason for this two-step approach was explained earlier (see the 7th link in the list above), so it is not repeated here.

After the TreeView has been populated, the LoadListView() subroutine is called with the first category record’s CID value (1) as its parameter.

This call filters the product records whose ParentID field equals 1 and displays them in the ListView control. The detailed procedure for this step was covered in last week’s post (8th link in the list above).

The Drag-Drop Action Subroutines.

The following Subroutines associated with the Drag and Drop action will be executed automatically in the order they are presented below:

TreeView0_OLEStartDrag()
TreeView0_OLEDragOver()
TreeView0_OLEDragDrop()
TreeView0_OLECompleteDrag()

The first subroutine initializes the nodes involved in the operation, while the last one resets their status once the process is complete.

The second subroutine, OLEDragOver(), functions similarly to the MouseMove event. It tracks the mouse movement during the drag-and-drop operation, highlights the node text when the pointer hovers over a node, and follows the cursor’s path until the left mouse button is released.

The code for the TreeView0_OLEDragDrop() procedure is shown below.

Private Sub TreeView0_OLEDragDrop(Data As Object, Effect As Long, Button As Integer, Shift As Integer, X As Single, Y As Single)

    Dim tv_nodSource As Node
    Dim tv_nodTarget As Node
    
    Dim strtv_ParentKey As String
    Dim strtv_TargetKey As String
    Dim strListItemKey As String
    Dim strSQL As String
    
    Dim vCatID As Long
    Dim lngPID As Long
    
    On Error GoTo TreeView0_OLEDragDrop_Err
    
    'Get the source/destination Nodes
    Set tv_nodSource = tv.SelectedItem
    Set tv_nodTarget = tv.HitTest(X, Y)
    
        If Not tv_nodTarget Is Nothing Then
            strtv_ParentKey = tv_nodSource.Key
            strtv_TargetKey = tv_nodTarget.Key
                
            If strtv_ParentKey = strtv_TargetKey Then Exit Sub

            'Extract ListItem Key
            strListItemKey = lvList.SelectedItem.Key
                
            'extract Category Record CID Value
            'and ListItem Product ID Key
            vCatID = Val(Mid(tv_nodTarget.Key, 2))
            lngPID = Val(Mid(strListItemKey, 2))
    
            'UPDATE lvProducts Table
            strSQL = "UPDATE lvProducts SET ParentID = " & vCatID & _
            " WHERE PID = " & lngPID
             
            CurrentDb.Execute strSQL, dbFailOnError
                
            Set tv.DropHighlight = Nothing
            tv_nodSource.Selected = True
                
            'Rebuild ListView Nodes
            TreeView0_NodeClick tv_nodSource
                
        Else ' Invalid Target location
            MsgBox "The destination is invalid!", vbInformation
        End If
    
TreeView0_OLEDragDrop_Exit:
Exit Sub

TreeView0_OLEDragDrop_Err:
MsgBox Err & " : " & Err.Description, vbInformation, "TreeView0_OLEDragDrop()"
Resume TreeView0_OLEDragDrop_Exit
End Sub

The Drag and Drop Action Step by Step.

The TreeView0_OLEDragDrop() procedure is triggered as soon as the left mouse button is released to complete the drop action. At the start, references to the active (source) node and the target node are stored in the object variables tv_nodSource and tv_nodTarget, respectively.

The code first checks whether the ListItem was dropped on a valid TreeView node. If it was dropped on the same source category node or on an empty area of the TreeView, the action is invalid. In the case of an empty drop area, tv_nodTarget will contain the value Nothing, prompting a message to be displayed before the procedure exits.

Next, the key values of the source and target nodes are stored in string variables. If both keys are identical, it means the ListItem was dropped onto its own parent (category) node, and the procedure terminates without changes.

If the keys differ, the product record’s ParentID field is updated with the CID value of the target category node, and the ListView items are refreshed. To do this:

The selected ListItem’s key value (PID) is saved in the variable strListItemKey.
The target category’s actual CID value is extracted from the target node by removing the prefix character 'X' and is saved in the variable vCatID. This value is used to update the product record’s ParentID field, effectively reassigning it to the new category.
The product’s PID is also stored in the variable lngPID, which serves as the filter criterion to locate the specific product record for updating.

An UPDATE SQL statement is then created, using lngPID as the filter and updating the ParentID field with the vCatID value. The change is executed with the CurrentDb.Execute method.

Finally:

The node highlight is reset to the source node.
The TreeView0_NodeClick() subroutine is called with tv_nodSource as its parameter, refreshing the ListView control to reflect the updated data.
The Close button exits the form when clicked.