How to fill a Treeview control recursively in Access 2000 (209891)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

The technique of recursion is defined as a procedure that calls itself in the middle of its routine. The following is a short sample recursive function called FirstFileMatch(), which 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 then passes the results back to the first instance of the procedure. The 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                                   ' Condition that ends the recursive loop.
      FirstFileMatch = strFileName        ' Return value to calling function.
   End If

End Function
				

The recursive procedure continues to call itself until some condition is satisfied, in this case, until the user's input matches a file name on the computer's hard disk. After there is a match, the results are passed back to the instance of the procedure that called it. Then, that instance of the procedure passes results back to the previous instance, and so on, until focus returns to the top-level instance of the procedure.

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 following example is a recursive procedure that does just that.

The following AddBranch procedure accepts five parameters:

• The first parameter, rst as Recordset, is the set of records the procedure uses 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. In the Employees table, this is the EmployeeID 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.

The AddBranch procedure is modular enough to use with any self-referencing table. The procedure's performance is optimized by passing the Recordset object by reference (ByRef) to each recursive instance, which reduces the amount of memory the procedure needs to use and eliminates the need to open a new recordset with each call to a new instance of the procedure.

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: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a new form in Design view, not based on any table or query.
3. On the Insert menu, click ActiveX Control.
4. Select Microsoft TreeView Control, version 6.0 in the Insert ActiveX Control dialog box, and then click OK.
5. Set the following properties for the TreeView control:

   TreeView control
   Name: xTree
   Width: 4" or 10 cm
   Height: 3" or 7 cm

6. Double-click the TreeView control to open the TreeCtrl Properties dialog box. On the General tab, select 6 - tvwTreelinesPlusMinusText in the Style box, and then click OK.
7. On the View menu, click Code, and in the Visual Basic Editor, type the following line if it is not already present:

   Option Explicit

8. Type or paste 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 DAO.Database, rst As DAO.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
   					

9. Save the form as frmEmployeeTree.
10. In the Visual Basic Editor, on the Debug menu, click Compile Northwind.
11. Open frmEmploeeTree in Form view, and then 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 do both of the following to 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 that you pass to the AddBranch procedure to the names of fields in your table or query according to the following summary:

  strPointerField:= ParentFieldName, where ParentFieldName is the field that points to a parent record
  
  strIDField:=PrimaryKey, where PrimaryKey is the name of the PrimaryKey field
  
  strIDField:=ListFieldName, where ListFieldName is the name of the field whose data you want to display