Click here to Skip to main content
15,867,453 members
Articles / Productivity Apps and Services / Microsoft Office

A Tree-View Control Using an MS Access Table and Form

Rate me:
Please Sign up or sign in to vote.
5.00/5 (8 votes)
16 Jul 2013CPOL7 min read 121.6K   4.7K   23   15
How to create a tree-view control using an Access table and form.

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

Image 1

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…

Tree-View data model

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…

Z Table showing specimen data

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…

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

Image 4

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…

VB
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…

VB
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 there are no child nodes then just exit
    If Me.ChildrenCount = 0 Then GoTo NormalExit
    
    'Determine the node level and make sure that the click is in
    'the target position on the X axis (over the symbol)
    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
                'Carry on
            Else
                GoTo NormalExit
            End If
        Case 1
            If (X >= 600 And X <= 750) Then
                'Carry on
            Else
                GoTo NormalExit
            End If
        Case 2
            If (X >= 1125 And X <= 1275) Then
                'Carry on
            Else
                GoTo NormalExit
            End If
        Case Else
            GoTo NormalExit
        End Select
        
    If Me.IsGroup Then
        If Me.IsExpanded Then
            CollapseNode Me.NodeKey
        Else
            'Need to signal the state of the checkbox
            'via NodeSelected so child nodes are ticked or not
            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…

VB
strKeys = Split(strKey, "|")
Select Case UBound(strKeys)
    
    Case 0 'Add level 1 subtree
        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…

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer ACCESSible IT Limited
United Kingdom United Kingdom
I hold a BSc(Hons) degree in Computer Science and Business Administration, an MSc in database design and an MPhil in neural networks.

Since 1994 I have been running my own Access and SQL Server development company. Four years ago I re-skilled into Silverlight but now, I am learning MVC .Net 2, HTML 5, JavaScript and TypeScript.

Comments and Discussions

 
Questiontree view Pin
Adrian Connolly17-Aug-21 19:45
Adrian Connolly17-Aug-21 19:45 
AnswerRe: tree view Pin
OriginalGriff17-Aug-21 19:48
mveOriginalGriff17-Aug-21 19:48 
GeneralRe: tree view Pin
Adrian Connolly17-Aug-21 20:20
Adrian Connolly17-Aug-21 20:20 
GeneralRe: tree view Pin
OriginalGriff17-Aug-21 21:32
mveOriginalGriff17-Aug-21 21:32 
QuestionHow to construct tables. Pin
Member 128332777-Nov-16 10:54
Member 128332777-Nov-16 10:54 
AnswerRe: How to construct tables. Pin
RB Starkey7-Nov-16 20:07
professionalRB Starkey7-Nov-16 20:07 
QuestionMouseover I-beam Pin
Member 103186246-Sep-15 1:19
Member 103186246-Sep-15 1:19 
AnswerRe: Mouseover I-beam Pin
RB Starkey6-Sep-15 23:47
professionalRB Starkey6-Sep-15 23:47 
QuestionMouse Cursor Pin
Member 87580185-May-15 13:13
Member 87580185-May-15 13:13 
AnswerRe: Mouse Cursor Pin
RB Starkey5-May-15 19:34
professionalRB Starkey5-May-15 19:34 
QuestionTreeview Pin
Member 116571841-May-15 4:14
Member 116571841-May-15 4:14 
AnswerRe: Treeview Pin
RB Starkey1-May-15 5:23
professionalRB Starkey1-May-15 5:23 
GeneralRe: Treeview Pin
Member 116571841-May-15 6:24
Member 116571841-May-15 6:24 
GeneralDownload won't unzip Pin
G.Reimers8-Jul-13 6:15
G.Reimers8-Jul-13 6:15 
GeneralDownload won't unzip - Fixed Pin
RB Starkey19-Jul-13 5:54
professionalRB Starkey19-Jul-13 5:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.