Creating Access Menu with Tree View Control

Best Microsoft Access Programmer Portland OR

Creating Access Menu with Tree View Control

The Microsoft Access Project Menu, when finished with the Tree View Control, will look like the Image given below.

The Image above shows the Report Group’s third Option Custom Report is selected and highlighted, with the Report Filter Parameter Form open, overlapping the Menu Screen, for User’s input.

Before going into that, in last Week’s Lesson we have learned how to organize the related items in hierarchical order, using Microsoft Tree View Control, based on the Sample  data Table. 

I have made a point last week, that the related items in Tree View control’s data need not necessarily be next to each other.  After this you will be more clear about as how to update Relative Keys of Child Nodes, irrespective of it’s physical position of the records in the Table, but based on the relationship with it’s Parent Node IDs. 

This was the Data Table that we have used and finished with 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 display look like the sample image given below:

New records for Table item record related field:

  1. Text Field.
  2. Number Field.
  3. Date/Time Field.
  4. Hypelink Field.

Form related Controls:

  1. Text Box.
  2. Command Buttons.
  3. Combo Box.
  4. List Box.

Report related Controls:

  1. Text Box.
  2. Label.
  3. 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:

It is a simple Menu with only three group of options: Forms, Report Views and Macros. 

Under Forms Group two options are given, the first one displays the Tree View controls Menu table record.  The second Option displays the same records in continuous form mode.

The first option under Report View displays a Report on products Category records, from the Categories Table of NorthWind.accdb database.

The second option displays the Products List-Price Report.

The third option opens a Parameter Form so that the User can set the Minimum and Maximum List-Price values range, to Filter data for the Products List-Price Report.

Under the Macros Processes Group both option runs Macro1 and Macro2 respectively and displays different messages.

We need a Menu Table with the above option records with some additional fields, besides the usual TreeView’s Unique IDs, Description and ParentID data fields.  The Menu Table Image is given below:

Create a Table with the above structure and add the above records and save it with the name MenuID field is AutoNumber, PID and Type fields are Numeric fields, others are Text Fields.

We are familiar with the first three Data Fields: the Unique ID, Description and the ParentID Fields. Here, I have shortened the ParentID field name to PID.

We need four more fields in the Menu Table, one field Type for the object type Code and three fields Form, Report and Macro.

Type Field contains the Access Object Type Numeric Codes to identify the Option the User clicked on.

  • Form field is for Form Names, object Type code 1,
  • Report Field contains Report Names, object Type code 2,
  • Macro Field is for Macro Names, object type code 3.

Note: All the object names can be put in one Column. We have used separate fields for clarity only.  If you do that then make changes in the VBA Code, wherever it references different field names.

Based on the code numbers we can pickup the Object Names, from their respective fields and call the DoCmd.Openform or Docmd.OpenReport or Docmd.RunMacro to execute the action on the Child Node Clicks.

Now, the only question remains is how to link/store these two information (the Type Code and Object Name) on the Child Nodes?  We will take up that topic when we start Adding the Nodes to the Tree View control.

We need two more data Tables for sample Forms and Reports.  The Categories Table and Products Tables, from the NorthWind.accdb sample Database.  To save your time I have attached the Demo Database with all the Objects and Programs at the end of this Page to Download and try it out.

Create two Forms using the Menu Table with the names Data Entry and another Form Data View in continuous Form mode.

Create two Reports, one on the Categories Table with the report name: Categories,  another report on Products Table with the name Products Listing.  Add a long Label control below the main heading on the Products Listing Report and change the Name Property Value to Range.

Create a small form with two unboound Text Boxes and change their name Property Value to Min & Max, like the design given 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 sets a Value Range by entering the Minimum and Maximum List Price range in their respective Text Boxes the Report Filter criteria String is created.  The Report Filter String value is passed to the Product Listing Report as Open Report command Parameter.  The Filter String value is also passed as OpenArgs (Open Argument) Parameter. 

The Filter parameter filters the Report Data, based on the Criteria, specified in Min & Max fields, and the open argument value is copied to the Range Label Caption when the Report is open.

Copy and Paste the following Code into the Product Listing Report’s VBA Module:

Private Sub Report_Open(Cancel As Integer)
DoCmd.Close acForm, "Parameter"
Me.Range.Caption = Nz(Me.OpenArgs, "")
End Sub
  1. Create a new Form, with the name frmMenu and add the Microsoft TreeView Control from the Activex Control’s List.  Resize the Control as shown in the Design View below:
  2. Change the Tree View Control’s name to TreeView0 in the normal Property Sheet.
  3. Add a Command Button below the Tree View control.  Change it’s Name Property Value to cmdExit and Caption Property value to Exit.
  4. Right-Click on the Tree View Control and highlight the TreeCtrl_Object option and select Properties to display the Property Sheet.
  5. Change the following Property Values as given below:
  • Style = 7 (tvwTreeLinesPlusMinusPictureText)
  • Line Style = 1 (tvwRootLines)
  • LabelEdit = 1 (tvwManual)

Last Week we have changed the first two Property Values.  When LabelEdit Property’s default value is 0 –  tvwAutomatic, Clicking on the Node twice (not double-click) the Node-Text will go on Edit Mode and you can change the Text.  But it will not directly update on the data source field.  By changing it to 1 – tvwManual will prevent it from going into edit mode.

We can change this through Code by adding the following lines in the Form_Load() Event Procedure:

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

Last Week we have used the Form_Load() Event Procedure to read the Tree View Node values to create the Root-level and Child Nodes.  We need the same Procedure here also with few lines of  additional Code.

Besides that we need to trap the Node_Click() Event of Nodes to check which Option the User has selected.

Copy and Paste the following VBA Code into the Form Module and 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 lighlight to normal

For Each nodOn In tv.Nodes
    nodOn.BackColor = vbWhite
    nodOn.ForeColor = vbBlack
Next

‘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

On the Global Declaration Area of the Module the Tree View Object is declared.  A constant variable KeyPrfx is declared with the value “X”.

The Form_Load() Event Procedure of last week’s Article we have modified with additional Code.  I have commented the new Code segment to give an indication of what it does but will explain what it does.

The Procedure declares Database, Recordset and four String Variables.  Next two line declares a temporary Node Object: tmpNod and Typ Variant Variables are declared.

Next, the TreeView Object tv is assigned with the TreeView0 Object on the Form.  The TreeView0’s existing Nodes, if any, are cleared with the statement: tv.Nodes.Clear, in preparation for loading all the Nodes again.

We have implemented the following Code to modify the Tree View control’s Properties through Code, rather than through the Property Sheet.

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

The Tree View Font is changed to Verdana. Besides that we will bring in some more functions like expand or collapse all the Menu Groups with one click, rather than manually expanding or collapsing one group after the other.

The new SQL String is modified to add the new Fields Type, Form, Report and Macro Fields from Menu Table.

The Menu Table’s first record is checked for the presense of any value in PID field, if it is empty then it is a Root-level Node record. It is added to the Tree View Object as the Root level Node and it’s reference is saved in the tmpNod Object.

The Node have several properties like Forecolor, Bold and several others out of that we have taken the Bold Property and assigned True to make the Root level Node look different than it’s Child Nodes.

If it is not Root Node entry then it has the PID value, the program takes the Else clause and the record is added as a Child Node.  Here, we checks the Type field value.  If it contains one of the three values 1, 2 or 3 then we must take the value from Form, Report or Macro Name along with that the Type Code and join them together  (like ”1Data Entry”, “2Category Listing” etc.) and save it in the Tag Property of Child Nodes.  We are familiar with the Tag Property in Access controls, like Text Boxes, Labels, Command Buttons and others, but we rarely use it.

The cmdExit_Click() Procedure closes the Menu Form, if the response from the User is affirmative.

When the User clicks on a Child Node, the value we have saved in it’s Tag Property must be extracted and checked to determine what to do next.  For this we need a 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 the clicked Node’s Reference as Parameter in the object Node.  At the beginning of this procedure we have declared few Variables.

Next few lines checks whether the clicked Node is in expanded or collapsed state.

Normally, to expand a Node, to show it’s hidden child Nodes, either we click on the + (plus symbol) at the left side of a Node or double-click on the Node itself.  Double-Clicking on the Node again or clicking on the – (minus symbol) will hide the Child Nodes.

With the following Code segment we can expand or collapse Child-Nodes with a single Click:

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

The next six executable lines ensures that the Node 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 Tag Property value is read into the varTag Variable. If it is not empty then the value is split into two part. The Numeric value is extracted and saved in Typid variable and the Object Name part is saved in variable objName.

Depending on the value in Typid variable the Docmd is executed to open the Form, Report or Runs the Macro.

We will add two more Command Buttons on the Top of the Menu. One to expand all the Nodes with one Click and the second one to collapse all the Nodes.

  1. Add two more Command Buttons at the top area of the Tree View Control as shown on the design below.
  2. Change the Name Property Value of the left Command Button to cmdExpand and the Caption to Expand All.
  3. Similarly, change the right-side Command Button’s Name Property to cmdCollapse and the Caption to Collapse All.
  4. 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 we have declared a Tree View Node object NodExp.  The For . . . Next loop takes one Node at a time and checks whether it is in expanded form or not.  If not then it’s Expanded Property value is set to True.

Similarly the cmdCollapse_Click() Event makes a similar check and if it is in expanded state then the Expanded Property value is set to False.

The full Tree View Control’s all Nodes can be expanded and makes all their child Nodes visible at once or all Child Nodes kept hidden except the Root-level Nodes.

Hope you enjoyed creating the new Menu for your Project.  If you run along the design task step by step then your Menu should look like the finished Menu Image given at the top.

During the Year 2007 I have designed a Menu in one of my Projects, for Vehicles Service Contract System, using the Tab Control with several Pages. Each Page having 10 or more Options and to make each Page appear in turn at the same area, when the User Clicks on the Command Buttons lined up at either side of the Menu. Command Buttons at the right-side also changes, based on the selection of left side Button.

Click to Enlarge

You can find the Menu Design with Tab Control Article on this Link:https://www.msaccesstips.com/2007/06/control-screen-menu-design.html

CLASS MODULE

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object and Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA-Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality

Microsoft Acess Developer Portland OR