Microsoft KB Archive/132242

= ACC2: Sample Function Using Recursion to Display Data Tree =

Article ID: 132242

Article Last Modified on 11/6/2000

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q132242



SUMMARY
This article demonstrates how you can create a sample user-defined Access Basic function that calls itself multiple times in order to loop through a linked list of items. You can use this technique to display data in a tree structure such as a directory of managers and employees.

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



MORE INFORMATION
To create a sample user-defined Access Basic function that calls itself multiple times, follow these steps:

 Open the sample database NWIND.MDB. Make a copy of the Employees table and name it Employees2. Open the Employees2 table in Design view. Change the name of the Employee ID field to EmpID. Change the name of the Reports To field to MgrID and set the MgrID field's Indexed property to Yes(Duplicates OK). Save and close the Employees2 table.  Create a module and type the following line in the Declarations section:

<pre class="fixed_text">     Option Explicit </li>  Type the following two procedures:

Function Start_List (EmpID As Long)

Dim db As Database, rs As Recordset Set db = CurrentDB Set rs = db.OpenRecordset("Employees2", DB_OPEN_TABLE) rs.Index = "Primarykey" rs.Seek "=", EmpID If rs.NoMatch Then Exit Function Debug.Print EmpID & " " & rs![first name] & " "; rs![last name] rs.Index = "MgrID" List_Employees rs, EmpID, 1 rs.Close db.Close

End Function

NOTE: In the following sample code, an underscore (_) at the end of a  line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code.

Sub List_Employees (rs As Recordset, ByVal MgrID As Long, ByVal _        level As Integer)

Dim bm As String rs.Seek "=", MgrID If rs.NoMatch Then Exit Sub Do While Not rs.EOF If rs!MgrID <> MgrID Then Exit Sub Debug.Print String$(level,9) & rs!EmpID & " " & rs! _                [first name]& " "; rs![last name] bm = rs.BookMark  ' Save place in the recordset. List_Employees rs, rs!EmpID, level + 1 rs.BookMark = bm  ' Return to proper place for this level. rs.MoveNext Loop

End Sub </li>  To test this function, type the following line in the Immediate window, and then press ENTER:

? Start_List(2)

Note that the records from the Employees2 table are displayed in a tree format of managers and employees as follows:

<pre class="fixed_text">     2 Andrew Fuller 1 Nancy Davolio 3 Janet Leverling 11 Tim Smith 12 Caroline Patterson 4 Margaret Peacock 5 Steven Buchanan 6 Michael Suyama 7 Robert King 9 Anne Dodsworth 8 Laura Callahan 10 Albert Hellstern 13 Justin Brid 14 Xavier Martin </li></ol>

<div class="references_section">