Creating Access Menu with Tree View Control ~ LEARN MS-ACCESS TIPS AND TRICKS

The Microsoft Access Project Menu, after completing the Tree View Control, will resemble the image shown below.

In the image above, the third option under the Report Group, Custom Report, is selected and highlighted. The Report Filter Parameter Form is open, overlapping the Menu Screen, ready for the user’s input.

Before we get into that, let’s briefly revisit last week’s lesson. We explored how to organize related items into a hierarchical structure using the Microsoft TreeView control, based on a sample data table.

Last week, I emphasized that related items in the TreeView control’s data do not need to appear next to each other in the table. In this session, we will take that concept further by showing how to update the Relative Keys of child nodes—regardless of the physical position of the records in the table—purely based on their relationship to their parent node IDs.

Here is the data table we worked with and completed in last week’s exercise:

Can you add the following list of items at the end of the above table and update their ParentID field values, so that the TreeView Control display will look like the sample image given below:

New records for the Table item record related field:

Text Field.
Number Field.
Date/Time Field.
Hyperlink Field.

Form-related Controls:

Text Box.
Command Buttons.
Combo Box.
List Box.

Report-related Controls:

Text Box.
Label.
Graph Chart.

Assign ParentID Values to these items so that the Tree View Display looks like the following Image:

Now, we will proceed with the creation of an MS-Access Project Menu and learn what it takes to create one. A simple Menu Image is given below:

This is a simple menu containing three main groups of options: Forms, Report Views, and Macros.

Forms Group
- The first option displays the TreeView control’s menu table records.
- The second option presents the same records in Continuous Form view.
Report Views Group
- The first option generates a report of product categories from the Categories table in the NorthWind.accdb database.
- The second option displays the Products List-Price Report.
- The third option opens a Parameter Form, allowing the user to specify a minimum and maximum list price range to filter the Products List-Price Report data.
Macros Processes Group
- The two options in this group run Macro1 and Macro2, respectively, each displaying a different message.

To implement this menu, we need a Menu Table containing the above option records, along with some additional fields beyond the standard TreeView fields—Unique ID, Description, and ParentID. The image below shows the structure of this menu table:

Create a Table with the above structure, add the above records, and save it with the name Menu. The ID field is an Auto Number, the PID and Type fields are numeric, while the remaining fields are text.

We are already familiar with the first three fields, Unique ID, Description, and ParentID. Here, the ParentID field name has been shortened to PID for convenience.

We now need four additional fields in the Menu Table:

Type – Stores the Access object type code.
Form – Stores form names (object type code: 1).
Report – Stores report names (object type code: 2).
Macro – Stores macro names (object type code: 3).

Note: While all object names could be stored in a single column, we are using separate fields for clarity. If you decide to use a single column, remember to adjust the VBA code wherever the field names are referenced.

The Type field helps us determine which action to take when a user clicks on an option:

If the type is 1, open the form using DoCmd.OpenForm.
If the type is 2, open the report using DoCmd.OpenReport.
If the type is 3, run the macro using DoCmd.RunMacro.

When we start adding nodes to the TreeView control, we will store both the Type Code and the Object Name for each child node.

Additional Required Tables and Objects.

We will also need two additional data tables—Categories and Products—from the NorthWind sample database.

To save time, a demo database with all required objects and programs is provided at the end of this page for download and testing.

Creating the Forms and Reports

Create two forms based on the Menu Table:
- Data Entry (single form view)
- Data View (continuous form view)
Create two reports:
- Categories – Based on the Categories table.
- Products Listing – Based on the Products table.
  - Add a long label below the main heading and set its Name property to Range.
Create a small form for filtering products:
- Add two unbound text boxes.
- Set their Name property values to Min and Max.
- Design it as shown in the sample layout below.

Add two Command Buttons as shown above. Change the Caption Property Value of the first Button to Open Report and the Name Property Value to cmdReport.

Change the Second Command Button’s Caption to Cancel and the Name Property value to cmdCancel.

Display the Code Module of the Form. Copy and Paste the following Code into the Form Module and save the Form:

Private Sub cmdOpen_Click()
Dim mn, mx, fltr As String
mn = Nz(Me![Min], 0)
mx = Nz(Me![Max], 9999)
If (mn + mx) > 0 Then
    fltr = "[List Price] > " & mn & " And " & "[List Price] <= " & mx
    DoCmd.OpenReport "Products Listing", acViewReport, , fltr, , fltr
Else
    DoCmd.OpenReport "Products Listing", acViewReport
End If

End Sub

Private Sub cmdCancel_Click()
DoCmd.Close
End Sub

When the user specifies a value range by entering the Minimum and Maximum list prices in their respective text boxes, a report filter criterion string is created.

This filter string is:

Passed to the Products Listing report as a filter parameter in the DoCmd.OpenReport command.
Also sent as the OpenArgs (open argument) parameter.

The filter parameter limits the report data to records that fall within the specified Min and Max price range.
The OpenArgs value is used to update the Range label’s caption when the report opens, so the user can see the filter range they applied.

Copy and paste the following VBA code into the Products Listing report’s code module:

Private Sub Report_Open(Cancel As Integer)
    DoCmd.Close acForm, "Parameter"
    Me.Range.Caption = Nz(Me.OpenArgs, "")
End Sub

Create a new form, with the name frmMenu, and add the Microsoft TreeView Control from the ActiveX Controls List. Resize the Control as shown in the Design View below:
Change the Tree View control’s name to TreeView0 in the standard Property Sheet.

Add a Command Button below the Tree View control.
- Set its Name property to cmdExit.
- Set its Caption property to Exit.
Right-click the Tree View control, choose TreeCtrl_Object, and select Properties to open its Property Sheet.

Update the following property values:
- Style = 7 (tvwTreeLinesPlusMinusPictureText)
- Line Style = 1 (tvwRootLines)
- LabelEdit = 1 (tvwManual)
Last week, we already updated the first two property values.

By default, the LabelEdit property is set to 0 – tvwAutomatic. In that mode, clicking a node twice (not double-clicking) puts the node text into edit mode, allowing you to change it. However, this does not update the underlying data source field.

Changing the value to 1 – tvwManual disables this automatic edit mode, preventing accidental changes to node text.

You can also make this change programmatically by adding the following lines to the Form_Load() event procedure:

With Me.TreeView0.Object
    .Style = tvwTreelinesPlusMinusPictureText
    .LineStyle = tvwRootLines	
    .LabelEdit = tvwManual
End With

Last week, we used the Form_Load() event procedure to read the Tree View node values and create both the root-level and child nodes. We will use the same procedure here, with a few additional lines of code.

In addition, we need to handle the Node_Click() event to determine which option the user has selected.

Copy and paste the following VBA code into the form’s module, then save the form:

Option Compare Database
Option Explicit

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

Private Sub Form_Load()
Dim db As Database
Dim rst As Recordset
Dim nodKey As String
Dim PKey As String
Dim strText As String
Dim strSQL As String

Dim tmpNod As MSComctlLib.Node
Dim Typ As Variant

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


‘Change the TreeView Control Properties
With tv
    .Style = tvwTreelinesPlusMinusPictureText
    .LineStyle = tvwRootLines
    .LabelEdit = tvwManual
    .Font.Name = "Verdana"
End with

    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 Nz(rst!PID, "") = "" Then
        nodKey = KeyPrfx &  CStr(rst!ID)
        strText = rst!Desc
      Set tmpNod = tv.Nodes.Add(, , nodKey, strText)
      
      'Root-Level Node Description in Bold letters
      With tmpNod
        .Bold = True
      End With
    Else
        PKey = KeyPrfx & CStr(rst!PID)
        nodKey = KeyPrfx & CStr(rst!ID)
        strText = rst!Desc
        Set tmpNod = tv.Nodes.Add(PKey, tvwChild, nodKey, strText)
     
     'Check for the presense of Type Code
        If Nz(rst!Type, 0) > 0 Then
            Typ = rst!Type
            Select Case Typ
                Case 1 'save type Code & Form Name in Node Tag Property
                    tmpNod.Tag = Typ & rst!Form
                Case 2 'save type Code & Report Name in Node Tag Property
                    tmpNod.Tag = Typ & rst!Report
                Case 3 'save type Code & Macro Name in Node Tag Property
                    tmpNod.Tag = Typ & rst!Macro
            End Select
      End If
        
    End If
    rst.MoveNext
Loop
rst.Close

Set rst = Nothing
Set db = Nothing

End Sub

Private Sub cmdExit_Click()
If MsgBox("Close Menu Form? ", vbYesNo, "cmdExit_Click()") = vbYes Then
    DoCmd.Close
End If
End Sub

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim varTag, typeid As Integer
Dim objName As String, nodOn as MSComctlLib.Node

If Node.Expanded = False Then
    Node.Expanded = True
Else
    Node.Expanded = False
End If


‘Reset the earlier highlight to normal
For Each nodOn In tv.Nodes
	nodOn.BackColor = vbWhite
    nodOn.ForeColor = vbBlack
    
    ‘changes BackColor to light Blue and ForeColor White
    tv.Nodes.Item(Node.Key).BackColor = RGB(0, 143, 255)
    tv.Nodes.Item(Node.Key).ForeColor = vbWhite
    
    ‘—Highlight code ends-
    varTag = Nz(Node.Tag, "")
    
If Len(varTag) > 0 Then
    typeid = Val(varTag)
    objName = Mid(varTag, 2)
End If

Select Case typeid
    Case 1
        DoCmd.OpenForm objName, acNormal
    Case 2
        DoCmd.OpenReport objName, acViewPreview
    Case 3
        DoCmd.RunMacro objName
End Select
End Sub

The Tree View object is declared in the global declaration area of the module. A constant variable, KeyPrfx, is defined with the value "X".

The Form_Load() event procedure from last week’s article has been updated with additional code. I have added comments to the new segments to indicate their purpose, and I will explain them in detail.

The procedure begins by declaring the Database, Recordset, and four String variables. The next two lines declare a temporary Node object (tmpNod) and a Variant variable named Typ.

The TreeView object tv is then set to reference the TreeView0 control on the form. Any existing nodes in TreeView0 are cleared using: tv.nodes.clear statement

tv.nodes.clear

This ensures we start with a clean slate before reloading all nodes.

Finally, we have implemented code to modify the Tree View control’s properties programmatically, instead of setting them manually in the property sheet.

With tv
    .Style = tvwTreelinesPlusMinusPictureText
    .LineStyle = tvwRootLines
    .LabelEdit = tvwManual
    .Font.Name = "Verdana"
End With

The Tree View font is set to Verdana for a cleaner look. In addition, we have implemented functions that allow all menu groups to be expanded or collapsed with a single click, instead of manually expanding or collapsing each group individually.

The SQL string has been updated to include the new fields—Type, Form, Report, and Macro—from the Menu table.

When the procedure runs, the first record in the Menu table is checked for a PID value.

If the PID field is empty, it is treated as a root-level node. The record is added to the Tree View as a root node, and its reference is stored in the tmpNod object. To visually distinguish root nodes from child nodes, the Bold property is set to True.
If the PID field contains a value, the record is treated as a child node. In this case, the program checks the Type field value. If it contains 1, 2, or 3, the corresponding object name is taken from the Form, Report, or Macro field, prefixed with its Type code (e.g., "1Data Entry", "2Categories", "3MyMacro"), and stored in the node’s Tag property.

The Tag property—available in most Access controls such as text boxes, labels, and command buttons—allows us to store hidden, custom data with each control. In this case, it serves as a convenient way to store both the Type code and object name for later use.

The cmdExit_Click() procedure prompts the user for confirmation and closes the menu form if the response is affirmative.

When a user clicks a child node, the program retrieves the value stored in the node’s Tag property. Based on the Type code, it determines whether to open a form, run a report, or execute a macro. This logic is implemented in the TreeView0_NodeClick() event procedure.

Private Sub TreeView0_NodeClick(ByVal Node As Object)
Dim varTag, typeid As Integer
Dim objName As String, nodOn as MSComctlLib.Node

If Node.Expanded = False Then
    Node.Expanded = True
Else
    Node.Expanded = False
End If

‘Reset the earlier lighlight to normal
For Each nodOn In tv.Nodes
  nodOn.BackColor = vbWhite 
  nodOn.ForeColor = vbBlack
Next nodOn

‘changes BackColor to light Blue and ForeColor White
    tv.Nodes.Item(Node.Key).BackColor = RGB(0, 143, 255)
    tv.Nodes.Item(Node.Key).ForeColor = vbWhite
‘—Highlight code ends-

varTag = Nz(Node.Tag, "")
If Len(varTag) > 0 Then
    typeid = Val(varTag)
    objName = Mid(varTag, 2)
End If

Select Case typeid
    Case 1
        DoCmd.OpenForm objName, acNormal
    Case 2
        DoCmd.OpenReport objName, acViewPreview
    Case 3
        DoCmd.RunMacro objName
End Select
End Sub

The Click() event procedure receives a reference to the clicked node through the Node parameter. At the start of the procedure, a few variables are declared for later use.

The next section checks whether the clicked node is currently expanded or collapsed.

Normally, to expand a node and reveal its child nodes, you either click the plus (+) symbol to the left of the node or double-click the node itself. Likewise, double-clicking the node again or clicking the minus (–) symbol collapses it, hiding the child nodes.

With the following code segment, this process is simplified—allowing you to expand or collapse a node with a single click instead of the usual two-step interaction.

If Node.Expanded = False Then
    Node.Expanded = True
Else
    Node.Expanded = False
End If

The next six executable lines ensure that the Node that received the Click remains highlighted.

‘Reset the earlier Highlight to Normal
For Each nodOn In tv.Nodes
  nodOn.BackColor = vbWhite 
  nodOn.ForeColor = vbBlack
Next nodOn
‘Changes BackColor to light Blue and ForeColor White
    tv.Nodes.Item(Node.Key).BackColor = RGB(0, 143, 255)
    tv.Nodes.Item(Node.Key).ForeColor = vbWhite
‘—Highlight code ends-

Next, the varTag variable is assigned the value from the node’s Tag property. If this value is not empty, it is split into two parts:

The numeric portion is extracted and stored in the Typid variable.
The object name portion is stored in the objName variable.

Based on the value of 'Typid'The appropriate DoCmd action is executed to open a form, open a report, or run a macro.

We will also add two additional command buttons at the top of the menu:

One button to expand all nodes with a single click.
Another button to collapse all nodes with a single click.

Place these two buttons in the top area of the TreeView control, as shown in the design layout below.
Change the Name Property Value of the left Command Button to cmdExpand and the Caption to Expand All.
Similarly, change the right-side Command Button’s Name Property to cmdCollapse and the Caption to Collapse All.
Copy and paste the following VBA Code below the existing Code in the frmMenu Form Module and save the Form.

Private Sub cmdExpand_Click()
Dim Nodexp As MSComctlLib.Node

For Each Nodexp In tv.Nodes
    If Nodexp.Expanded = False Then
        Nodexp.Expanded = True
    End If
Next Nodexp
End Sub


Private Sub cmdCollapse_Click()
Dim Nodexp As MSComctlLib.Node

For Each Nodexp In tv.Nodes
    If Nodexp.Expanded = True Then
        Nodexp.Expanded = False
    End If
Next Nodexp
End Sub

At the beginning of the cmdExpand_Click() event procedure, we declare a TreeView Node object named NodExp. The For…Next loop then iterates through each node, checking whether it is already expanded. If a node is not expanded, its Expanded property is set to True.

The cmdCollapse_Click() event follows the same approach. However, instead of expanding nodes, it checks if they are currently expanded, and if so, sets their Expanded property to False.

With these two commands, you can:

Instantly expand the entire TreeView, making all child nodes visible.
Collapse all child nodes so that only the root-level nodes remain visible.

If you have followed each design step carefully, your menu should now look like the finished menu image shown at the top.

Back in 2007, I designed a similar menu for a Vehicle Service Contract System project. That version used a Tab Control with multiple pages. Each page displayed 10 or more options, and the user could switch pages by clicking command buttons arranged along either side of the menu. The command buttons on the right would change dynamically based on the selection made from the left side—creating a flexible, context-sensitive menu system.