Introduction
Microsoft Access does not have a native tree or list view control – we have to rely on a Windows control called MSCOMCTL.OCX to provide these services. From time to time, the control is updated which sometimes leads to applications breaking. This article addresses the need for a ‘native’ tree-view control constructed with Access tables, forms, and code to get around the problem.
Background
The last instance of the control breaking led me to accept that the best way to avoid the problem from now on would be to write my own. As an aside, a really good solution to fixing the problem can be found here.
What Does the Control Look Like?
This control is constructed from an Access form and an underlying local table...
The control is designed to allow the user to select an individual node by clicking the check-box or using the arrow keys to open, close, or to move the focus up and down the tree.
The numbers on the right hand side indicate the children belonging to each node – these are optional.
How Does it Work?
After creating an Access database, I incorporated some tables from the Northwind Traders database. The data model looks like this…
Conventions
Tables prefixed with z mean that they are local and, usually, their content is temporary.
Queries prefixed with hqry mean that this would normally be a hidden query. The use of query names with postfix numbers is my way of grouping associated functionality together in the order in which it is used – on large systems you get lost trying to dream up meaningful names!
The level of a node means its logical position in the X-Axis. In the screenshot of the tree-view (above) zero level nodes are the names of the customers, level one nodes are the order-dates, and level two nodes are the products ordered on the level one date.
The Underlying Z Table
When the form loads, the OnOpen event fires and the underlying table is loaded; after a selection, it looks like this…
This is a self referencing table. I always shy away from any data model that is not in normal form but in this instance, having everything in one table is easier to display, manage, and debug.
The fields hold the following information…
NodeKey
The field has two functions, first is the primary key and second, because it is a text field, it also acts as the sort key. In most instances, you will use the primary keys from the associated tables to create a compound node-key.
You can see that where a second level node has been added, I have used a pipe (|) symbol to separate the keys. Some forethought is needed at the design stage for this key so that it is always unique.
Where child nodes have a numeric key, you may find that you can’t rely on this concatenation being unique so you may have to use a postfix letter such as <space> letter O for Orders or <space> OI for Order Items.
Using a pipe symbol allows the key to be disassembled like this…
Dim strKeys() As String...
strKeys = Split(strKey, "|")
If you have used a postfix character to make a numeric key unique, then remember that the Val function is really useful to extract the number because it stops reading once it encounters a non-numeric character. So, if your partial key is: strKeys(N) with a value of 1234 OL then Val(strKeys(N)) will return 1234. See here for details.
NodeSelected
This is the tick box to the left of each item in the tree-view. It allows the user to select more than one node and then take some collective action.
NodeCaption
This is the main field visible in the tree-view. Note that it contains the Unicode character indicating whether the node is closed or expanded (4,6). Also note the indentation used to mark off each tree-level.
IsExpanded
Indicates if the node is closed or expanded.
IsGroup
Indicates that the node has children.
ChildrenCount
Shows the number of children for that parent.
The Form
The tree-view form is a simple Access form set in continuous-forms mode...
You can just see the node-key text box field behind the label that gives the user some instructions. To the right, you can see the IsGroup check-box. The IsGroup and node-key controls are hidden when the form is displayed. The node caption and child-count text boxes have their conditional formatting set for highlighting. When the form loads, we populate the zero level nodes using a suite of queries and a Unicode character to display the closed symbol (4).
Unicode symbols are declared as strings and instantiated as follows…
Private mstrRightArrow As String
Private mstrDownArrow As String…
mstrRightArrow = ChrW(&H25BA)
mstrDownArrow = ChrW(&H25BC)
Because the characters are Unicode, we have to use the ChrW function; this prevents us from declaring them as constants.
A third Unicode character called the black circle character (=) is used to indicate a node without children. You will find an interesting list of these symbols here.
Expanding and Collapsing Nodes - Signaling Context
As the user clicks in the text area of each row, a mouse-down event is raised. The code for this event is the main switchboard for changing the tree’s appearance.
The challenge is to determine if the user wants to expand or collapse the node or just wants to select it.
The mouse-down event identifies where the click occurred in the X-axis. This tells us which level of sub tree is signaling the event. Once we know the click occurred over a Unicode symbol, the IsGroup and IsExpanded values provide the clues to what happens next…
Private Sub NodeCaption_MouseDown(Button As Integer, _
Shift As Integer, X As Single, Y As Single)
Const cstrTitle As String = "NodeCaption_MouseDown"
Dim strKeys() As String
On Error GoTo ErrHandler
If Me.ChildrenCount = 0 Then GoTo NormalExit
If InStr(Me.NodeKey, "|") Then
strKeys = Split(Me.NodeKey, "|")
Else
ReDim strKeys(0 To 0) As String
End If
Select Case UBound(strKeys)
Case 0
If (X >= 30 And X <= 180) Then
Else
GoTo NormalExit
End If
Case 1
If (X >= 600 And X <= 750) Then
Else
GoTo NormalExit
End If
Case 2
If (X >= 1125 And X <= 1275) Then
Else
GoTo NormalExit
End If
Case Else
GoTo NormalExit
End Select
If Me.IsGroup Then
If Me.IsExpanded Then
CollapseNode Me.NodeKey
Else
ExpandNode Me.NodeKey, Me.NodeSelected
End If
End If
NormalExit:
Exit Sub
ErrHandler:
MsgBox Err.Description & vbCrLf & "Error number: " & _
Err.Number, vbExclamation, cstrTitle
Resume NormalExit
End Sub
Expanding a Node
The Expand Node subroutine uses a suite of queries to replace the Unicode symbol and then adds records according to the level of the node.
First, we determine how many levels there are and then set the appropriate query…
strKeys = Split(strKey, "|")
Select Case UBound(strKeys)
Case 0
Set qd = db.QueryDefs("hqryTreeView02")
qd.Parameters!prmSpacer = Space$(6)…
Case N
Set qd = db.QueryDefs("hqryTreeViewNN")
qd.Parameters!prmSpacer = Space$(N)
The Space$ command is used to signal group structure for each sub-tree.
Collapsing the Node
Where a user clicks on an expanded node symbol, the program recognizes this, removes the child nodes, and changes the symbol. This is where the use of a good primary key comes into play so that we can delete child nodes but leave the parent node in the table.
The delete query uses the current record’s NodeKey like this…
PARAMETERS prmNodeKey Text ( 255 );
DELETE ztblTreeView01.*
FROM ztblTreeView01
WHERE (((ztblTreeView01.NodeKey) Like [prmNodeKey] & "|*"));
By adding the pipe symbol to the WHERE clause we are telling the query to delete only child nodes, thus, the parent will not be removed.
Finally, after the records are added, the form is re-queried and the current record is set to the parent node.
Selecting a Node
By clicking on a node, you will see, in the lower left-hand side of the main Access screen, the caption from the selected row. This is driven by the click event for the node-caption and it is also the place in the VBA code where you would take some other appropriate action.
Drawbacks
There are two drawbacks with this design, first, you can’t change the cursor from an I-Beam to an arrow as it passes over Unicode characters and second, there is no drag and drop.
Points of Interest
Where you use a set of tables with natural primary keys, you may run into sort-order problems by leaving spaces in the node-key. I found that creating the nodes in this situation required the use of a Replace function to eliminate spaces, then everything appears in the correct sort-order.
It will be possible to develop this design into a tree-view table which will come in useful in some applications.
Previous Attempts at this Control
I was not able to find many attempts at creating a native Access tree-view control but in 2005, the following were published…
- SmartTree:
- This was blogged by Lauren Quantrell and converted into an Access 97 database by Gord Thompson and ‘
Bri’. This solution uses a command button as the target area for the expand and collapse events – very neat. - Listview Tree: David Fenton shows a list view control adapted to look like a tree view – nice. See picture only here.
History
- 9th July, 2013, replaced download zip file - CRC problems reported
