Vlad developed the below code and it works great. But if you add more platform_id in it will count up. Like its designed to do. What I was trying to do with Do until platform_id <> platform_id of previous count, then reset icounter back to 1.
If I put Do while and loop (3rd loop) around the below code it will lock up/run a looooooong time. How do I store the platform_id and use that to check it against the next platform_id? Then reset to new when icounter reserts to 1?
I tried Dlookup and it would stay at the first record.
I put sPlatformID = rstId("platform_id") at the beginning. That stores the first platform_id
When I put at the end inside the outer loop:
Code:
If sPlatformID = rstChild![platform_id] then
iCounter = iCounter + 1
Elseif sPlatformID <> rstChild![platform_id] then
iCounter = iCounter + 1 End If
rstid.movenext
in place of last
iCounter = iCounter + 1
rstId.MoveNext
in the outside loop.
I placed a msgbox in the loop to track splatformID and rstId("platform_id") and I watch it count up. When they don't match, the count does not reset to 1.
Code:
sSQL = "UPDATE Table1 SET Table1.EndCnt='0';"
CurrentDb.Execute sSQL, dbFailOnError
'Start sorting
sSQL = "SELECT qryMEQUIPWithChild.* FROM qryMEQUIPWithChild Where [Row Type]='MEQUIP';"
Set rstId = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
'Parents with Children
iCounter = 1
Do Until rstId.EOF
' Parents
sPlatformID = rstId("platform_id")
sUniqueID = rstId("unique_id")
rstId.Edit
rstId("EndCnt") = iCounter
rstId.Update
' Children
sSQLChild = "SELECT Table1.* FROM Table1 WHERE Table1.parent_equipment_item_id = '" & sUniqueID & "' AND[Row Type]='MEQUIP' ORDER BY Table1.ID;"
Set rstChild = CurrentDb.OpenRecordset(sSQLChild, dbOpenDynaset)
Do Until rstChild.EOF
iCounter = iCounter + 1
rstChild.Edit
rstChild("EndCnt") = iCounter
rstChild.Update
rstChild.MoveNext
Loop
iCounter = iCounter + 1
rstId.MoveNext
Loop
Suggestions?