Thanks to the way that Windows Explorer handles folders and subfolders, your users are used to being able to work with hierarchical data using a TreeView control.  Glenn Lloyd shows how to incorporate a TreeView control into your application.

Relational databases typically use a hierarchical structure for data organization that reflects the real-world entities the database is modeling. Each database has a few top-level or parent tables. Most of the database tables, however, depend either directly or indirectly on these top-level tables. This tree structure provides an easy means to visualize both real-world entity relationships and relational database structure by using a TreeView control. Using a real-world example, this article shows how you can use the TreeView approach to displaying and working with hierarchical data.

Most Windows users have seen the TreeView control in action many times, although they may not know or care what it’s called. For example, the folder list in a Windows Explorer window is a form of the TreeView control (see Figure 1). Other examples include the online Help file tables of contents of many Windows applications, and the Outlook Folder view.

Figure 1

If you intend to use the TreeView control, you’ll need to do some initial planning before you begin. This planning will include determining the overall purpose of the display you’ll be developing, analyzing the data that will be used to populate the tree, deciding whether the tree data needs to be sorted, deciding how to handle events triggered when the user clicks a node, and planning for the maintenance of the control’s underlying data. To help you work through this, I’ll use an example based around the country/province structure of Canada (feel free to substitute the country/state, country/county, or country/region organization structure of your native land).

For this case, the TreeView will display in hierarchical order the political jurisdictions within the country, visually depicting how the various political levels relate to each other, along with the overall structure, and how informal low-level community components fit into the picture.

Constitutionally, Canada has three levels of political responsibility: Federal, Provincial, and Municipal (see Figure 2). Larger municipalities typically have wards or districts that may be further subdivided into neighborhoods or communities.

Figure 2

Using the TreeView to display this structure requires placing each organizational component of the political structure at an appropriate position in the tree. The TreeView itself is set up as a group of nodes. Every node, except for the top-level node, has a parent node to which it belongs as a child, thus forming the levels that my data needs. Every node can have subordinate or child nodes.

In my example, the top-level node is the country: Canada. All other nodes in the tree belong either directly or indirectly to the top-level node. Provinces belong directly to Canada, while municipalities belong directly to provinces and indirectly to Canada.

Once you’ve analyzed the underlying data and have determined the appropriate parent and child nodes, you can create a form and add a TreeView control to it, then populate the control with its initial data set. Additional steps will be required if the user is to be allowed to add new nodes or remove unneeded nodes.

Unlike the simpler controls used on everyday forms, TreeView controls can’t be bound to a data source. Instead, you must maintain the data using VBA code. In my example, the data to populate the tree will be hard-coded into a VBA procedure to simplify the code. However, the data could just as easily be drawn from one or more tables.

Creating a TreeView control

To add a TreeView to a form in Design view, begin by clicking on the More Controls button in the lower right-hand corner of the Toolbox to display a scrollable list of the various ActiveX controls installed on your computer. Scroll down through the More Controls list to find Microsoft TreeView Control (version 5 or 6). If you don’t find the TreeView control on the list, it may not be installed on your computer or it may not be properly registered in Windows (see the sidebar on installing the TreeView for how you can get a copy of the control). Once I’d added the control to my form, I sized it to be about 2.5″ wide by 3″ high and named the control TViewCanada. The result can be seen in Figure 3. So far the control doesn’t do anything spectacular; it merely occupies space on the form. Switching to Form view will show the new control as a blank rectangle.

Figure 3

The TreeView control has a set of custom properties that determine the control’s general appearance and behavior. For example, setting the Line style property to 1- tvwRootLines will display the expansion/collapse icon to the left of top-level nodes. Selecting the Sorted checkbox will cause top-level nodes to appear in alphabetical order. You can access the custom properties by selecting the control and clicking the Build button by the custom properties line on the Other tab on the control’s property sheet.

Now that the TreeView control exists and can be displayed, a little code is needed to manage the data it will display. The TreeView’s list is stored as a collection of items that belong to the object’s Nodes collection. Four methods and one property are available for managing and retrieving TreeView data:

• Add–Add nodes to the collection.
• Count–Determine the number of nodes in the collection.
• Clear–Delete all nodes from the collection.
• Item–Retrieve a specific item by its index value.
• Remove–Remove a specific node from the collection.

In most applications, the Add and Clear methods are used most frequently. The other methods are used as needed. The Add method has six possible parameters. At a minimum, the index and text values must be specified. Except for top-level nodes (which have no parent by definition), you must specify the Relative and Relationship values. You may also specify two images that will be displayed in front of the node text, Image and Selected Image.

In the order they appear in the Add method’s argument list, here’s a brief description of each of the parameters to be passed to the method:

• Relative–The node to which the new node will be related.
• Relationship–Whether the new node is the child or sibling of the relative node. Not required for the first node (the root node) in the tree. Table 1 lists all the values.
• Key–The value by which the node may be looked up and referred to.
• Text–The text the control will display for this node.
• Image–The image that will appear in front of the node.
• Selected Image–The image that will appear in front of the node if it’s selected.

Table 1. Constants used with the Add method.

The code to add the top node (“Canada”) and the remaining provinces would look like this:

Private Sub Form_Open(Cancel As Integer)

    With Me.TViewCanada

        .Nodes.clear

        .Nodes.Add , , "Top", "Canada"

        .Nodes.Add "Top", tvwChild, "BC", _

             "British Columbia"

        .Nodes.Add "Top", tvwChild, "Alta", _

             "Alberta"

        …remaining provinces

        'display all nodes within the top node

        .Nodes.Item("Top").Expanded = True

    End With

End Sub

With the first two levels populated, the form with its TreeView control will look as it appears in Figure 4. As you can see, loading the control from a recordset wouldn’t be hard to do (in fact, looping through a recordset of all of the provinces would use less code than my example, where I needed 12 lines of code to add all the provinces and territories).

Figure 4

Part of TreeView’s functionality comes from its ability to display multi-level trees. All that’s needed is some additional code to add child nodes to the second level nodes that the control now has. If you pass the constant twChild as the second parameter to the Add method along with the Key value of an existing node, the new node will be the child of an existing node. Here’s the code to add some cities to the province of British Columbia:

 'add some cities to the second level nodes

  .Nodes.Add "BC", tvwChild, "Van", "Vancouver"

  .Nodes.Add "BC", tvwChild, "Vic", "Victoria"

  .Nodes.Add "BC", tvwChild, "Kam", "Kamloops"

  .Nodes.Add "BC", tvwChild, "Ver", "Vernon"

You can now open the form in Form view to see the changes. The third level nodes will be represented by small plus signs inside a square and clicking one of the squares will expand the node. Each click on a second level node toggles any node with child nodes between expanded and collapsed.

Of course, populating the tree with anything more than a trivial amount of data using hard-coded values would be an exercise in frustration. The sample database includes a table of tree node information and a form, frmTview2, that populates the tree using an ADO recordset.

User interaction with TreeView

Selecting a node by clicking its text will trigger the Tree_NodeClick event. Unlike standard form controls, the property sheet for a TreeView object doesn’t list events specific to the control. Nevertheless, the form’s code can respond to the Tree_NodeClick event.

The skeleton for an event handler for the NodeClick event, complete with error handling, looks like this:

Private Sub TViewCanada_NodeClick(ByVal Node _

            As Object)

On Error GoTo Tree_NodeClick_Error

  'application code

Tree_NodeClick_Exit:

    'cleanup objects here

    On Error GoTo 0

    Exit Sub

Tree_NodeClick_Error:

    Select Case Err.Number

    Case 91 'no object - top level nodes will

                'not have parents for example

        Resume Next

    Case Else

    MsgBox "Error " & Err.Number & " (" & _

                Err.Description & ") in _

                procedure Tree_NodeClick _

                of VBA Document Form_Form2"

    End Select

Resume Tree_NodeClick_Exit

End Sub

The form’s code can access six properties for the node: Text, Parent, Children, Index, Key, and Tag. My skeleton procedure includes an error handler because attempting to access a top-level node’s parent property will cause a runtime error.

In addition to accessing node properties, you may need to “walk” through a tree’s nodes. This can be done using a simple recursive procedure. This example reads all of the nodes in a tree so that you can retrieve all the data in the tree:

Public Sub TraverseTree(objNode As Node)

    Dim objSiblingNode As Node

    Set objSiblingNode = objNode

   Do

       txtResults = txtResults & _

         objSiblingNode.Text & Chr(13) & Chr(10)

       '…process node data

       If Not objSiblingNode.Child Is Nothing Then

           Call TraverseTree(objSiblingNode.Child)

       End If

       Set objSiblingNode = objSiblingNode.Next

   Loop While Not objSiblingNode Is Nothing

   Set objsiblingNode = Nothing

End Sub

To use the routine, just call it and pass the node that you want processed (the routine will process the node and all of its children before it stops):

TraverseTree Node

With this information in hand, you can add a TreeView to your forms and let your users start processing their data in a way that they’re already familiar with.

Other Pages You Might Like

Treeview: Building Explorer-style Forms

Taming the Treeview Control

Making Your Applications Talk