troubleshooting Question

Access 2016: Using recursions to crawl through table with self-join

Avatar of Massimo Scola
Massimo ScolaFlag for Switzerland asked on
DatabasesMicrosoft AccessVisual Basic ClassicVBASQL
6 Comments1 Solution190 ViewsLast Modified:
I am developing an employee database. I added all employees to an organisational unit (department etc) - and each organisational unit belongs to another organisational unit, except for the top one which is blank; the CEO. With a self-join, I can then create a hierarchy once all the data has been added.

 
 screengrab2.png

I tried creating a function which would populate a collection from the uppermost organisational unit (the CEO) all the way down and I used recursions to traverse the tree.

Function preorderProcessing(parentID As Integer)

    Dim MyDB            As DAO.Database
    Dim MyRS            As DAO.Recordset
    Dim x               As Integer
    
    Set MyDB = CurrentDb()
    Dim qdf As DAO.QueryDef
    Set colOrgUnits = New Collection
    
    If parentID = 0 Then
        Set qdf = MyDB.QueryDefs("qyrOrgUnits_SearchParentID_Null")
    Else
        Set qdf = MyDB.QueryDefs("qryOrgUnits_SearchParentID")
        qdf.Parameters("parentID") = parentID
    End If
    
    Dim rs As DAO.Recordset
    Set rs = qdf.OpenRecordset
    
    Do While Not rs.EOF
      colOrgUnits.Add (rs.Fields("org_OrganisationUnitID"))
      rs.MoveNext
    Loop
    rs.Close
    
    'debug
    Debug.Print "Now checking units: "
    For x = 1 To colOrgUnits.Count
        Debug.Print "In this stack: " & colOrgUnits.Item(x)
    Next x
    
    Debug.Print "COUNT: " & colOrgUnits.Count
    If colOrgUnits.Count > 0 Then
        For x = 1 To colOrgUnits.Count
            Debug.Print "Organisational Unit: " & colOrgUnits.Item(x)
            preorderProcessing (colOrgUnits.Item(x))
        Next x
    End If
    
End Function

The problem with this function is, that I get a run-time error 5: Invalid procedure call or argument.
This happens when the recursion reaches the organisational unit #10 - the assistant.

What do I need to do, in order to make this code work .. and that it will add all organisational units from the topmost downwards into the collection?
Or is this an issue with recursions?

Thanks for your help

Massimo
DB.accdb
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros