TreeView ImageCombo Drop-Down Access Menu

Introduction.

In this session of the TreeView Control Tutorial, we will focus on programming the ImageComboBox Control. Our objective is to build an MS Access project drop-down menu using an ImageCombo control. Additionally, we will use a second ImageComboBox control to display the images along with their key values from the ImageList control. Both ImageComboBox controls will draw their images from a common ImageList control, into which the images were manually uploaded from the computer during an earlier session of this tutorial series.

The sessions of the TreeView Control Tutorial covered so far are as follows:

The TreeView Control Tutorial Session Links.

1. Microsoft TreeView Control Tutorial
2. Creating an Access Menu with a TreeView Control
3. Assigning Images to TreeView Control
4. Assigning Images to TreeView Control-2
5. TreeView Control Check-Mark Add, Delete Nodes

The Demo View of Both ImageComboBoxes Expanded.

The completed MS-Access Project Drop-Down Menu Image is given below:

The Design View Image of the above Form is given below:

The Drop-Down ImageComboBox Source Data.

The source data for the new MS Access Project Drop-Down Menu is taken from our earlier Access Menu Project.

If you have not already done so, please download the demo database using the link provided in item 4 above. Once downloaded, you will have all the required data objects to proceed with this session.

The database includes three tables: Categories, Products, and Menu. It also contains two forms to display category and product data, along with a parameter form for filtering report data.

Additionally, we will utilize two forms—frmMenu and frmMenu2—which were introduced in our earlier tutorial sessions.

You will also find two reports for previewing the Categories and Products data items.

In addition, there are two macros designed to display simple messages. These macros can also be used to sequence action queries for data processing, particularly when working with more complex reports. In earlier tutorial sessions, we executed these actions by selecting options from the TreeView Control 

Project Menu.

We will also need these objects here, as we are about to create a new drop-down menu using the ImageComboBox Control. The goal is to ensure that all objects can be opened through selections in the new drop-down menu, in the same way we accessed them earlier on the frmMenu2 form using the TreeView Control within this same database.

The Menu table image is given below for your reference.

Preparing for the Design of the Drop-Down Menu Form.

Open the Menu Form in Design View, and you will see two menu-related controls:

• An ImageList control

• An ImageComboBox control

Additionally, another ImageComboBox control has been placed on the right side of the form to display the images stored in the ImageList.

To set this up:

1. Copy the ImageList Control

   • Open the frmMenu2 form, copy the ImageList control, and paste it onto a new form named frmMenu3Combo.

   • This ImageList control already contains images that were manually uploaded from the computer in an earlier tutorial session.

   • Open the Property Sheet: right-click the ImageList control, select ImageListCtl Object, and then choose Properties to review the images and their key names.

2. Add the First ImageComboBox Control

   • From the ActiveX Controls group, insert a Microsoft ImageComboBox Control onto the left side of the form.

   • Rename this control to imgCombo1. This will serve as the drop-down menu.

3. Add the Second ImageComboBox Control

   • Insert another ImageComboBox control on the right side of the form.

   • Rename this control to imgCombo2. This control displays the images and their key names from the ImageList0 control in a drop-down list.

4. Add a Label

   • Place a label above the second ImageComboBox control.

   • Set its caption to Image List.

The Images Listed in the Image Combo Box control.

We will begin with the second control, imgCombo2, which is used to display the list of images from the ImageList control. Once you understand this code, creating the drop-down menu will become much easier.

The VBA code for the frmMenu3Combo form module has been divided into two parts. Let us start with the first part and examine its contents.

In the global declaration area, the main object variables are defined. The Form_Load() event procedure initializes the ImageList control on the form and assigns it to the object variable 'objImgList'. It then calls the cboImageList() subroutine, which loads the images from the ImageList control into the second ImageComboBox control (imgCombo2).

Now, let’s take a closer look at the code.

The first-part vba code, with the Form_Load() and cboImageList() subroutines listed below:

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

VBA Code Review.

In the global declaration area, we have defined the following variables:

• imgCombo1 – the ImageComboBox control used for the project menu.

• imgCombo2 – the ImageComboBox control used for displaying images from the ImageList control.

• objImgList – an object variable representing the ImageList control on the form.

• KeyPrfx – a constant variable assigned the character "X".

Within the Form_Load() event procedure, the objImgList variable is initialized with the ImageList control on the form using the following statement:

Set objImgList = Me.ImageList0.Object

This allows all pre-loaded images in the ImageList control to be accessed through the objImgList object.

Next, the procedure calls the cboImageList() subroutine, which adds all the images to the imgCombo1 control.

For now, the call to the CreateMenu() subroutine has been commented out.

Inside the cboImageList() subroutine, two variables are declared.

Next, the following statement assigns the second ImageComboBox control on the form to the object variable imgCombo2:

Set imgCombo2 = Me.ImageCombo2.Object

Similar to the TreeView control, the imgCombo2 control includes an ImageList property, which is used to link it with the ImageList control. This allows the ImageComboBox to access the properties of the ImageList. The statement below establishes that link:

imgCombo2.ImageList = objImgList

After this, a 'For…Next loop' is executed to iterate through the collection of images contained in the ImageList control. Each image is then processed and added to the imgCombo2 drop-down list.

The first item from the ImageList has its key value ("form_close") stored in the variable strText. In this example, we are using the Key value of the ImageList item as the text (or description) for the corresponding image in the ImageComboBox control.

Since no descriptive text is available apart from the key, it serves as the most suitable option. The Tag property, on the other hand, is left empty because it is for a different purpose later when working with the drop-down menu.

The next statement is an important one that we need to examine closely: the Add method of the ImageComboBox control. This method is used to add items (with images) to the ImageComboBox list.

The general syntax of the statement is as follows:

imgCombo2.ComboItems.Add [Index],[Key],[Text],[Image],[SelImage],[Indentation]

All the parameters of the Add() method are optional. For our initial test run of this control, we will supply values only for the [Text], [Image], and [Indentation] parameters.

After previewing the result of this first test run (the image list view), we will discontinue using the [Indentation] parameter for this ImageCombo control, since it is not required for our intended design.

Note: Keep in mind that we will later need the Indentation property when creating the drop-down menu. This will allow the menu items to visually resemble Root Nodes and Child Nodes, just as they appear in the TreeView control.

At that stage, we will also make use of the [Key] parameter—assigning it the same value as the Text parameter—so that we can reliably access a specific menu item’s Tag property value.  

Image List with incrementing Indentation Param Setting.

The result of the first test run, displaying the image list in imgCombo2, appears as shown in the illustration below. This output is achieved by applying incremental values to the Indentation property for each item.

From this trial run, the effect of indentation is clearly visible: each successive item is shifted slightly to the right, one step further than the previous one. This feature is useful for positioning our project menu items so that they visually resemble Root-Level and Child Nodes, similar to the structure in the TreeView control.

After the strText value ("form_close"), The first variable j refers to the ImageList’s index number. The [SelImage] parameter is skipped in our test, and the next occurrence of j is used to set the Indentation level for each list item when displayed in the ComboBox.

For the initial test run, after reviewing the output, you may remove all parameters that come after the image index value, as they are not required for our purpose.

The next statement is:

imgCombo2.ComboItems(1).Selected = True

This selects the first item in the ComboBox. When the Combobox item is selected programmatically  (through code), the Change() event of the ImageCombo control is triggered. However, when an item is selected manually on the form, this event is not fired. To address this, the Update() event is used; it ignores manual updates and instead attempts to invoke the event explicitly through code.

Save the form frmMenu3Combo and open it in Normal View.  Expand the second ImageList ComboBox control and view the result.  Remove the commas and the variable < j > at the end, after the first variable < j >, retained for the ImageList index number.

The VBA Code of the Project Drop-Down Menu.

Now, let us move on to the second part of the form module VBA code, where we will learn how to create the Access Drop-Down Menu. In this section, we will also see how to open Forms, Reports, and Macros by selecting an item from the ComboBox control.

The second part of the VBA code consists of two main procedures:

1. CreateMenu() – a subroutine that builds the project drop-down menu by adding menu items to the ImageComboBox control.

2. ImageCombo1_Click() – the event procedure that responds when a user selects a menu option, triggering the opening of the corresponding Access object.

The code for these procedures is shown below:

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

Before diving into the VBA code, take a moment to review the Table Image (the third image from the top of this page), especially if you have not already done so in the earlier session on Access Project Menu creation.

The table fields are defined as follows:

• ID – An AutoNumber field that provides a unique identifier for each record.

• Desc – Contains either the object type group names (Forms, Reports, Macros) or the actual object names of forms, reports, and macros.

• PID (Parent ID) – Empty for the object group names (Forms, Reports, Macros). These empty PID values ensure that the group names appear at the leftmost position in the ImageComboBox drop-down menu, with one-character space indentation. All other items (child objects) are indented by four character spaces.

This indentation technique makes the menu items visually resemble Root-Level and Child-Level Nodes, similar to the structure in a TreeView control—except that the connecting tree lines will not be displayed.

In the ImageComboBox control, the selected item’s image is positioned at the leftmost side. The Group items (such as Forms, Reports, Macros) appear with a one-character indentation, while the child items under each group are indented by four character spaces.

The PID field plays an important role in this arrangement.

• If the PID field value is empty, the record is treated as a Group Name (e.g., Forms, Reports, Macros).

• If the PID field contains a value, the record is treated as an Access Object Name, which must be opened when the user clicks it. These items are displayed as child members of their respective groups.

Although the actual key values in the PID field are not significant in this context, we need the field to determine the hierarchy. Alternatively, the Type field can also serve this purpose.

The Type field contains the object type codes:

• 1 – Form

• 2 – Report

• 3 – Macro

Based on this code, the next three fields—Form, Report, and Macro—store the actual object names. For clarity in design, these names have been kept in separate fields, though they could also be stored in a single column.

The Type Code and Object Name pair (say 2rptCategories) will be saved in the ImageComboBox’s Tag Property.

The CreateMenu() Subroutine.

Now, let’s move on to the VBA code of the CreateMenu() subroutine.

At the start of the procedure, the Database and other working variables are declared.

1. The imgCombo1 object variable is initialized with the Me.ImageCombo1 control on the form.

2. The 'imgCombo1.ImageList' property is then assigned the reference of the objImgList object, enabling direct access to the ImageList’s Index numbers and Key values.

3. The Menu table is opened as a recordset using an SQL string.

4. For each record, the PID field is checked:

   • If PID is empty, the record is treated as an object group name (e.g., Forms, Reports, Macros).

   • In this case, the ID value is prefixed with the constant "X" and stored in the variable strKey, while the Desc field value is stored in strText.

5. The Add() method of the ImageComboBox control is then called, and the first item is added to the drop-down menu.

---

imgcombo1.ComboItems.Add , strKey, strText, 1, 2, 1

The first parameter (Index) is omitted—Access assigns it automatically.

• Key: strKey contains the ID value prefixed with the constant X (e.g., X1).

• Text: strText holds the Desc field value.

• Image / SelImage: 1 is the index (or "folder_close" key) of the first ImageList image; 2 is the index (or "folder_open" key) of the second image.

• Indentation: 1 Indents the item one level.

Example:

' Add a group item (e.g., "Forms")
imgCombo1.ComboItems.Add , strKey, strText, 1, 2, 1
' …or with image keys:
' imgCombo1.ComboItems.Add , strKey, strText, "folder_close", "folder_open", 1

Note: To confirm image index order, refer to the ImageCombo2 display you created earlier. If you like, you can also prepend the image index to the Text (not the Key) for clarity—for example: "1 form_close", "2 form_open".

@@@If the PID field value is non-zero, then the record represents an actual menu option (not a group header). In this case, the item is added under the Else clause.

The process is similar to how we added the group items earlier, but with a few differences:

• For the [Image] and [SelImage] parameters, we use the ImageList item index values 4 and 5.

• The Indentation parameter is set to 4 character spaces, so that these items appear as child members under their respective group headings.

Example:

' Add a child item (e.g., an actual Form, Report, or Macro)
imgCombo1.ComboItems.Add , strKey, strText, 4, 5, 4 

This ensures that the group names (e.g., Forms, Reports, Macros) stay at the root level, while their associated objects are properly indented beneath them, visually simulating a TreeView hierarchy in the ImageComboBox.

Within the ImageCombo item’s Add() method, under the Else clause, we also need to store the Access Object Name along with its Type Code in the item’s Tag property.

For example:

' Save object details (Form Name + Type Code) in the Tag property
ImageCombo1.ComboItems.Item(strKey).Tag = "frmData Entry;1"

Here:

• "frmData Entry" is the object name (in this case, a form).

• 1 is the Type Code, which identifies it as a form (2 = report, 3 = macro).

When the user selects this item from the drop-down menu, the Click() event of the ImageComboBox fires. In this event procedure:

1. The Tag property value of the selected item is retrieved.

2. The value is split into the object name and the Type Code.

3. The Type Code is checked:

   • If it equals 1, the item is a form, and the form name is opened using:

   DoCmd.OpenForm "frmData Entry"
   

This same logic will later be extended for Reports and Macros, using their respective Type Codes.

@@@With this, all the records from the Menu Table are successfully added to the ImageComboBox control.

The statement:

imgCombo1.ComboItems.Item(1).Selected = True

sets the first item as the default selection in the ImageComboBox. When this line of code executes, the Change() event is triggered; however, selecting an item directly in Form View does not fire the Change event.

Note: Before running the form to test the drop-down menu, make sure to remove the comment symbol from the CreateMenu call in the Form_Load() event procedure. This line was commented out earlier during trial runs when we were testing image display in the ImageCombo2 control.

The ImageCombo1_Click() event fires whenever the user selects an item from the drop-down menu. In this procedure, the selected item’s Tag property is parsed to retrieve the Type Code and the Object Name, and the corresponding Access object is opened using:

DoCmd.ObjectType ObjectName

Finally, for your reference and practice, the Demo Database (ProjectMenuV221.accdb) is provided in .zip format for download.

Download ProjectMenuV221.zip

DICTIONARY OBJECT

1. Dictionary Objects Basics
2. Dictionary Object Basics-2
3. Sorting Dictionary Object Keys and Items
4. Display Records from Dictionary
5. Add Class Objects as Dictionary Items
6. Update Class Object Dictionary Item

Read More

Tree View Control Check-Mark Add Delete Nodes

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

1. Microsoft TreeView Control Tutorial
2. Creating an Access Menu with a TreeView Control
3. Assigning Images to TreeView Control
4. Assigning Images to TreeView Control-2

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:

1. 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.

2. 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.  

1. 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:

1. Change the Text property from Date Field to Hyperlink in the Property display text box.

2. 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.

1. 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.

2. 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.

3. 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.

4. Child Node Option

   • The value of the Child Node checkbox is saved in the variable ChildFlag.

5. 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

1. 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:

1. 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.

---

2. 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.

---

3. 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.

---

4. 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:

Your Observations, comments, and suggestions are welcome.

The Demo Database is attached for Download.

Download AddDelete.zip

DICTIONARY OBJECT

1. Dictionary Objects Basics
2. Dictionary Object Basics- 2
3. Sorting Dictionary Object Keys and Items
4. Display Records from Dictionary
5. Add Class Objects as Dictionary Items
6. Update Class Object Dictionary Item

Read More