A. TreeView Control Tutorial.
The Microsoft TreeView Control, part of the Microsoft Windows Common Controls library, is a powerful object for displaying related data in a hierarchical structure of nodes. It can represent information such as index entries and sub-entries, folder structures similar to Windows Explorer’s left pane, or any list of related items—complete with tree lines, checkboxes, and graphical icons.
Alongside the TreeView Control, we will also be using the ListView and ImageList controls, which are part of the Windows ActiveX Controls suite, to enhance functionality and presentation in Microsoft Access.
In the coming weeks, we’ll explore how to build and customize a TreeView Control from scratch. To give you a preview, I’ll share some sample TreeView demo images showcasing what we’ll be working toward.
B. Sample Demo Images.
Nodes in the Collapsed state.
- The Sample Demo TreeView Image with all Nodes in Collapsed form.
Nodes in the Expanded View.
- The above TreeView Control Nodes are in the expanded view.
With Arrowhead Image Icons.
- The TreeView Sample Display, with arrowhead Image Icons displayed to the left of each Node Text.
Folder Images for Root-Level Nodes, others with arrowheads.
Next, we’ll look at a TreeView display integrated with linked data in a subform. The root-level nodes are visually enhanced with two images: a closed folder icon in the default state, and an open folder icon when the node is clicked. Selecting a root-level node not only changes the icon but also expands it to reveal its child nodes.
The subform dynamically updates to display related information based on the selected root-level node. Additionally, selecting certain child nodes can trigger the display of another form—normally kept hidden—that provides further related details.
TreeView and ListView Controls.
In the next form layout, there are two panels. The left panel contains the TreeView control, which displays nodes representing product categories. When a category node is clicked, the right panel—comprising the ListView control—updates to display the related product items, along with their quantities and list prices in separate columns.
C. Creating Sample Data for Trial Run.
Let us try out the TreeView Control with some sample data shown below, based on the first two images displayed at the beginning of this Page.
The sample data table contains three fields:
-
ID – An AutoNumber field that generates unique ID numbers. AutoNumber is used here for convenience, but regardless of the type, each record in the table must have a unique ID value. If the ID is numeric, it should be converted to a string before adding it to the TreeView control.
-
Description (Desc) – Contains the node descriptions. The entries in this column are logically related to one another.
-
ParentID – A numeric field that identifies the parent node for each record. This value should also be converted to a string before using it in the TreeView control.
To build the hierarchy, we need to understand how the description values relate to one another. Based on these relationships, appropriate values are entered into the ParentID field.
For example, the logical hierarchy could represent:
-
Authors → Publishers → Bookstores where the books are sold.
-
Members of a family tree.
-
Product categories → Products → Stock → Price.
This related information may not always exist in a single column or table—it could be spread across multiple columns or even different tables.
The ParentID field plays a crucial role in defining the hierarchical arrangement of nodes. If the ParentID field is empty, the record is treated as a root-level node. A child node must always have its ParentID populated with the ID value of its parent record.
A root-level node can have one or more child nodes, and a child node can, in turn, have its own child nodes, creating multiple hierarchy levels.
We will first load the sample data into a TreeView control to view its initial arrangement. Then, by filling in the ParentID field with related IDs, we can reorganize the nodes into the desired logical order.
D. Windows Common Controls Library File
- First, open an existing database or create a new one.
- Press ALT + F11 to open the VBA editor, then choose References… from the Tools menu.
In the list of available references, look for Microsoft Windows Common Controls and check its box to select it.
If the file is not listed, click Browse… and locate MSCOMCTL.OCX in the Windows system directory.
In Windows 7 (64-bit), you will typically find it in the SysWOW64 folder.
Once selected, click OK to close the References dialog.
Create a Table with the following structure:
Save the Table with the name Sample.
Fill the Table with the sample data of 12 records as shown in the data view Image above.
E. Creating TreeView Control on Form
Create a New blank Form.
Click on the ActiveX Controls button from the Controls Group, find the Microsoft TreeView Control, then select it.
Click OK to insert a TreeView control on the Form.
Drag the TreeView control slightly down and to the right to leave some space along the top and left edges.
Next, use the bottom-right sizing handle to stretch the control toward the right and downward, enlarging it to match the proportions shown in the sample image below.
Display the control's Property Sheet, and change its Name Property Value to TreeView0, if it is different.
Display the VBA Editing Window of the Form.
- Copy and paste the following VBA Code into the Module, overwriting the existing lines of code there:
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 strSQL As String Dim nodKey As String Dim ParentKey As String Dim strText As String Set tv = Me.TreeView0.Object strSQL = "SELECT ID, Desc, ParentID FROM Sample;" Set db = CurrentDb Set rst = db.OpenRecordset(strSQL, dbOpenDynaset) Do While Not rst.EOF And Not rst.BOF If Nz(rst!ParentID, "") = "" Then nodKey = KeyPrfx & CStr(rst!ID) strText = rst!Desc ‘Add the TreeView Root Level Nodes tv.Nodes.Add , , nodKey, strText Else ParentKey = KeyPrfx & CStr(rst!ParentID) nodKey = KeyPrfx & CStr(rst!ID) strText = rst!Desc ‘Add the Record as Child Node tv.Nodes.Add ParentKey, tvwChild, nodKey, strText End If rst.MoveNext Loop rst.Close Set rst = Nothing Set db = Nothing End Sub
Save the Form with the name frmSample, but don’t close the VBA Window.
G. VBA Code Review – Line by Line
Let’s take a quick look at the VBA code and understand what it does.
In the Global Declaration area of the Form module:
-
tv
is declared as a TreeView object. -
KeyPrfx
is declared as a String constant with the value"X"
.
The TreeView Node’s Key must always be a string and must contain at least one non-numeric character. Since our sample table’s key values are purely numeric, we convert them to strings and prefix them with the letter
"X"
.-
Example:
1
→"X1"
-
Simply converting a numeric value to a string (without an alphabet) will not be accepted as a valid Node key.
Note: If the Node Key and Parent Key are already in alphabetic or alphanumeric form, conversion is unnecessary. All Node keys must still be unique.
In the
Form_Load()
event procedure:-
Database and Recordset objects are declared, along with four string variables.
-
The statement:
assigns the TreeView0 control on the form to the object variable
tv
. -
OpenRecordset()
Opens the sample table records using the SQL stringstrSQL
. -
The
Do While...Loop
ensures the recordset is processed only if it is not empty; otherwise, the loop exits and the procedure ends.
Determining Node Type
-
If the ParentID field is empty, the record becomes a Root-level Node.
-
A Root Node needs only a unique Key and Text (from the
ID
andDesc
fields).
-
-
If the ParentID has a value, the record becomes a Child Node of either a Root Node or an upper-level Child Node.
Building the Node Key & Text
-
nodKey
is created by converting theID
field to a string and prefixing it with"X"
. Example:ID = 1
→nodKey = "X1"
. -
strText
stores theDesc
field value. This keeps theNodes.Add()
parameters are short and readable, especially when field references are long.
Adding the Node
The statement:
calls the
Add()
method of the TreeView’sNodes
collection to insert the node into TreeView0 onfrmSample
.
Syntax of
Add()
method:-
All six parameters are optional.
-
Calling it without parameters adds an empty Root Node (a blank tree line).
Requirements:
-
Root Node: Needs only
Key
andText
. -
Child Node: Requires both
[Relative]
and[Relationship]
.-
Omitting either adds it as a Root Node (no error occurs).
-
[Relative]
is the Node Key of the existing parent (fromParentID
). -
[Relationship]
is the constanttvwChild
(value4
), identifying it as a child of the parent node.
-
-
F. Access VBA Code.
The other Constant values for the Relationship Argument are used to position the Child Nodes in a specific location. Constant Values are as follows:
tvwFirst = 0, places it as the first Node, at the level of the relative Node.
tvwLast = 1, places as the last Node, at the level of the relative Node.
tvwNext = 2, places the Node after a specified Node.
tvwPrevious = 3, places the Node immediately preceding the specified Node.
Note: You can experiment with different values in the Relationship argument and run the code in Debug Mode, keeping the VBA editor and the form in Normal View side by side. Observe how the nodes are arranged during each iteration of the code. This will help you understand the effect of each relationship type.
This knowledge becomes useful when editing the TreeView—such as deleting an item, inserting another item in its place, or adding a new node at a specific location.
A node referenced in the [Relative] argument must already exist in the Nodes collection before you can add a child node to it. Otherwise, it will generate an error.
The process continues until all records in the recordset have been processed.
Note: You may need to review the VBA code again after running the demo to reinforce your understanding.
H. The First Trial Run.
Open the Form in Normal View. The Trial Run result looks like the Image given below.
It doesn’t look much different from a regular list box at the moment. This is because we have not yet assigned any values in the ParentID field in our sample table. To arrange items hierarchically in the TreeView control, we must define relationships between the rows in the table.
I. Understanding the Relationship Between Records
Open the sample table and examine how the records relate to each other.
We will keep the Database item as a root node. The database object also contains several top-level objects—Application, DBEngine, Workspaces collection, and Databases collection—which we have not included here.
Next, we have the Tables group item with an ID value of 2.
In the following records, we see the Table, Fields, and Field items, which are all related to the Tables group. Our goal is to place the Table, Fields, and Field records under the parent node Tables (ID value 2).
In this hierarchy, the Tables record becomes the parent node, and the Table, Fields, and Field records become its child nodes.
J. Updating the ParentID Field
To achieve this structure, we need to update the ParentID field for the Table, Fields, and Field records with the value 2 (the node key of Tables).
Update only these records, then close the table. Once completed, your records should look like the example shown in the image below.
Now, open frmSample in Form View and check the TreeView control.
At first glance, the display will look exactly the same as before—no visible changes.
However, the updates you made are already in effect; they’re just not reflected visually yet.
K. The Property Sheet of the TreeView Control
The TreeView control has its own Property Sheet, and these settings influence how the control appears. We’ll adjust one of these properties before returning to the TreeView to see the change.
-
Switch frmSample to Design View.
-
Right-click the TreeView control.
-
From the shortcut menu, highlight TreeCtrl_Object and select Properties.
The Property Sheet will now appear, as shown below.
The Settings on this Property Sheet change the appearance of the TreeView Display.
The left-side top Property Style is already set with the maximum features available, Option-7 (tvwTreeLinesPlusMinusPictureText).
Change the LineStyle Property Value = 1 (tvwRootLines) and click the Apply button, then click OK to close the Property Sheet.
L. Running After the LineStyle Property Change
Save the form and open it in Normal View.
You’ll now see the tree lines displayed correctly.The Tables node now shows a plus (+) sign to its left, indicating that it has one or more child nodes at the next level, which are currently collapsed.
-
Click the plus (+) sign to expand the node and reveal all child nodes sharing the same ParentID.
-
Click the minus (–) sign to collapse them again, hiding the child nodes and changing the symbol back to a plus (+).
When expanded, the display will resemble the image shown below:
Updating the ParentID for Other Records.
Next, we will establish the parent–child relationships for the remaining records.
-
Forms and Controls
-
Update the ParentID field of the
Form
,Controls
, andControl
records with the Forms record’s ID value (Node-Key). -
This ensures that these records will appear under the Forms node as its child nodes in the TreeView.
-
-
Reports and Controls
-
Similarly, update the ParentID field of the
Report
andControl
records with the Reports record’s ID value (Node-Key). -
This positions them under the Reports node as its child nodes.
-
-
Once the updates are complete, your Sample Table should match the ParentID values shown in the illustration below:
After applying the above changes, open the form and expand all the nodes in the TreeView. The display should now resemble the image shown below, with every parent node and its corresponding child nodes fully visible.
All child nodes related to the root-level nodes—Tables, Forms, and Reports—are grouped under their respective parent nodes. However, a child node may also have a parent node, a grandparent node, or even a great-grandparent node, depending on the hierarchy.
N. Arranging All Objects in a Logical Hierarchical Order
Let’s take the first root-level node, Tables, as an example. Logically:-
Field (record ID 5) is directly related to the Fields collection (record ID 4).
-
The Fields collection is related to the Table.
-
The table is part of the Tables collection.
This means that each item in the group (record numbers 5 through 2) is connected one step upward to the next level in the hierarchy.
Next, we will position these child nodes under their respective parent nodes in the correct order and see how the arrangement appears in the TreeView control.
-
Open your Sample Table and change the ParentID values of the Tables related to Child Records as shown below:
The record Field with ID 5 has Fields (record ID 4) as its parent. Therefore, we update the ParentID field of record 5 with the value 4.
Similarly:
-
Record 4 (Field) has record 3 (Table) as its parent, so we set its ParentID to 3.
-
Record 3 (Table) has record 2 (Tables) as its parent, so we set its ParentID to 2.
Note: The records do not have to appear next to each other in the table to establish this hierarchy.
After making these changes, save the table and open frmSample to view the results. Your TreeView display should now resemble the image shown below, with all nodes expanded.
-
The Child-Node of a Root-level Node can be a Parent-Node to its own Child or Children. This way, it can go several steps down the tree.
Change the other two groups of Child Node's ParentID field values to look like the Image given above.
Download TreeView Demo Database.
DICTIONARY OBJECT
-
This is a terrific tutorial. Thanks much. The level of detail, the pictures, this helped me a lot.
ReplyDeleteThank you.
DeleteI tied to apply treeview exercise your posted here. Sir I am currently using Windows 10 and Microsoft access 10,... I tried to search the reference library - Microsoft windows common controls OR tried to browse MSCOMLIB or SysWOW64 …. I didn't get any of the above reference … is there equivalent reference library for windows 10 or is there any options
ReplyDeletethanks
I'm using a treeview in MS Access 365, and everything (so far) is great EXCEPT that Access isn't recognizing the SelectedNode property, so a user can't use the treeview to select a node and manipulate the interface based on that selection. Any idea what's wrong? Is there a setting I haven't switched on or off? Has the name of the collection since 2021?
ReplyDeleteYou can safely skip that part. The code still runs fine.
ReplyDeleteI have followed this word for word and get an error in the compile.
ReplyDeleteThe line in the Else section......
tv.Nodes.Add ParentKey, tvwChild, nodKey, strText
Yields a "Run-time Error '35601' Element not found.
Each attribute does have a value.
What am I missing.
MS Access 365, (version 2302), Windows 11
A Demo Database is attached now with two Demo Forms. Download and compare the Code with your VBA Code to track the Errors.
ReplyDelete