This article applies only to a Microsoft Access database (.mdb).
The TreeView control is available with Microsoft Office 2000 Premium Edition; the ActiveX controls that are available to you depend on the various programs installed on your computer.
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:
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.
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.
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.
- Start Microsoft Access and open the sample database Northwind.mdb.
- Create a new form in Design view, not based on any table or query.
- On the Insert menu, click ActiveX Control.
- Select Microsoft TreeView Control, version 6.0 in the Insert ActiveX Control dialog box, and then click OK.
- Set the following properties for the TreeView control:TreeView control
Width: 4" or 10 cm
Height: 3" or 7 cm
- 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.
- On the View menu, click Code, and in the Visual Basic Editor, type the following line if it is not already present:Option Explicit
- 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"
'================= AddBranch Sub Procedure ======================
' Recursive Procedure to add branches to TreeView Control
' ActiveX Control: TreeView Control
' Name: xTree
' 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)
' Find the first emp to report to the boss node.
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)
' 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.
'--------------------------Error Trapping --------------------------
MsgBox "Can't add child: " & Err.Description, vbCritical, "AddBranch Error:"
- Save the form as frmEmployeeTree.
- In the Visual Basic Editor, on the Debug menu, click Compile Northwind.
- 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 CodeThe 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