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
Set colOrgUnits = New Collection
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
it seems to run: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
For x = 1 To colOrgUnits.Count
MyDB.Execute "INSERT INTO tblOrganisation (OrganisationID) VALUES (" & colOrgUnits.Item(x) & ");"
Next x
For x = colOrgUnits.Count To 1 Step -1
MyDB.Execute "INSERT INTO tblOrganisation (OrganisationID) VALUES (" & colOrgUnits.Item(x) & ");"
Next x
ASKER
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.
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.