This article first explains recursive procedures and how
you can use them in Microsoft Access, and then demonstrates how to use a
recursive procedure to fill branches of a TreeView
control with data.
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.
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
The function prompts for a path and file name, and then uses
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.
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
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
- The third parameter, strIDField as String, is the name of
the PrimaryKey field. In the Employees table, this is the EmployeeID
- 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
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.
- Start Microsoft Access and open the sample database
- Create a new form in Design view, not based on any table or
- 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:
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:
- 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
Comments About the Code
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
- Change the constant strTableQueryName to the name of your own self-referencing table or
- 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
For more information about ActiveX controls, click Microsoft Access Help
on the Help
menu, type activex control properties
the Office Assistant or the Answer Wizard, and then click Search
to view the topics returned.
Article ID: 209891 - Last Review: June 23, 2005 - Revision: 4.0
- Microsoft Access 2000 Standard Edition
|kbhowto kbinfo kbprogramming KB209891|