ACC2: Sample Function Using Recursion to Display Data Tree

Last reviewed: June 8, 1997
Article ID: Q132242
The information in this article applies to:
  • Microsoft Access version 2.0

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:

  1. Open the sample database NWIND.MDB.

  2. Make a copy of the Employees table and name it Employees2.

  3. Open the Employees2 table in Design view.

  4. Change the name of the Employee ID field to EmpID.

  5. Change the name of the Reports To field to MgrID and set the MgrID field's Indexed property to Yes(Duplicates OK).

  6. Save and close the Employees2 table.

  7. Create a module and type the following line in the Declarations section:

          Option Explicit
    

  8. 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
    
    

  9. 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:

          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
    
    

REFERENCES

Microsoft Access "Building Applications," version 2.0, Chapter 11, "Working With Sets of Records," pages 242-267


Additional query words: recursive re-enterable re-entry bill of material
Keywords : kbprg PgmHowTo PgmLoop PgmPrcs
Version : 2.0
Platform : WINDOWS
Hardware : X86
Issue type : kbinfo


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: June 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.