November 2020 ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

I hope you found last week’s tutorial on the ImageCombo Control useful in your Microsoft Access projects. With the TreeView-style ImageCombo, we were able to create a compact yet versatile drop-down menu that accommodates multiple options while occupying very little space on a form.

In an earlier session, we explored how to add new Nodes at specific positions in the hierarchy, or delete and reinsert Nodes to relocate them on the TreeView control.

That method required creating a new record in the source table (for new Nodes) or deleting and recreating records (to move existing Nodes), ensuring the changes were stored permanently. Using these Add/Delete functions, we could build or restructure the Node hierarchy as needed.

However, when it comes to rearranging Nodes, there’s a more efficient approach than repeatedly deleting and recreating them: simply drag the Node from its current position and drop it into its new location within the TreeView control. That’s exactly what we’ll be learning in this episode.

The advantage of this method is its simplicity. Instead of complex record manipulations, we only need to update the ParentID field of the affected records. This single change makes the new Node arrangement permanent.

The Topics Covered so far in Earlier Sessions.

However, while this method is simpler, it does come with a few challenges and limitations that we should be aware of. We’ll explore these issues in detail a little later in this session, along with the techniques to handle them effectively.

Demo Data Table and Form.

For this exercise, we need a Table and a Form. Fortunately, we already have a suitable table named Sample, which we created in an earlier tutorial session. If you have previously downloaded the Demo Database from the second link provided above, you can continue using that database for this session as well. We will make use of the following objects from that database for our drag-and-drop experiments:

Table: Sample
Form: frmSample

The TreeView Control Image on frmSample with demo data is given below for reference:

You can download the Demo Database (ProjectMenu.zip) from the second link provided above, then extract the file to obtain ProjectMenu.accdb.

New Form for Drag and Drop Trial Runs.

Open the ProjectMenu.accdb database.
First, make a copy of the Sample table and name it Sample_bk. This backup ensures that the original data remains safe for later use. During our drag-and-drop experiments, the ParentID field values in the Sample table will be updated, but we will need the original data intact afterward.
Next, create a new form and name it frmDragDrop.
Once you have finished designing the form, it will resemble the illustration shown below.

Insert the TreeView Control
- From the ActiveX Controls list, insert a TreeView Control onto the form.
- Leave enough space above the control to accommodate two command buttons and a heading label.
- Drag the sizing handle at the bottom-right corner to enlarge the TreeView so that it can display all the nodes without the need for scrolling.
- Change the Name property of the TreeView control to TreeView0.
Add the Command Buttons
- Insert a Command Button above the left edge of the TreeView control.
  - Set its Name property to cmdExpand.
  - Set its Caption property to Expand All.
- Insert another Command Button above the right edge of the TreeView control.
  - Set its Name property to cmdCollapse.
  - Set its Caption property to Collapse All.
Add the Heading Label
- Insert a Label Control above the command buttons.
- Make it wide enough to display the heading as shown in the reference image.
- Increase the Font Size to 14 for better visibility.

Skip the ImageList for Now
- For the time being, ignore the ImageList Control.
- The code lines that modify the Node’s ImageList index numbers have been commented out.
- Later, you can import the ImageList Control along with manually uploaded images from our earlier Tutorial Demo Database (see the 4th Link Page mentioned previously).
- This will allow you to display images on the nodes.
- When node positions are changed during drag-and-drop actions, the node images should also be updated to reflect their new positions (whether they are root-level nodes or child nodes).
Drag-Drop Form Module Code.

Open the VBA Code Module of the form frmDragDrop.
Copy and paste the following VBA code (this is only the first half of the form’s module code) into the frmDragDrop Form’s Class Module, and then save the form:

Option Compare Database
Option Explicit

Dim tv As MSComctlLib.TreeView
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim imgListObj As MSComctlLib.ImageList
Const KeyPrfx As String = "X"

Private Sub Form_Open(Cancel As Integer)
Set tv = Me.TreeView0.Object

'Set imgListObj = Me.ImageList1.Object
'tv.ImageList = imgListObj

LoadTreeView

End Sub

Sub LoadTreeView()
Dim strKey As String
Dim strPKey As String
Dim strText As String
Dim strsQL As String

strsQL = "SELECT * FROM Sample ORDER BY ID"

Set db = CurrentDb
Set rst = db.OpenRecordset(strsQL, dbOpenDynaset)
    
tv.Nodes.Clear

'Add all Items are added as Root Nodes
Do While Not rst.BOF And Not rst.EOF
    strKey = KeyPrfx & CStr(rst!ID)
    strText = rst!desc
    tv.Nodes.Add , , strKey, strText
    
    'With tv.Nodes.Item(strKey)
    '    .Image = 1
    '    .SelectedImage = 4
    'End With

    rst.MoveNext
Loop

'Prepare to update the Parent-Key of Nodes
'wherever applicable to move and position the Child Nodes
strPKey = ""
rst.MoveFirst
Do While Not rst.EOF
    strPKey = Nz(rst!parentid, "")
    
    If Len(strPKey) > 0 Then
        strPKey = KeyPrfx & strPKey
        strKey = KeyPrfx & CStr(rst!ID)
        strText = rst!desc
        
        'Move the Child Node under it's Parent-Node
        Set tv.Nodes.Item(strKey).Parent = tv.Nodes.Item(strPKey)
        
'Update Image and SelectedImage Properties
'with ImageList Index numbers
        'With tv.Nodes.Item(strKey)
        '    .Image = 2
        '    .SelectedImage = 3
        'End With

    End If
    rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set db = Nothing

End Sub


Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim SelectionNode As MSComctlLib.Node
    
'Ensure that the clicked node equals the selected node in the tree
If Not Node Is Nothing Then
    Set SelectionNode = Node
       If SelectionNode.Expanded = True Then
            SelectionNode.Expanded = False
        Else
            SelectionNode.Expanded = True
        End If
End If
End Sub

Private Sub cmdCollapse_Click()
Dim tmpnod As MSComctlLib.Node
For Each tmpnod In tv.Nodes
    If tmpnod.Expanded = True Then
        tmpnod.Expanded = False
    End If
Next

End Sub

Private Sub cmdExpand_Click()
Dim tmpnod As MSComctlLib.Node
For Each tmpnod In tv.Nodes
    If tmpnod.Expanded = False Then
        tmpnod.Expanded = True
    End If
Next

End Sub

If you have followed the earlier episodes, most of the above code should look familiar. The main difference lies in the LoadTreeView() subroutine, which now works in a slightly different way.

In this version, populating the TreeView nodes is done in two steps:

First pass:
All records from the Sample table are loaded as root-level nodes in the TreeView control. Each node uses the ID field value as its Key.
Second pass:
The same records are read again, this time checking for values in the ParentID field.
- If the ParentID field is empty, the node remains as a root-level node.
- If the ParentID field contains a value, the code locates the node whose Key matches this ParentID, and then reassigns the current node as its child node (updating the [Relative] parameter of the Add() method accordingly).

At first glance, this two-step population may look redundant, but there is a very specific reason behind it. We will revisit this later in the session, and the advantage will be clear without needing much explanation.

On the form design, I have included an ImageList control. You may:

Insert the ImageList ActiveX control and upload a few images manually from your disk, or
Copy and paste the ImageList control (with preloaded images) from one of the earlier demo databases.

Make sure the control is named ImageList1. Otherwise, you will need to update the control name in the code.

After adding the ImageList, enable the commented-out lines in the Form_Open() event procedure by removing the leading apostrophes:


'Set imgListObj = Me.ImageList1.Object
'tv.ImageList = imgListObj

Similarly, in the TreeView0_OLEDragDrop() subroutine (found in the second part of the VBA code), remove the comment symbols from the lines that update the Image Index parameters. With these changes, images will appear alongside the TreeView nodes.

If you already have your own ImageList control with uploaded images, you may adjust the image index numbers in the code to decide which images should appear on each node.

The TreeView0_NodeClick() event procedure expands the currently selected node if its child nodes are collapsed; otherwise, it collapses them. Normally, this expand/collapse action can be done by clicking the [+] or [–] symbol next to the node, but here it is handled programmatically for more control.

The cmdExpand_Click() and cmdCollapse_Click() subroutines expand all nodes and collapse all nodes, respectively.

When you run the code, the form will display as shown in the Form View image below:

You may save the frmDragDrop Form and open it in Normal View. If everything went well, then you will see the above screen. Try out the Expand All and Collapse All Command Buttons and check whether they are working too. If not, then re-check whether the following settings are correct or not:

i) The TreeView Control’s Name is: TreeView0

ii) Display the Property Sheet of Exampand All Command Button and select [Event Procedure] in the On Click Event Property.

iii) Ensure that the same setting is intact for the Collapse All Command Button also.

iv) Click on a Node, having Child Nodes, to see whether they collapse or expand on repeated clicks.

v) If the ImageList Control is placed on the Form, then its name must be ImageList1.

Let us proceed with the second part of the VBA Code that implements the drag-and-drop events.

Second Half of the VBA Code.

Copy the following Second Part of the VBA Code, on the frmDragDrop Form Module, that implements the Drag-Drop action, and paste it below the existing Code:

Private Sub TreeView0_OLEStartDrag(Data As Object, AllowedEffects As Long)
    Set Me.TreeView0.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)
    
    Dim SelectedNode As MSComctlLib.Node
    Dim nodOver As MSComctlLib.Node
    
    If tv.SelectedItem Is Nothing Then
        'Select a node if one is not selected
        Set SelectedNode = tv.HitTest(x, y)
        If Not SelectedNode Is Nothing Then
            SelectedNode.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

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 sourceNode As MSComctlLib.Node
    Dim SourceParentNode As MSComctlLib.Node
    Dim targetNode As MSComctlLib.Node
    
    Dim tmpRootNode As MSComctlLib.Node
    Dim strtmpNodKey As String
    Dim ChildNode As MSComctlLib.Node
    
    Dim strSPKey As String
    Dim strTargetKey As String
    
    Dim strsQL As String
    Dim intKey As Integer
    Dim intPKey As Integer
    
    On Error Resume Next
    
    Select Case Screen.ActiveControl.Name
            
           Case TreeView0.Name
                Set sourceNode = tv.SelectedItem
            
    End Select
    
    'Get Source Parent Node & Target Node Reference
    Set SourceParentNode = sourceNode.Parent
    Set targetNode = tv.HitTest(x, y)
            
    'If any errors then exit
    If Err <> 0 Then
        MsgBox Err & " : " & Err.Description, vbInformation + vbCritical, "OLEDragDrop()"
        Err.Clear
        Exit Sub
    Else
        On Error GoTo 0
    End If
    

    'Get/define Source parent Node Key to compare it with Target Node Key
    If SourceParentNode Is Nothing Then
        strSPKey = "Empty"
    Else
        strSPKey = SourceParentNode.Key
    End If
    
    'Check the Target Node/Location and define the Key
     Select Case True
        Case targetNode Is Nothing
            strTargetKey = "Empty"
        
        Case targetNode.Key = ""
            strTargetKey = "Empty"
            Set targetNode = Nothing
        Case Else
            strTargetKey = targetNode.Key
     End Select
    
    'Make sure the Target Node is not the source Node's own parent
    If strTargetKey = strSPKey Then Exit Sub
    
    'Track User's Node move action, check for error.
    On Error Resume Next
    
    If targetNode Is Nothing Then
        
        'If target Node is Nothing (the Node dropped in the empty area),
        'then the Node must be moved to the Root-level
        'save the original sourceNode.Key
        strtmpNodKey = sourceNode.Key
        
        'Modify the source Node Key, with addition of some text, say 'Empty', like 'X5Empty'
        'So that a temporary Node can be created with the original source Node key.
        'Note: Two Nodes with the same Key cannot remain in memory at the same time.
        'The Source Node with key 'X5Empty' deleted later,
        'temporary Node takes it's droped location.
        sourceNode.Key = sourceNode.Key & strTargetKey

        'Create the temporary Root Node, with original sourceNode Key
        Set tmpRootNode = tv.Nodes.Add(, , strtmpNodKey, sourceNode.Text)
        
        'define the Root Node image indexes
        'With tmpRootNode
        '    .Image = 1
        '    .SelectedImage = 4
        'End With
        
        'Move all child Nodes from SourceNode,if any,
        'as tmpRootNode's Children
        Do Until sourceNode.Children = 0
            Set sourceNode.Child.Parent = tmpRootNode
            
            'modify Node image indexes
            'With sourceNode
            '    .Image = 2
            '    .SelectedImage = 3
            'End With
        Loop

        'Delete the Source Node with modified Key from TreeView
        tv.Nodes.Remove sourceNode.Index
        
        'Move the tmpRootNode with original Key
        'to the dropped location on TreeView
        Set sourceNode = tmpRootNode
    Else
        'Move the sourceNode under targetNode as child
        Set sourceNode.Parent = targetNode
        
        'modify Node image indexes
        'With sourceNode
        '    .Image = 2
        '    .SelectedImage = 3
        'End With
    End If
    
    'Notify, if there was an Error then Exit, else Update PrentID of related Record.
    If Err <> 0 Then
        MsgBox Err & " : " & "Unable to move:" & vbCrLf & Err.Description, vbInformation + vbCritical, "DragDrop2()"
        Exit Sub
    Else
        'Build and execute the SQL statement to update the record
        If targetNode Is Nothing Then
            intKey = Val(Mid(sourceNode.Key, 2))
            strsQL = "UPDATE Sample SET ParentID = Null" & _
                     " WHERE ID = " & intKey
        Else
            intKey = Val(Mid(sourceNode.Key, 2))
            intPKey = Val(Mid(targetNode.Key, 2))
            
            strsQL = "UPDATE sample SET ParentID = " & intPKey & _
                     " WHERE ID = " & intKey
        End If
        
        'Modify the table records
        CurrentDb.Execute strsQL, dbFailOnError
        
        'If an error raised then refresh TreeView and exit
        If Err <> 0 Then
            MsgBox Err & " : " & Err.Description
            LoadTreeView 'Refresh/display TreeView without changes
        Else
            'Sort Nodes
            If sourceNode.Parent Is Nothing Then
                sourceNode.Root.Sorted = True
            Else
                sourceNode.Parent.Sorted = True
            End If
            
            tv.Nodes(sourceNode.Key).Selected = True
        End If
    End If
    On Error GoTo 0

End Sub

Private Sub TreeView0_OLECompleteDrag(Effect As Long)

    'Turn off the drophighlight
    Set tv.DropHighlight = Nothing

End Sub

Private Sub Form_Close()

Set tv = Nothing
End Sub

For the drag-and-drop action, four event procedures are executed automatically. These handle the different stages of the process—when you begin dragging a node, when the dragged node is moved over other nodes (highlighting them as potential targets), and finally, when it is dropped either on another node or onto the empty root-level area.

The Main Subroutines of the Code.

The drag-and-drop operation in the TreeView control is managed through four key event procedures:

TreeView0_OLEStartDrag() – Initializes the drag process by setting the selected item and resetting the Node to Nothing.
TreeView0_OLEDragOver() – Functions like the MouseMove event; it highlights the node as the dragged item is moved over potential target nodes.
TreeView0_OLEDragDrop() – Handles the main task: performs the necessary checks, positions the node(s) at the drop location, and updates the related record in the base table.
TreeView0_OLECompleteDrag() – Completes the action by resetting the DropHighlight Property to Nothing.

Technically, the entire drag-and-drop process could be managed with the TreeView0_OLEDragDrop() procedure alone. However, without the supporting routines, nodes will not be visually highlighted when the dragged item passes over them. The only indicator would be the mouse pointer, which changes to display a small second arrow, as shown in the sample image above.

With that in mind, let us now focus on this subroutine and review its code in detail. At the beginning of the procedure, we declare the required string variables for the nodes, along with other supporting variables.

Rather than analyzing the code line by line here, note that each section is already documented with inline comments, making it easier for you to follow and understand the logic as you go through the code yourself.

The Sequence of Drap Drop Events

Let us first understand the sequence of events: the user selects a node, drags it across other nodes on the way to its final destination, and finally drops it either on a target node or in the empty space of the TreeView control to make it a root-level node.

As you drag a node over another node’s text, the text becomes highlighted, indicating your current position. Once you move away, the highlight disappears. This visual feedback continues until you reach the target node. The TreeView0_OLEDragOver() subroutine manages this highlighting behavior.

When a node is finally dropped, the TreeView0_OLEDragDrop() subroutine takes over. At this stage, the user’s intention must be interpreted, and the appropriate action taken. To move the node correctly, the following information must be captured and analyzed.

The Important Information to Keep Track Of.

To correctly process a drag-and-drop action, the following details must be identified and analyzed:

Source Node details: reference to the node, its key, ParentID value, and any child nodes.
Target Node or location: reference and key of the node on which the source node is dropped.
Drop on empty TreeView area: if the target is not another node but the blank area of the TreeView, the source node is moved to the root level.
Drop on another node: the target node becomes the new parent of the source node.
Child nodes: if the source node has children, they are moved together with their parent node.

Special cases where no action is taken:

Dropping a node onto its own parent.
- Example: dragging the TextBox node and dropping it onto Controls, or dragging Controls onto its parent Form. These attempts simply place the node back in its current position, so the action is ignored.
Dragging a root-level node and dropping it onto the empty TreeView area. Since it is already a root node, no action is needed.

For all valid moves, the corresponding record in the Sample table must be updated by setting its ParentID field to reflect the new hierarchy.

Node Drop in the Root-level Empty Area.

In case #3 (dropping a node onto the empty TreeView area), we face a technical issue:

A new root-level node must be created with the same key as the source node. However, duplicate keys are not allowed in the TreeView hierarchy. This is where the procedure gets slightly confusing.

The workaround is as follows:

Rename the source node’s key temporarily
- Append a suffix to the key (e.g., change X5 → X5Empty) so that the original key (X5) becomes available.
- This prevents a duplicate key conflict.
Create a temporary node with the original key
- Insert a new node using the original key (X5) at the root level.
Reassign child nodes (if any)
- Move all child nodes from the renamed source node (X5Empty) to the new temporary node (X5).
Delete the renamed source node
- Remove the source node (X5Empty) from the TreeView control.
- Note: this deletion only affects the TreeView; the related record in the Sample table remains untouched.
Promote the new node to the root level
- The temporary node (X5) — now carrying its children — is placed at the root level.
Update the table
- Set the ParentID field of the corresponding record to a zero-length string (""), marking it as a root-level node in the database.

👉 This sequence ensures:

No duplicate key errors in the TreeView.
All child nodes remain intact with their parent.
The database correctly reflects the hierarchy change.

Self-Experiments of Drag and Drop.

You can try out a few drag-and-drop experiments yourself to see this in action. Select a node, hold down the left mouse button, and drag it to a new location—either onto another node or into the empty area of the TreeView control.

As you drag the node over another node’s text, the target node becomes highlighted, and the highlight disappears once you move away.
When you release the mouse button, the dragged node is placed in its new position.
You can repeat this process with a single node or a node with its children, and the entire branch will move together.

Behind the scenes, each move updates the related record in the table: the ParentID field is assigned the Key value (ID) of the new parent node’s record. If the node is dropped in the empty area, the ParentID is set to a zero-length string to mark it as a root-level node.

Why Two-Step Node Populating Procedure?

Now, let’s return to the LoadTreeView() subroutine and take a closer look at the two-step process we used to populate the TreeView control with all nodes.

First pass: All records from the Sample table are added as root-level nodes, using the ID field value as the node key.
Second pass:
- If the ParentID field is empty, the node remains as a root-level node.
- If the record contains a ParentID value, the node is correctly positioned under its corresponding parent node.

At this point, you may wonder: Why is this two-step process necessary?

Rather than explaining in detail, we’ll answer this through a quick experiment. You may already have tried a few drag-and-drop operations and rearranged nodes, which also updated their corresponding ParentID values in the table. To ensure our demo starts with clean data, we need to restore the table to its original state.

Fortunately, we already created a backup copy earlier, named Sample_bk. Here’s what to do:

Delete the current Sample table.
Create a fresh copy of Sample_bk and rename it as Sample.
Open the table to view the records and their ParentID values.

A sample image of the table is shown below:

The ID field values in the Sample table are AutoNumbers. They are always sequential and unique.

When adding child nodes to the TreeView control, we follow one fundamental principle:

The Simple Child Node Rule

A record’s ParentID value (the parent key) assumes that a parent node with the same Node-Key (ID) already exists in the TreeView control.

Let’s look at a couple of examples from the table image above:

Record 3:
- ID = 3
- ParentID = 2
  This means the record with ID = 2 must already exist in the TreeView before record 3 can be added as its child node.
Record 21:
- ID = 21
- ParentID = 12
  Even though record 21 appears much later in the table, its parent node (ID = 12) must still be present in the TreeView before it can be added as a child.

In both cases, the program assumes that by the time a record with a ParentID value is processed, the parent record (ID = ParentID) has already been added as a node in the earlier cycle of populating the TreeView.

Justifying the Two-Step Procedure.

Now let’s try out some drag-and-drop trial runs.

Before we begin, recall that we originally had a form named frmSample (from our first tutorial session). In that form, all TreeView nodes were loaded in a single pass. We’ve followed the same approach so far, but from this point onward, we’ll be shifting to the new method.

Open frmSample
- This form still uses the old loading method.
- Observe how the TreeView nodes are displayed with the Sample table records.
Close frmSample after you’re done viewing the nodes.
Open frmDragDrop
- This form is prepared for testing drag-and-drop functionality.
- Select the node with the Node-Text “Table”.
- Click and hold the left mouse button, drag it, and drop it onto the node with the Node-Text “Form.”
👉 When you do this, the Table node and all of its children (its immediate child node Fields and the Fields’ own children) are moved under the Form node as child nodes.
Close frmDragDrop and reopen it.
- You will notice that the nodes are correctly positioned where you dropped them earlier, as shown in the sample image below.

✨ This step-by-step trial shows that the drag-and-drop action not only repositions nodes visually but also updates their relationships in the underlying Sample table so that the new hierarchy persists.

Now, close the form frmDragDrop.
Open the form frmSample.

Instead of displaying the TreeView nodes, you will be greeted with an error message:

Element Not Found
Error Number: 35601

Click the Debug command button in the error dialog.
- This takes you to the highlighted code line where the error occurred.
- If you hover the mouse over the parameters in the Add() method:
  - nodKey shows X3
  - ParentKey shows X7
From these values, we can infer:
- The program is currently processing the record with ID = 3.
- It is attempting to make this record a child node of the record with ID = 7.
- However, node ID = 7 has not yet been added to the TreeView in this pass, which causes the Element Not Found error.
Press the F5 key to bring the error dialog up again, and then click End to stop the program.
- This will return you to the database window.
- Close the form frmSample.
Now, open the Sample table to review the arrangement of the ParentID values after our drag-and-drop action.
- You will see the updated records reflecting the change.
- Notice the highlighted record where the ParentID = 7, showing its parent record’s position.
- This is the record that triggered the error when the TreeView tried to rebuild the hierarchy in frmSample.

✨ This illustrates why our two-step loading method LoadTreeView() (root-level first, then re-parenting in the second pass) is essential. It ensures that when a child node is being added, its parent already exists in the TreeView control.

Following the normal node-population procedure, we arrive at the third record.

This record has a ParentID value of 7, which means the node with ID = 7 must already exist in the TreeView control.
However, since the node with ID = 7 has not yet been added, the program attempts to reference a non-existent node, which immediately triggers the error.

You might think that simply sorting the records ParentID before loading them could resolve the issue. Unfortunately, even if we rearrange the records this way, the problem remains. The new order would look like the sample image shown below.

✨ This sets up the explanation for why a two-pass method is necessary—because sorting alone cannot guarantee that every parent node exists before its children are added.

When we look further into the recordset, we find another case where the expected Parent Node is not yet available in the TreeView. This reinforces the fact that relying on a single-pass load (or even sorting) is not enough.

✅ This is exactly why our two-step TreeView Node loading approach is necessary. It works consistently for both the initial load and for cases where Nodes have been rearranged by drag-and-drop.

Step 1 – Populate all records as Root-level Nodes

Each record is added to the TreeView as a root-level Node.
The record’s ID field value is used as the Node-Key.
At this point, all Nodes exist in the TreeView, even though the hierarchy is not yet correct.

With this, we guarantee that no matter what ParentID a record refers to, the corresponding Node already exists in the TreeView.

Step 2 – Reorganize Nodes under their correct parents

Loop through the same set of records again.
If the ParentID field is empty, the Node remains at the root level.
Otherwise, the Node is reassigned as a child of its Parent Node using the following statement:


Set tv.Nodes.Item(strKey).Parent = tv.Nodes.Item(strPKey)

Here,

strKey = current record’s Node Key (ID),
strPKey = ParentID value.

This is achieved by resetting the record pointer with:


rst.MoveFirst

and then looping through all records again with the usual pattern:


Do While Not rst.EOF
    '… Node reassignment logic …
    rst.MoveNext
Loop

✨ The key benefit of this two-pass approach is that it guarantees parent Nodes exist before child reassignment. This avoids the “Element Not Found” error entirely and ensures the TreeView hierarchy always reflects the current state of the data—even after drag-drop operations have reshaped the relationships.

Second Step in Reverse Order.

Alternatively, you can approach the second pass in reverse order.

After all records have been populated as Root-level Nodes, the record pointer will naturally be positioned at the end of the recordset (EOF). From there:

Reset the pointer to the last record using:
```
rst.MoveLast
```
Begin a loop with a BOF check, and for each iteration, move the pointer backwards with:
```
rst.MovePrevious
```
Within the loop, reassign each Node under its Parent Node as required.

This method ensures that every Node has already been created in the TreeView when its children are processed—just like in the forward pass. However, you may notice that the visual placement order of Nodes in the TreeView differs slightly compared to the forward-loading method.

I encourage you to try this reverse-order approach yourself with the code changes above and observe the results.

Download Demo Database.

Download DragDropDemo.zip

Dim imgcombo1 As MSComctlLib.ImageCombo Dim imgCombo2 As MSComctlLib.ImageCombo Dim objimgList As MSComctlLib.ImageList Const KeyPrfx As String = "X" Private Sub Form_Load() Set objimgList = Me.ImageList0.Object cboImageList 'load imagelist-combo 'CreateMenu 'Create Drop-Down Menu End Sub Private Sub cboImageList() Dim j As Integer Dim strText As String Set imgCombo2 = Me.ImageCombo2.Object imgCombo2.ImageList = objimgList For j = 1 To objimgList.ListImages.Count strText = objimgList.ListImages(j).Key imgCombo2.ComboItems.Add , , strText,j,,j Next imgCombo2.ComboItems(1).Selected = True End Sub

Private Sub CreateMenu() Dim db As DAO.Database Dim rst As DAO.Recordset Dim strSQL As String Dim strKey As String Dim strText As String Dim typ As Integer Set imgcombo1 = Me.ImageCombo1.Object imgcombo1.ImageList = objimgList strSQL = "SELECT ID, Desc, PID, Type,Macro,Form,Report FROM Menu;" Set db = CurrentDb Set rst = db.OpenRecordset(strSQL, dbOpenDynaset) Do While Not rst.EOF And Not rst.BOF If Len(Trim(Nz(rst!PID, ""))) = 0 Then strKey = KeyPrfx & CStr(rst!ID) strText = rst!Desc imgcombo1.ComboItems.Add , strKey, strText, 1, 2, 1 ' image index 1,2([image],[selectedimage]) 'imgcombo1.ComboItems.Add , strKey, strText, "folder_close", "folder_open", 1 Else strKey = KeyPrfx & CStr(rst!ID) strText = rst!Desc imgcombo1.ComboItems.Add , strKey, strText, 4, 5, 4 'last param is spacing 'imgcombo1.ComboItems.Add , strKey, strText, "left_arrow", "right_arrow", 4 'Check for the presense of Type Code If Nz(rst!Type, 0) > 0 Then typ = rst!Type With imgcombo1.ComboItems Select Case typ 'save type Code & Form/Report/Macro Name in Tag Property Case 1 .Item(strKey).Tag = typ & rst!Form Case 2 .Item(strKey).Tag = typ & rst!Report Case 3 .Item(strKey).Tag = typ & rst!Macro End Select End With End If End If rst.MoveNext Loop rst.Close imgcombo1.ComboItems.Item(1).Selected = True End Sub Private Sub ImageCombo1_Click() Dim strObject As String Dim strTag As String Dim typ As Integer strTag = ImageCombo1.SelectedItem.Tag typ = Val(strTag) strObject = Mid(strTag, 2) Select Case typ Case 1 DoCmd.OpenForm strObject, acNormal Case 2 DoCmd.OpenReport strObject, acViewPreview Case 3 DoCmd.RunMacro strObject End Select End Sub