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

Introduction.

We are familiar with the drag-and-drop operations on TreeView Control that rearrange Nodes in Ms-Access. All the base records for the Treeview Control Nodes come from a single Access Table. We always update the Source Node’s ParentID field value, with the Target Node’s ID Value on the same Table record, to change the position in the TreeView Control. The records are not moved physically anywhere.

Here, with the addition of ListView Control along with TreeView Control, we plan to work with two different Access Tables.

lvCategory – Category Code and Description.
lvProducts – Categorywise Products.

This way it is easier to understand the relationship between both Tables and what changes to make and where when one Product Item (ListView item) moves from one Category to the other on the TreeView Control.

The lvCategory Access Table has 20 records for the TreeView Nodes and the lvProducts Table has 45 for the ListView Control. One or more records in the Products table are directly related to a product category on the Category Table. The relationship between them has been updated with the Category ID (CID) Field value on the Product Table’s ParentID field so that the change of category of the product reflects immediately on the ListView Control.

The demo data table was taken from the Microsoft Access Sample Database Northwind and split into two parts.

Based on the ParentID Field Value, of lvProduct records, we could filter and list all the related product items in the ListView Control, when a Category Node gets selected in the TreeView Control.

The Topics which we have covered so far.

The main topics on the TreeView, ImageList, ImageCombo, and ListView Controls, we have covered so far, in MS Access, are given below.

The ListView Drag-Drop Task.

As far as ListView’s Drag and Drop operation is concerned, it is a simple exercise comparing the same method within the TreeView Control alone. Since the Drag Drop action involves both TreeView and ListView Controls, we use the same TreeView0_OLEDragDrop() Event Procedure with some simple VBA Code.

The Product items listed in the ListView Control belong to the current Category item selected in the TreeView Control.

The User selects a particular product item from the ListView Control, if he/she thinks it belongs to a different Category Item, then drag and drop it on the target Category item on the TreeViewCcontrol.

The moved ListView Product Item will be added to the list of items that belong to the changed Category. The product record’s ParentID field value gets updated with the target Category record ID (CID value).

It is only a one-way action, always move the ListView item from one category and drop it on a different Category Node on the TreeView Control.

The ListView drag-drop demo Access Form frmListViewDrag’s trial run Screen image is given below:

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 TreeVew, ListView, and ImageList Controls. It creates the ColumnHeadings of the ListView Control, before populating the List items in the Listview control. At the end of this process, the LoadTreeView() subroutine is executed.

The LoadTreeView() subroutine populates the products’ Category Nodes on the TreeView Control, with the records from the lvCategory Table. Loading Nodes on the TreeView Control is a two-step process. Why it is so, rather than doing it in one go? This aspect has been explained in detail on an earlier Page, the 7th link on the list of links given above if you would like to go through it. Repeating all of them here may not be appropriate.

At the end of the above subroutine, the LoadListView() subroutine has been called with the first Category record’s CID Value 1 as the parameter.

The Product Records with ParentID field value 1 have been filtered and listed on the ListView Control. This procedure was explained in detail in last week’s post, the 8th item, among the List of Links given 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 and last Subroutines initialize the Nodes involved and reset their status at the end respectively.

The second one, the OLEDragOver() subroutine works like the MouseMove Event Procedure and tracks the movement of the mouse during the drag-drop operation. It highlights the NodeText when the mouse is over a Node and tracks its trajectory till the left mouse button gets released.

The TreeView0_OLEDragDrop() procedure code alone is listed 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 Drop Action Step by Step.

The TreeView0_OLEDragDrop() Procedure executes immediately after the left mouse button has been released to complete the Drop Action. At the beginning of the code, the active and the Target TreeView Node’s references have been saved in tv_nodSource and tv_nodTarget object Variables respectively.

Next, we perform a check on, whether the ListItem has been dropped on a valid TreeView Node or not. If it is dropped on the same source Category Node or dropped on an empty area on the TreeView Control then these moves are not valid. If it has been dropped in an empty area of the TreeView Control then the tv_nodTarget object variable will contain the value Nothing. In that case, it displays a message and exits from the Program.

Next, the TreeView Source and Target Node Key Values are saved in two String Variables. If both keys are the same then the ListItem is dragged and dropped on its own Parent Node (Category Node) on the TreeView Control, then the program is aborted.

If both Keys are different then it is time to update the change on the Product Record’s ParentID field, with the Target Category Record’s CID Code, and refresh the ListView Items.

The selected ListItem’s Key value (PID field value) has been saved in the strListItemKey String Variable.

The Category record’s actual CID field value has been extracted from the Target Node, by stripping the prefix character value 'X' and saved in the variable vCatID. This is the value that we will be updating on the Product Record’s ParentID field. When updated the Product ListItem becomes the subitem of the new Category.

Similarly, the selected List Item’s Product’s Key PID value is extracted and saved in Variable lngPID. This has been used as criteria to filter and pick that particular Product record for updating the ParentID field with vCatID.

An UPDATE Query SQL has been created to filter the record, using the lngPID Code as Criteria, to filter the Product record, and to update the vCatID Value in the ParentID field.

The Execute method of the Currentdb has been called with the SQL and updates the change.

The Highlight of the Node has been reset to the Source Node.

Next, the TreeView0_NodeClick() subroutine has been called with the tv_nodSource as a parameter to reflect the change on the ListView Control.

The Close Button Click will close the Form.