Record Processing (dis) Order, VBA, Access

[SetiRich]

I'm attempting to create an outline number, similar to the work breakdown structure number in Project. This generally looks something like:

0
1
1.1
1.1.1
1.1.3
1.1.3
1.2
etc...

I wrote some code to do this that uses the position in an indexed Access table, plus the "level" indicator for the record. This level indicator shows how many "indents" the record has...whether it's a child of the preceding record, a peer, or jumps up in level to a higher rating.

Here is the code...pretty much as it runs. Note that I will change things to work more elegantly for the first record number...always the only "0" level record in the table.

The array iLev holds the last or highest so far increment for the same-level counter. The first to appear is always a 0 since. The second is a 1, then a 2 etc. You'll see how this works in the

Sub CalcOutLine()
Dim myDb As Database, myRs, strOut As String, iNext As Integer
Dim iCurrLev As Integer, iPrevLev As Integer, z As Integer
Dim x As Integer, i As Integer, LastSeqNum As Integer, iLev() As Integer

On Error Resume Next
DoCmd.SetWarnings False

Set myDb = CurrentDb()
Set myRs = myDb.OpenRecordset("GPS&quot

ReDim iLev(1 To 20)

With myRs
.MoveLast
LastSeqNum = myRs.RecordCount 'Works only when all numbered from 1 to n
.MoveFirst
i = 1
iCurrLev = DLookup("[Level]", "GPS", "[RecNum] =" & i)
If iCurrLev = 0 Then
strOut = "0"
.Edit
myRs![OutLineNum] = strOut
.Update
.MoveNext
End If
iPrevLev = 0
i = 2
Do Until i = LastSeqNum
iCurrLev = DLookup("[Level]", "GPS", "[RecNum] =" & i)

iNext = iCurrLev + 1

If iCurrLev < iPrevLev Then
For z = iNext To 20
iLev(z) = 0
Next z
iLev(iCurrLev) = (iLev(iCurrLev) + 1)
Else
iLev(iCurrLev) = (iLev(iCurrLev) + 1)
End If

strOut = iLev(1)
If iCurrLev > 1 Then
For x = 2 To iCurrLev
strOut = strOut & &quot;.&quot; & iLev(x)
Next x
ElseIf iCurrLev = 1 Then
strOut = iLev(1)
End If

.Edit
myRs![OutLineNum] = strOut
.Update

iPrevLev = iCurrLev
i = i + 1
.MoveNext
Loop
.Edit 'oops, better write out the last record
myRs![OutLineNum] = strOut
.Update
End With

DoCmd.SetWarnings True

End Sub

This actually works pretty good...for the most part. But every so often, it just skips, like it's re-arranging record order for no apparent reason. I'll show the lastest result, with only the example out of order results...since the 'tween records are in order with their precedence and trailing records...

RecNum OutLineNum Level
1 0 0
2 1 1
3 1.1 2
4 1.1.1 3
5 1.1.2 3 <-- Ok...smoothies
---
458 1.9.5.3 4
459 1.9.5.3.1 5
460 2.4.7 5 <-- What?
461 2.4.8 5
462 2.4.8.1 4
463 2.4.8.2 4
---
584 2.5.4.8 5
585 2.5.4.9 5
586 1.9.5.3.2 4 <-- Pick's up the previous theme?
587 1.9.5.3.3 4 Ok for musical refrain, but not here...
---
646 1.17 5
647 1.18 5
648 2 5 <-- Wrong again...!
649 2.1 5
650 2.2 5

and errors occur throughout the file like this. It's clear that a Level 5 item should have a 5 segment code generated for it. like a 1 should have only a single integer, 2 should have 2, like 3.2

The RecNum is the indexed field. It and the Level field are long types, while the generated code field is a string...for obvious reasons.

Any idea what or where I could look to fix this? I'm on my 5th version of this over the last few weeks & it's clearly beyond me why this is happening. I suspected there was something to do with load order of the records, which is why I'm using DLookup to get the correct in-sequence record, but then, maybe I just think I am.

Any help would be greatly appreciated...

Rich