Vlad,
figured it out. Thanks
Code:
Private Sub Counter_Click()
Dim rstId As DAO.Recordset, rstChild As DAO.Recordset,rstPar As DAO.Recordset
Dim sUniqueID As String, sPlatformID As String
Dim sSQLPar As String, sSQLChild As String, sSQL AsString
Dim iCounter As Integer
' Reset EndCnt to 0
sSQL = "UPDATE Table1 SET Table1.EndCnt='0';"
CurrentDb.Execute sSQL, dbFailOnError
' Start sorting
sSQL = "SELECT qryMEQUIPWithChild.* FROMqryMEQUIPWithChild Where [Row Type]='MEQUIP' and Expr1 <> 0;"
Set rstId = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
iCounter = 1
' Parents with Children
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 BYTable1.ID;"
Set rstChild =CurrentDb.OpenRecordset(sSQLChild, dbOpenDynaset)
Do UntilrstChild.EOF
iCounter =iCounter + 1
rstChild.Edit
rstChild("EndCnt") = iCounter
rstChild.Update
rstChild.MoveNext
Loop
rstId.MoveNext
If rstId.EOF =True Then
Exit Do
End If
If sPlatformID= rstId![platform_id] Then
iCounter =iCounter + 1
ElseIfsPlatformID <> rstId![platform_id] Then
iCounter =1
End If
Loop
' Childless
sSQLPar ="SELECT qryMEQUIPWithChild.* FROM qryMEQUIPWithChild WHERE [Row Type]='MEQUIP'and qryMEQUIPWithChild.EndCnt = 0 ORDER BY qryMEQUIPWithChild.ID;"
Set rstPar= CurrentDb.OpenRecordset(sSQLPar, dbOpenDynaset)
Do UntilrstPar.EOF
sPlatformID = rstPar![platform_id]
If sPlatformID= rstPar![platform_id] Then
iCounter = rstPar![Expr2] + 1
ElseIfsPlatformID <> rstPar![platform_id] Then
iCounter =0
End If
rstPar.Edit
rstPar![EndCnt] = iCounter
rstPar.Update
rstPar.MoveNext
Loop
End Sub