Hello all, I am having trouble navigating a recordset. This is a recursive situation where there is a list of IDs in the LineItemT and some of them depend on other line items in the same table (as many levels deep as the user wants, usually only two or three). The dependencies are tracked in a table, DependenciesT with a structure of [ID], [ComponentID]. I am trying to assign a sequence number to each line item in LineItemT (Seq) by looping through the recordset to the lowest level item (one with no dependencies), assigning a Seq, and then move to the next record. The issue I am having is that rs.find "id = " & componentID is not actually moving the recordset to that position so I'm stuck in an infinite loop. Is there another step I need to take to get the recordset to actually move to that record? (PS, I know the sequence is being assigned backwards and there are a variety of issues with the code structure, I just want to figure out the navigation first. I'll take any other advice, though.)
Code:
Public Sub AssignOrder()
Dim db As dao.Database
Dim rs As dao.Recordset
Set db = CurrentDb
RunSQL "UPDATE DependenciesQ SET seq = 0;"
Set rs = db.OpenRecordset("SELECT * FROM DependenciesQ where seq = 0;")
CalcSequence = 1
Stop
rs.MoveFirst
Do Until rs.EOF
If rs!Seq > Calc.CalcSequence Or rs!Seq = 0 Then
rs.Edit
rs!Seq = Calc.CalcSequence
rs.Update
CalcSequence = Calc.CalcSequence + 1
End If
If DCount("*", "DependenciesQ", "ID = " & rs!ComponentID) = 0 Then
'End of the line - no dependencies that remain unassigned
rs.Requery
rs.MoveNext
ElseIf rs!ID <> rs!ComponentID Then
'find the component's own dependencies
rs.FindFirst "ID = " & rs!ComponentID
End If
Loop
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub