Access 2016: Using recursions to crawl through table with self-join
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.
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.
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
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
Last Comment
I guess it is because you for every iteration reset the collection, which may exit the calling loop:
If you move the collection inside the function:
Set colOrgUnits = New Collection
If you move the collection inside the function:
Dim colOrgUnits As Collection
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim x As Integer
Set colOrgUnits = New Collection
Set MyDB = CurrentDb
Dim qdf As DAO.QueryDef
preorderProcessing 0
Now checking units:
In this stack: 4
COUNT: 1
Organisational Unit: 4
Now checking units:
In this stack: 8
COUNT: 1
Organisational Unit: 8
Now checking units:
In this stack: 10
In this stack: 12
In this stack: 13
In this stack: 17
In this stack: 22
In this stack: 25
COUNT: 6
Organisational Unit: 10
Now checking units:
COUNT: 0
Organisational Unit: 12
Now checking units:
COUNT: 0
Organisational Unit: 13
Now checking units:
In this stack: 14
In this stack: 15
In this stack: 16
COUNT: 3
Organisational Unit: 14
Now checking units:
COUNT: 0
Organisational Unit: 15
Now checking units:
COUNT: 0
Organisational Unit: 16
Now checking units:
COUNT: 0
Organisational Unit: 17
Now checking units:
In this stack: 18
In this stack: 19
In this stack: 20
In this stack: 21
COUNT: 4
Organisational Unit: 18
Now checking units:
COUNT: 0
Organisational Unit: 19
Now checking units:
COUNT: 0
Organisational Unit: 20
Now checking units:
COUNT: 0
Organisational Unit: 21
Now checking units:
COUNT: 0
Organisational Unit: 22
Now checking units:
In this stack: 23
In this stack: 24
COUNT: 2
Organisational Unit: 23
Now checking units:
COUNT: 0
Organisational Unit: 24
Now checking units:
COUNT: 0
Organisational Unit: 25
Now checking units:
In this stack: 26
In this stack: 27
In this stack: 28
In this stack: 29
COUNT: 4
Organisational Unit: 26
Now checking units:
COUNT: 0
Organisational Unit: 27
Now checking units:
COUNT: 0
Organisational Unit: 28
Now checking units:
COUNT: 0
Organisational Unit: 29
Now checking units:
COUNT: 0
ASKER
Yes that was the issue!
However, I noticed something strange..
I've added the values into a new table and I do seem to get a hierarchy - but it is the wrong way round.
The CEO is at the bottom - instead of at the top
I tried to reverse the way it is added to the table
But I still have the same problem.
Is it the way I am adding the items/the organisational units into the collection?
I've attached the DB with the changes.
DB2.accdb
However, I noticed something strange..
I've added the values into a new table and I do seem to get a hierarchy - but it is the wrong way round.
The CEO is at the bottom - instead of at the top
For x = 1 To colOrgUnits.Count
MyDB.Execute "INSERT INTO tblOrganisation (OrganisationID) VALUES (" & colOrgUnits.Item(x) & ");"
Next x
I tried to reverse the way it is added to the table
For x = colOrgUnits.Count To 1 Step -1
MyDB.Execute "INSERT INTO tblOrganisation (OrganisationID) VALUES (" & colOrgUnits.Item(x) & ");"
Next x
But I still have the same problem.
Is it the way I am adding the items/the organisational units into the collection?
I've attached the DB with the changes.
DB2.accdb
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Maybe you'll find the nested set model usefull.
A query will do all the "recursive" work.
See this article:
http://mikehillyer.com/art
A query will do all the "recursive" work.
See this article:
http://mikehillyer.com/art
ASKER
Now it works - I will try it at work tomorrow with the original DB.
Thanks a lot for the explanation Gustav. Also thanks Fabrice for the link. I will read it when I have a bit more time tomorrow.
Thanks a lot for the explanation Gustav. Also thanks Fabrice for the link. I will read it when I have a bit more time tomorrow.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
What is/are the expression value(s) on that line when it fails?
Do you know how deep you are in the recursion?
My gut recommendation is to break this up where you don't create a copy of the database object every time or replace the currentdb with dbengine(0)(0).
Unless the query is overly complicated, drop the querydef and use a simpler SQL string.