LEARN MS-ACCESS TIPS AND TRICKS: TreeView Control

Showing posts with label TreeView Control. Show all posts

TreeView Control with Subforms

a.p.r. pillaiJanuary 19, 2021ImageList Control, ListView Control, TreeView Control No comments

Introduction.

In this session of the TreeView Control Tutorial, we will work with a main form (frmTreeViewtab) that hosts a TreeView control and two Subforms. The ImageList control with preloaded images, imported from an earlier demo project, is also included.

We will continue using the same tables from our previous projects: lvCategory and lvProducts.

The lvCategory table provides the category information. Its primary key field (CID) and description field (Category) are used as the Key and Text parameters of the TreeView node’s Add() method.
The lvProducts table stores detailed product information, including product code, description, stock quantity, and list price. It also contains a ParentID field, which links each product to a category by storing the corresponding CID value. This establishes a master–child relationship between the two tables.

On the form, product records are managed through two subforms placed on a Tab Control:

First Page (Data View Subform): Displays all products that belong to the category currently selected in the TreeView. Users can view records here and select one for editing.
Second Page (Edit Subform): Provides an editable view of the record selected on the first page. Key fields (highlighted in gray) are locked to prevent modification.

This setup allows users to browse products by category via the TreeView, view product details in the first subform, and then switch to the second subform to edit the selected record while maintaining data integrity.

TreeView with Subforms Design View.

The Design View of the form frmTreeViewTab is given below:

On the main form, the first two unbound text boxes are updated whenever the user selects a Category item from the TreeView control.

The third unbound text box (p_ID) is used to track the current product. By default, it is initialized with the PID value of the first product record. If the user selects a different record in the first subform, the text box is updated with that record’s PID instead.

This ensures that the record currently highlighted in the data view subform is made available in the edit subform, allowing the user to make modifications seamlessly.

Links to Earlier Tutorial Sessions.

The earlier Tutorial Session Links are given below for ready reference:

The CatID unbound text box on the main form is assigned to the [Link Master Fields] property of the first subform.

Similarly, the p_ID unbound text box (holding the product code) is linked through the [Link Master Fields] property of the second subform on the Edit tab page.

The value of p_ID is automatically updated whenever the first subform is refreshed or when the user selects a specific record. This ensures that the corresponding product is always available for editing on the second subform.

Normal View of the Screen.

The normal view of the frmTreeViewTab form is given below:

On the second subform, the key fields of the product record are displayed in gray text and are locked to prevent any modifications.

**The form frmTreeViewTab Class Module VBA Code:**

Option Compare Database
Option Explicit

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

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

'Initialize TreeView Nodes
    Set tv = Me.TreeView0.Object
    tv.Nodes.Clear
'Initialixe ImageList Object
    Set imgList = Me.ImageList3.Object
    
'Modify TreeView Font Properties
With tv
    .Font.Size = 9
    .Font.Name = "Verdana"
    .ImageList = imgList 'assign preloaded imagelist control
 End With
    
   LoadTreeView 'Create TreeView Nodes

End Sub

Private Sub LoadTreeView()
    Dim Nod As MSComctlLib.Node
    Dim strCategory As String
    Dim strCatKey As String
    Dim strProduct As String
    Dim strPKey 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
    
    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)

    ' Populate all Records as Rootlevel Nodes
    Do While Not rst.BOF And Not rst.EOF
        If rst.AbsolutePosition = 1 Then
           Me![CatID] = rst![CID]
        End If
            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
    

    TreeView0_NodeClick tv.Nodes.Item(1)
    
End Sub

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

'Initialize hidden unbound textbox 'Link Master Field' values
Cat_ID = Node.Tag
Me!CatID = Cat_ID
Me![xCategory] = Node.Text

End Sub

Private Sub cmdExit_Click()
    DoCmd.Close
End Sub

Since the usage and functionality of the TreeView and ImageList controls were thoroughly explained in earlier sessions, only a few of those previously introduced VBA subroutines are included in this form’s module.

So far, we have designed several screens using TreeView, ListView, ImageList, and ImageCombo controls in MS Access. I hope you will find these examples to be a valuable reference for designing the interface of your own projects.

MS-Office Version Issues for TreeView Control.

If you encounter any issues running the demo database in your version of Microsoft Access, you may refer to the following link for corrective steps that could help resolve the problem.

In earlier versions, these controls did not function properly on 64-bit systems. However, in September 2017, Microsoft released an updated version of the MSCOMCTL.OCX library. For your reference, an extract from Microsoft’s documentation is provided below.

Click on the document image above to view the full text of the 2017 Update: 1707 release. For additional guidance, you may also refer to the following link, which provides some useful troubleshooting tips.

SOLVED MSCOMCTL.OCX DOWNLOAD, REGISTER for 64-BIT WINDOWS

By leveraging the TreeView control and related objects, we can design user interfaces that are both more visually appealing and more efficient for our future projects.

Download the Demo Database.

Download TreeViewSubforms.zip

ListView Control Drag Drop Events Handling

a.p.r. pillaiDecember 31, 2020ImageList Control, ListView Control, TreeView Control No comments

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.

Download Demo Database.

You may download the Demo database, do trial runs, and study the VBA Code.

Download ListViewDragDrop.zip

WISH YOU A VERY HAPPY NEW YEAR.

MS-ACCESS EVENT HANDLING

Tree View Control Check-Mark Add Delete Nodes

a.p.r. pillaiOctober 18, 2020ImageList, ImageList Control, TreeView Control No comments

Introduction.

Adding or Deleting Nodes in TreeView Control

In this episode of the TreeView Control Tutorial, we will explore how to add or delete nodes dynamically. Before performing these actions, we will determine the position of the selected node—whether the new node should be added at the same level (sibling) or as a child node. Similarly, when deleting, we will identify the currently selected node and remove it using the Remove() method.

This functionality makes the TreeView control more flexible, allowing users to build or modify hierarchical menus on the fly.

TreeView Control Tutorial Sessions So Far

The Normal View of the Demo Tree View Control on MS-Access Form, with other controls.

The Data Table for TreeView Control’s Demo.

We will once again make use of the Sample table that we introduced in the very first tutorial session. This small table contains records representing the structure of common Microsoft Access objects—such as Tables, Forms, and Reports—arranged in a hierarchical order. Its simplicity makes it an excellent source for demonstrating how to add or delete nodes in the TreeView control.

The ID column (used as the TreeView Key) is defined as an AutoNumber field.

Note for New Visitors:
To get up to speed with this session, please download the Demo Database from the second tutorial page: Creating Access Menu with Tree View Control. This database contains a table named Sample and a form named frmSample. You may import both into your current project.

Additionally, the database includes another form named frmMenu. From this form, copy the Expand All and Collapse All command buttons along with their Click Event procedures into the frmSample code module to continue smoothly with this session.

As mentioned earlier, the ImageList Control—with all the images uploaded in the last tutorial—can be shared across projects. We will make a copy of that ImageList Control and bring it into this session. It will then be linked to the TreeView control on frmSample, enabling us to display node images as before.

Importing the ImageList Control with Images.

You can bring an existing ImageList Control (with uploaded images) from another database into your active project in one of the following ways:

Import the entire form that contains the ImageList Control into your current database. Once imported, open the form, then copy and paste the ImageList Control onto the form where you want to use it.
Alternatively, copy the ImageList Control directly:
- Open the database where the ImageList Control with images already exists.
- Copy the ImageList Control to the Clipboard.
- If you have downloaded the Demo Database from the earlier tutorial session, you already have this control with all the uploaded images. After copying, close that database.
- Open your target database, open the form where you want the ImageList Control, and paste it onto the form.

Once the ImageList Control is in place, you can link it to your TreeView control. In the CreateTreeView() subroutine, add the following code lines to pass the ImageList object reference to the TreeView control:


Set TreeView0.ImageList = Me.ImageList0.Object

This ensures that the TreeView control can access and display the images stored in the ImageList.

After that, you can add the Image Key Names to the TreeView Nodes.Add() Method’s last two parameters.

These exercises we have already done in the earlier sessions. To remind you, we are using the Table and TreeView Control we created in the first Tutorial Session and implemented with the above-explained changes.

Displaying the Check-Marked Nodes Property Values.

The selected Node’s Key, Parent Key, and Text properties are displayed in the TextBoxes on the right-hand side of the TreeView control. In addition to that, a CheckBox (labeled Child Node), along with the Delete Node and Add Node command buttons, has been added to the form. Their functions will be explained shortly.

Displaying CheckBoxes on the TreeView Control

By default, the TreeView control does not display checkboxes. To enable them, you must set the appropriate property in the TreeView control’s Property Sheet.

However, it’s important to note that the TreeView’s built-in checkbox feature has limited functionality. The operations we are about to demonstrate could be performed without using checkboxes at all.

A more practical use of checkboxes in the TreeView would be for scenarios such as generating a report on selected branches of a company. In such a case, the user could tick the checkboxes next to the desired branch nodes in the Access Project Menu, and the report could then be prepared based on those selections.

Here, our aim is simply to introduce this feature and run a short demo to show how it works.
1. Open the Form containing the TreeView control in Design View.
2. Right-click on the TreeView control, highlight the TreeCtrl Object, and select Properties to open the Property Sheet.
3. In the Property Sheet, enable the CheckBox option by placing a check mark in its box.
4. Once enabled, checkboxes will be displayed next to each TreeView node, as shown in the image below.

The Demo TreeView Image - the first Image on this Page, Details.

Let us take a closer look at the Demo Form frmSample shown at the top of this page.

The TreeView Control and the top two command buttons—Expand All and Collapse All—function exactly as we saw in the last episode.
On the right side, under the heading Property Values, three text boxes display the Key, ParentKey, and Text values of the currently checked node.
The Delete Node command button removes the selected node. If the node has child nodes, those will be removed as well.
To add a new node, you must first edit the Text property in the Property Values section, replacing it with the text you want to assign to the new node.

Just above the Add Node button is a Child Node check box. Together, these two controls define where the new node will be inserted:

If Child Node is checked, the new entry will appear as a child of the selected node.
If the Child Node is not checked, the new node will be added at the same level as the currently selected node.

Understanding the Add Node Action.

For example, let’s say we want to add a new node for the Hyperlink field under the Fields parent group in the data type list.

In the demo image at the top of this page, notice that the Date Field node is currently selected (checkmarked). On the right-hand Property display, its details are shown as:

Key: X15
ParentKey: X4
Text: Date Field

To create a new Hyperlink node:

Change the Text property from Date Field to Hyperlink in the Property display text box.
Click the Add Node button.

The result will appear as shown below:

If you place the check mark on the Fields parent node and also check the Child Node option (above the Add Node button), the new node will be added under Fields as one of its child items. The result will be the same as in the previous example.

On the other hand, if you keep the check mark on the Date Field node (instead of the parent node) and then set the Child Node option before clicking the Add Node button, the new entry will be created as a child of Date Field.

The result is as shown below:

The Child Node will be created directly under the check-marked node.

When the Add Node action is triggered, a new record must first be created in the underlying Sample table. This record will contain the new Text value (for example, HyperLink) and the corresponding ParentID value.

The AutoNumber field in the table will then generate a unique ID for the new record. This value is retrieved and assigned as the Key for the new TreeView node, ensuring its uniqueness within the control.

The Add Node sub-Routine VBA Code is given below:

Private Sub cmdAdd_Click()
Dim strKey As String
Dim lngKey As Long
Dim strParentKey As String
Dim lngParentkey As Long
Dim strText As String
Dim lngID As Long
Dim strIDKey As String

Dim childflag As Integer
Dim db As DAO.Database
Dim strSql As String
Dim intflag As Integer
Dim tmpnode As MSComctlLib.Node

Dim i As Integer
i = 0
For Each tmpnode In tv.Nodes
    If tmpnode.Checked Then
       tmpnode.Selected = True
        i = i + 1
    End If
Next
If i > 1 Then
      MsgBox "Selected Nodes: " & i & vbCr & "Select only One Node to mark Addition.", vbCritical, "cmdAdd()"
    Exit Sub
End If

'Read Property Values from Form
strKey = Trim(Me![TxtKey])
lngKey = Val(Mid(strKey, 2))

strParentKey = Trim(Me![TxtParent])
lngParentkey = IIf(Len(strParentKey) > 0, Val(Mid(strParentKey, 2)), 0)

strText = Trim(Me![Text])

'Read child Node Option setting
childflag = Nz(Me.ChkChild.Value, 0)

intflag = 0

strSql = "INSERT INTO Sample ([Desc], [ParentID] ) "
If lngParentkey = 0 And childflag = 0 Then
    'Add Root-level Node, ParentKey is Blank
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & " "
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 1
ElseIf (lngParentkey >= 0) And (childflag = True) Then

    'Inserts a child Node to the Check-marked Node, here Key value used as ParentKey
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & lngKey
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 2
ElseIf (lngParentkey >= 0) And (childflag = False) Then
    'Inserts Node at the check-marked level, Add item under the same ParentKey
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & lngParentkey
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 3
End If

Set db = CurrentDb
db.Execute strSql

'Get newly created autonumber to use as Key
lngID = DMax("ID", "Sample")
strIDKey = KeyPrfx & CStr(lngID)

On Error GoTo IdxOutofBound

Select Case intflag
    Case 1
        'Add Root-level Node, ParentKey is Blank
        tv.Nodes.Add , , strIDKey, strText, "folder_close", "folder_open"
    Case 2
        'Inserts a child Node to the Check-marked Node, here Key value used as ParentKey
        tv.Nodes.Add strKey, tvwChild, strIDKey, strText, "left_arrow", "right_arrow"
    Case 3
        'Inserts Node at the check-marked level, Add item under the same ParentKey
        tv.Nodes.Add strParentKey, tvwChild, strIDKey, strText, "left_arrow", "right_arrow"
End Select
tv.Refresh

    'Erase Property Values from Form
        With Me
            .TxtKey = ""
            .TxtParent = ""
            .Text = ""
        End With

Set db = Nothing
cmdExpand_Click
 
cmdAdd_Click_Exit:
Exit Sub

IdxOutofBound:
    CreateTreeView
Resume cmdAdd_Click_Exit
End Sub

Let us now examine the VBA code behind the Add Node process.

Variable Declarations
After the local variables are declared, the code scans through the TreeView Nodes to identify checkmarks and counts the number of checked items.
- If more than one Node is checked, a message is displayed, and the procedure is aborted.
Node Selection
- Instead of check-marking, a Node can also be selected by directly clicking on it.
- In both cases, the Checked/Selected Node is passed as a parameter to the Event procedure.
- Using checkmarks is particularly useful when selecting multiple items—for example, choosing different sets of data for a report.
Property Extraction
- The checked Node’s Key, ParentKey, and Text values are read from the Form controls into the variables:
  - strKey
  - strParentKey
  - strText
- From these values, the numeric portions of ID and ParentID are extracted and stored in:
  - lngKey (Node ID)
  - lngParentKey (Parent Node ID)
- These will be used to build the SQL statement.
Child Node Option
- The value of the Child Node checkbox is saved in the variable ChildFlag.
SQL String Preparation
- Based on the selection and the Child Node option, three different SQL string variations are prepared:
  - Insert at the same level as the selected Node
  - Insert as a child Node under the selected Node
  - Insert as a child Node under the selected Node’s Parent

Node Creation Logic

When the Add Node button is clicked, the program determines the type of Node to be created based on the ParentID property value and the Child Node checkbox status.
1. Root-Level Node
  - Condition: ParentID is empty, and the Child Node option is not checked.
  - Action: A Root-Level Node is created because the user had check-marked a Root Node.
2. Child Node
  - Condition: ParentID >= 0 and the Child Node option is checked.
  - Action: A new Node is created as a Child of the check-marked Node.
    - The Key (ID) of the check-marked Node is used as the ParentID in the new record.
3. Sibling Node (Same Level)
  - Condition: ParentID >= 0 and Child Node option is not checked.
  - Action: A new Node is created at the same level as the check-marked Node, sharing the same Parent.
Control Flow
- The variable intFlag is assigned values 1, 2, or 3 depending on which of the above cases applies.
  - 1 → Root-Level Node
  - 2 → Child Node
  - 3 → Sibling Node
- The appropriate SQL INSERT statement is executed to add a new record to the Sample table.
- Access automatically generates a new AutoNumber ID for this record.
- Using DMax()The program retrieves this new ID and assigns it as the Key value for the new Node in the TreeView control.
- Finally, based on the intFlag value, the Node is created in the correct position (root, child, or sibling).
- The Property display TextBox values are cleared to reset the form for the next action.

Deleting Node or Node with Children.

The Delete Node Option is much easier than the earlier exercise. Simply deletes the Check-Marked Node and its Children, if present, from the Tree View Control. The related records are deleted from the Table.

The VBA Code for Node Removal is given below:

Private Sub cmdDelete_Click()
Dim nodId As Long, nodParent As Long
Dim strSql As String
Dim db As DAO.Database
Dim j As Integer
Dim tmpnode As MSComctlLib.Node
Dim strKey As String
Dim strMsg As String

j = 0 ' Get check-marked Nodes count
For Each tmpnode In tv.Nodes
    If tmpnode.Checked Then
        tmpnode.Selected = True
        strKey = tmpnode.Key
        j = j + 1
    End If
Next

   If j > 1 Then
      MsgBox "Selected Nodes: " & j & vbCr & "Select Only One Node to Delete.", vbCritical, "cmdDelete()"
      Exit Sub
   End If

Set tmpnode = tv.Nodes.Item(strKey)
tmpnode.Selected = True
Set db = CurrentDb

'check the presense of Child Node(s) of marked Node
If tmpnode.Children > 0 Then
'Warnings:
'       Deleting Nodes at Random will leave orphaned Nodes
'       in the Table and end up with errors, during next Tree View loading process
    strMsg = "The Marked Node have " & tmpnode.Children & " Children. " & vbCr & "Delete the Child Nodes also?"
    If MsgBox(strMsg, vbYesNo + vbCritical, "cmdDelete()") = vbYes Then
       'Double check and get confirmation.
       strMsg = "Delete Only the deepest set of Child Nodes" & vbCr
       strMsg = strMsg & "and their Parent Node at one time." & vbCr & vbCr
       strMsg = strMsg & "Are you sure to Proceed..?"
       If MsgBox(strMsg, vbYesNo + vbCritical, "cmdDelete()") = vbYes Then
            Do Until tmpnode.Children = 0
                nodId = Val(Mid(tmpnode.Child.Key, 2))
        'Delete Child Node
                tv.Nodes.Remove tmpnode.Child.Index
        'Delete the related record
                strSql = "DELETE Sample.*, Sample.ID FROM Sample WHERE (((Sample.ID)= " & nodId & "));"
                db.Execute strSql
            Loop
        Else
            Exit Sub
        End If
    Else
        Exit Sub
    End If
End If

        nodId = Val(Mid(tmpnode.Key, 2))
    'Delete Parent
       tv.Nodes.Remove tmpnode.Key
       tv.Refresh
    'Delete Marked Record
        strSql = "DELETE Sample.*, Sample.ID FROM Sample WHERE (((Sample.ID)= " & nodId & "));"
        db.Execute strSql
       
      
    'Erase Property Values from Form
        With Me
            .TxtKey = ""
            .TxtParent = ""
            .Text = ""
        End With
    Set db = Nothing
    
End Sub

Delete Node Logic

After the local variable declarations, the procedure to delete a Node follows these steps:

Count Check-Marked Nodes
- A For Each … Next loop scans through the Nodes.
- If more than one Node is check-marked, a message is displayed and the program is aborted.
- If exactly one Node is check-marked, the program continues.

Validation Check – Child Node Presence
- The program checks if the selected Node has Child Node(s).
- If Child Nodes are found:
  - A message is displayed to inform the user.
  - The user must reconfirm their intention before proceeding.
  - Deletion must occur from the deepest level first (child → parent → grandparent).

⚠️ Important Rule for Simplicity

Always delete the deepest-level Child Nodes first, or delete a parent with its immediate children.
Avoid deleting higher-level (grandparent) Nodes directly, as this can leave some Nodes orphaned.
Orphaned Nodes will cause errors the next time the TreeView is opened.

Delete Operation
- If Child Nodes exist:
  - Each Child Node is removed one by one, along with its corresponding record in the underlying table.
  - Finally, the selected Parent Node is deleted, both from the TreeView and the table.
- If no Child Nodes are present:
  - The selected Node is deleted immediately, and its corresponding table record is also deleted.

Cleanup
- After deletion, the Property display TextBox values (Key, ParentKey, and Text) are cleared to reset the form.

The Form frmSample’s Complete Class Module VBA Code.

Complete VBA Code in frmSample’s Class Module

Below is the complete code for the form frmSample, which includes:

Utility subroutines for Expanding and Collapsing Nodes
The TreeView0_NodeCheck() event procedure
The cmdExit_Click() event
The Form_Load() procedure
The CreateTreeView() subroutine
And the full implementation of the Add Node and Delete Node logic

Option Compare Database
Option Explicit

Dim tv As MSComctlLib.TreeView
Dim ImgList As MSComctlLib.ImageList
Const KeyPrfx As String = "X"

Private Sub cmdAdd_Click()
Dim strKey As String
Dim lngKey As Long
Dim strParentKey As String
Dim lngParentkey As Long
Dim strText As String
Dim lngID As Long
Dim strIDKey As String

Dim childflag As Integer
Dim db As DAO.Database
Dim strSql As String
Dim intflag As Integer
Dim tmpnode As MSComctlLib.Node

Dim i As Integer
i = 0
For Each tmpnode In tv.Nodes
    If tmpnode.Checked Then
       tmpnode.Selected = True
        i = i + 1
    End If
Next
If i > 1 Then
      MsgBox "Selected Nodes: " & i & vbCr & "Select only One Node to mark Addition.", vbCritical, "cmdAdd()"
    Exit Sub
End If

'Read Property Values from Form
strKey = Trim(Me![TxtKey])
lngKey = Val(Mid(strKey, 2))

strParentKey = Trim(Me![TxtParent])
lngParentkey = IIf(Len(strParentKey) > 0, Val(Mid(strParentKey, 2)), 0)

strText = Trim(Me![Text])

'Read child Node Option setting
childflag = Nz(Me.ChkChild.Value, 0)

intflag = 0

strSql = "INSERT INTO Sample ([Desc], [ParentID] ) "
If lngParentkey = 0 And childflag = 0 Then
    'Add Root-level Node, ParentKey is Blank
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & " "
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 1
ElseIf (lngParentkey >= 0) And (childflag = True) Then

    'Inserts a child Node to the Check-marked Node, here Key value used as ParentKey
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & lngKey
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 2
ElseIf (lngParentkey >= 0) And (childflag = False) Then
    'Inserts Node at the check-marked level, Add item under the same ParentKey
    strSql = strSql & "SELECT '" & strText & "' AS [Desc], '" & lngParentkey
    strSql = strSql & "' AS ParentID FROM Sample WHERE ((Sample.ID = 1));"
        intflag = 3
End If

Set db = CurrentDb
db.Execute strSql

'Get newly created autonumber to use as Key
lngID = DMax("ID", "Sample")
strIDKey = KeyPrfx & CStr(lngID)

On Error GoTo IdxOutofBound

Select Case intflag
    Case 1
        'Add Root-level Node, ParentKey is Blank
        tv.Nodes.Add , , strIDKey, strText, "folder_close", "folder_open"
    Case 2
        'Inserts a child Node to the Check-marked Node, here Key value used as ParentKey
        tv.Nodes.Add strKey, tvwChild, strIDKey, strText, "left_arrow", "right_arrow"
    Case 3
        'Inserts Node at the check-marked level, Add item under the same ParentKey
        tv.Nodes.Add strParentKey, tvwChild, strIDKey, strText, "left_arrow", "right_arrow"
End Select
tv.Refresh

    'Erase Property Values from Form
        With Me
            .TxtKey = ""
            .TxtParent = ""
            .Text = ""
        End With

Set db = Nothing
cmdExpand_Click
 
cmdAdd_Click_Exit:
Exit Sub

IdxOutofBound:
    CreateTreeView
Resume cmdAdd_Click_Exit
End Sub

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Private Sub cmdDelete_Click()
Dim nodId As Long, nodParent As Long
Dim strSql As String
Dim db As DAO.Database
Dim j As Integer
Dim tmpnode As MSComctlLib.Node
Dim strKey As String
Dim strMsg As String

j = 0 ' Get check-marked Nodes count
For Each tmpnode In tv.Nodes
    If tmpnode.Checked Then
        tmpnode.Selected = True
        strKey = tmpnode.Key
        j = j + 1
    End If
Next

   If j > 1 Then
      MsgBox "Selected Nodes: " & j & vbCr & "Select Only One Node to Delete.", vbCritical, "cmdDelete()"
      Exit Sub
   End If

Set tmpnode = tv.Nodes.Item(strKey)
tmpnode.Selected = True
Set db = CurrentDb

'check the presense of Child Node(s) of marked Node
If tmpnode.Children > 0 Then
'Warnings:
'       Deleting Nodes at Random will leave orphaned Nodes
'       in the Table and end up with errors, during next Tree View loading process
    strMsg = "The Marked Node have " & tmpnode.Children & " Children. " & vbCr & "Delete the Child Nodes also?"
    If MsgBox(strMsg, vbYesNo + vbCritical, "cmdDelete()") = vbYes Then
       'Double check and get confirmation.
       strMsg = "Delete Only the deepest set of Child Nodes" & vbCr
       strMsg = strMsg & "and their Parent Node at one time." & vbCr & vbCr
       strMsg = strMsg & "Are you sure to Proceed..?"
       If MsgBox(strMsg, vbYesNo + vbCritical, "cmdDelete()") = vbYes Then
            Do Until tmpnode.Children = 0
                nodId = Val(Mid(tmpnode.Child.Key, 2))
        'Delete Child Node
                tv.Nodes.Remove tmpnode.Child.Index
        'Delete the related record
                strSql = "DELETE Sample.*, Sample.ID FROM Sample WHERE (((Sample.ID)= " & nodId & "));"
                db.Execute strSql
            Loop
        Else
            Exit Sub
        End If
    Else
        Exit Sub
    End If
End If

        nodId = Val(Mid(tmpnode.Key, 2))
    'Delete Parent
       tv.Nodes.Remove tmpnode.Key
       tv.Refresh
    'Delete Marked Record
        strSql = "DELETE Sample.*, Sample.ID FROM Sample WHERE (((Sample.ID)= " & nodId & "));"
        db.Execute strSql
       
      
    'Erase Property Values from Form
        With Me
            .TxtKey = ""
            .TxtParent = ""
            .Text = ""
        End With
    Set db = Nothing
    
End Sub

Private Sub cmdExpand_Click()
Dim nodExp As MSComctlLib.Node

        For Each nodExp In tv.Nodes
            nodExp.Expanded = True
        Next

End Sub

Private Sub cmdCollapse_Click()
Dim nodExp As MSComctlLib.Node

        For Each nodExp In tv.Nodes
            nodExp.Expanded = False
        Next
End Sub

Private Sub Form_Load()
    CreateTreeView
    cmdExpand_Click
End Sub

Private Sub CreateTreeView()
Dim db As Database
Dim rst As Recordset
Dim nodKey As String
Dim ParentKey As String
Dim strText As String
Dim strSql As String

Set tv = Me.TreeView0.Object
tv.Nodes.Clear

'Pass ImageList control reference to TreeView's ImageList Property.
Set ImgList = Me.ImageList0.Object
tv.ImageList = ImgList

strSql = "SELECT ID, Desc, ParentID FROM Sample;"

Set db = CurrentDb
Set rst = db.OpenRecordset("sample", dbOpenTable)
Do While Not rst.EOF And Not rst.BOF
    If Nz(rst!ParentID, "") = "" Then
        nodKey = KeyPrfx & CStr(rst!ID)
        strText = rst!desc
        tv.Nodes.Add , , nodKey, strText, "folder_close", "folder_open"
    Else
        ParentKey = KeyPrfx & CStr(rst!ParentID)
        nodKey = KeyPrfx & CStr(rst!ID)
        strText = rst!desc
        tv.Nodes.Add ParentKey, tvwChild, nodKey, strText, "left_arrow", "right_arrow"
    End If
rst.MoveNext
Loop

rst.Close
On Error GoTo 0
Set rst = Nothing
Set db = Nothing

End Sub

Private Sub TreeView0_NodeCheck(ByVal Node As Object)
Dim xnode As MSComctlLib.Node

Set xnode = Node
  If xnode.Checked Then
    xnode.Selected = True

    With Me
        .TxtKey = xnode.Key
      If xnode.Text = xnode.FullPath Then
        .TxtParent = ""
      Else
        .TxtParent = xnode.Parent.Key
      End If
        .Text = xnode.Text
    End With
  Else
    xnode.Selected = False
    With Me
      .TxtKey = ""
      .TxtParent = ""
      .Text = ""
    End With
End If
End Sub

📌 Notes for Readers:

The Add Node and Delete Node button procedures should contain the full logic we explained in the last two sections (SQL insert for new records, DMax() to fetch ID, and deletion with child validation).
In this complete listing, I’ve left placeholders (' (Full Add Node logic goes here)) so you can easily copy and paste the previously explained code blocks in.

The Design View of the frmSample Form is given below: