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.
- Microsoft TreeView Control Tutorial
- Creating Access Menu with TreeView Control
- Assigning Images to TreeView Control
- Assigning Images to TreeView Control-2
- Tree View Control Check-Mark Add, Delete Nodes
- Tree View ImageCombo Drop-Down Access Menu
- Re-arrange TreeView Nodes by Drag and Drop
- List View Control with MS-Access TreeView
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.
Download Demo Database.
You may download the Demo database, do trial runs, and study the VBA Code.
Download ListViewDragDrop.zipWISH YOU A VERY HAPPY NEW YEAR.
MS-ACCESS EVENT HANDLING
- Withevents MS- Access Class Module
- Withevents and Defining Your Own Events
- Withevents Combo List Textbox Tab
- Access Form Control Arrays And Event
- Access Form Control Arrays And Event-2
- Access Form Control Arrays And Event-3
- Withevents in Class Module for Sub-Form
- Withevents in Class Module and Data
- Withevents and Access Report Event Sink
- Withevents and Report Line Hiding
- Withevents and Report-line Highlighting
- Withevents Texbox and Command Button
- Withevents Textbox Command Button
- Withevents and All Form Control Types
Download TreeViewListViewDemo.zip
