ACC: Sample Procedure to Fill a TreeView Control Recursively

• Microsoft Access versions 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article first explains recursive procedures, and how you can use them in Microsoft Access. Then, it gives an example that demonstrates a method for using a recursive procedure to fill branches of a TreeView control with data.

The TreeView control is available with the Microsoft Office 97 Developer Edition Tools and the Microsoft Access Developer's Toolkit version 7.0.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

MORE INFORMATION

The technique of recursion is defined as a procedure that calls itself in the middle of its routine. Following is a short example of a recursive function that returns the first file name that matches a user's input. The function prompts for a path and file name, and then uses the Dir() function to verify that the file exists. If the Dir() function returns an empty string (""), the file does not exist and the recursive procedure calls itself again. The second instance of the procedure prompts for a path and file name again, tests the input, and passes the results back to the first instance of the procedure. The following sample function continues to call itself recursively until a user types a valid path and file name:

   Function FirstFileMatch()
      Dim strFileName as String
      On Error Resume Next
      strFileName = Dir(InputBox("Enter a valid path and file name."))
      If strFileName = "" Then    ' Bad input. No Match.
         FirstFileMatch = FirstFileMatch()   '  Here is the recursive call.
      Else    ' This is the condition that ends this recursive loop.
         FirstFileMatch = strFileName   ' Return value to calling function.
      End If
   End Function

Recursion is an elegant way to handle data structures, such as linked lists and binary trees. It simplifies the logic and, in most cases, reduces the number of programming lines in your code. Recursion is also an ideal method for handling self-referencing tables. Self-referencing tables contain records that are linked to other records in the same table. The Employees table in the sample database Northwind.mdb is an excellent example of a self-referencing table. The ReportsTo field in the Employees table contains a number that corresponds to the EmployeeID field of the same table. To find the supervisor for any employee, check the number in the employee's ReportsTo field, and then find the employee with that same number in the EmployeeID field. That supervisor also has a ReportsTo field that may contain another employee's EmployeeID number. That employee, in turn, may report to someone else, and so on, until you reach an employee who does not report to anyone.

You can use a recursive procedure to display this chain of command in a TreeView control. As the procedure adds each node (employee) to the TreeView control, it calls another instance of itself to add child nodes for all employees who report to that employee. As the procedure adds each child node, it calls another instance of itself to add nodes for those employees who report to that employee, and so on, until it reaches the bottom of the chain. The example below is a recursive procedure that does just that.

The AddBranch procedure below accepts five parameters:

• The first parameter, rst as Recordset, is the set of records the procedure will use to get its data.
• The second parameter, strPointerField as String, is the name of the field that contains another record's PrimaryKey in the same table. In the Employees table, this parameter is the ReportsTo field.
• The third parameter, strIDField as String, is the name of the PrimaryKey field.
• The forth parameter, strTextField, is the name of the field to display in the TreeView control.
• The last parameter, varReportToID As Variant, is optional. The procedure uses this parameter to start adding related branches to the existing nodes. You do not supply anything for this parameter; when it is blank, the procedure begins adding all nodes that have a Null value in the strPointerField parameter. As it adds those nodes to the TreeView control, the procedure automatically calls itself again and passes the varReportToID parameter to add only the related child branches under those nodes.

Follow these steps to fill a TreeView control with a hierarchical list of employees using a recursive procedure. Employees are added to the tree according to the EmployeeID in the ReportsTo field of the Employees table.

CAUTION: Following the steps in this example will modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and perform these steps on a copy of the database.

1. Open the sample database Northwind.mdb, and create a new form not based on any table or query in Design view.

2. On the Insert menu, click ActiveX Control (or Custom Control in version 7.0).

3. In Microsoft Access 97, select Microsoft TreeView Control, version 5.0 in the Insert ActiveX Control dialog box, and then click OK.

   In Microsoft Access 7.0, select TreeView Control in the Insert OLE Custom Control dialog box, and then click OK.

4. Set the following properties for the TreeView control:

         TreeView control:
            Name: xTree
            Width: 4"
            Height: 3"
   
   

5. Double-click the TreeView control to invoke the TreeCtrl Properties dialog box. On the General tab, select 6 - tvwTreelinesPlusMinusText in the Style box, and then click OK.

6. On the View menu, click Code, and then type the following procedures:

         '=================Load Event for the Form=======================
         'Initiates the routine to fill the TreeView control
         '===============================================================
         Private Sub Form_Load()
         Const strTableQueryName = "Employees"
         Dim db As Database, rst As Recordset
         Set db = CurrentDb
         Set rst = db.OpenRecordset(strTableQueryName, dbOpenDynaset, _
         dbReadOnly)
         AddBranch _
            rst:=rst, _
            strPointerField:="ReportsTo", _
            strIDField:="EmployeeID", _
            strTextField:="LastName"
         End Sub
   
         '================= AddBranch Sub Procedure =========================
         '      Recursive Procedure to add branches to TreeView Control
         'Requires:
         '   ActiveX Control:  TreeView Control
         '              Name:  xTree
         'Parameters:
         '               rst:  Self-referencing Recordset containing the data
         '   strPointerField:  Name of field pointing to parent's primary key
         '        strIDField:  Name of parent's primary key field
         '      strTextField:  Name of field containing text to be displayed
         '===================================================================
         Sub AddBranch(rst As Recordset, strPointerField As String, _
                       strIDField As String, strTextField As String, _
                       Optional varReportToID As Variant)
         On Error GoTo errAddBranch
         Dim nodCurrent As Node, objTree As TreeView
         Dim strCriteria As String, strText As String, strKey As String
         Dim nodParent As Node, bk As String
         Set objTree = Me!xTree.Object
         If IsMissing(varReportToID) Then  ' Root Branch.
            strCriteria = strPointerField & " Is Null"
         Else  ' Search for records pointing to parent.
            strCriteria = BuildCriteria(strPointerField, _
            rst.Fields(strPointerField).Type, _
            "=" & varReportToID)
            Set nodParent = objTree.Nodes("a" & varReportToID)
         End If
         ' Find the first emp to report to the boss node.
         rst.FindFirst strCriteria
         Do Until rst.NoMatch
            ' Create a string with LastName.
            strText = rst(strTextField)
            strKey = "a" & rst(strIDField)
            If Not IsMissing(varReportToID) Then  'add new node to the parent
               Set nodCurrent = objTree.Nodes.Add(nodParent, _
               tvwChild, strKey, strText)
            Else    ' Add new node to the root.
               Set nodCurrent = objTree.Nodes.Add(, , strKey, _
               strText)
            End If
            ' Save your place in the recordset so we can pass by ref for
            ' speed.
            bk = rst.Bookmark
            ' Add employees who report to this node.
            AddBranch rst, strPointerField, strIDField, strTextField, _
            rst(strIDField)
            rst.Bookmark = bk     ' Return to last place and continue search.
            rst.FindNext strCriteria   ' Find next employee.
         Loop
         exitAddBranch:
         Exit Sub
         '--------------------------Error Trapping --------------------------
         errAddBranch:
         MsgBox "Can't add child:  " & Err.Description, vbCritical, _
         "AddBranch Error:"
         Resume exitAddBranch
         End Sub
   
   

7. Save the form as frmEmployeeTree.

8. In Microsoft Access 97, click "Compile and Save All Modules" on the Debug menu.

   In Microsoft Access 7.0, click "Compile All Modules" on the Run menu. Then on the File menu, click "Save All Modules."

9. Switch the form to Form view. Double-click one or more names in the TreeView control to expand and collapse the branches in the employee hierarchy.

Comments About the Code

The AddBranch procedure is a modular routine that you can use in your database without any modifications. However, you must modify the procedure in the OnLoad event of the form to customize it for your database:

• Change the constant strTableQueryName to the name of your own self-referencing table or query.
• Change the strPointerField, strIDField, and strTextField parameters you pass to the AddBranch procedure to the names of fields in your table or query according to the following summary:

        strPointerField:="<Field name that points to a parent record>"
        strIDField:="<Name of the PrimaryKey Field>"
        strTextField:="<Field name whose data you want to display>"
  

REFERENCES

For more information about using the TreeView control, search the Help Index for "TreeView control."

For more information about recursive procedures, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q132242
   TITLE     : ACC2: Sample Function Using Recursion to Display Data Tree

   ARTICLE-ID: Q165993
   TITLE     : ACC97: Example Using TreeView Control Drag-and-Drop
               Capabilities