indentured sequence numbers

[Dick Watson]

A little googling tells me similar subjects have been worked to death here.
But I still didn't fine a thread treating my problem.

I have a table procedure steps. Each row has a lngParent to a parent's
lngPriKey and a lngSeqWithinParent. Example:

lngPriKey lngParent lngSeqWithinParent txtStep
99 0 do some
stuff
100 1 0 do some
other stuff
101 2 9 do
different stuff
102 1 1 and do
some more
103 73 yet more
stuff

I have a query that ferrets out the linkages up to ten levels of depth in
sorted order and counts/outputs the indenture level. It covers every
possible step and can give a indentured number based on the sequence numbers
stored in the rows:

lngPriKey IndentLevel txtStepNum txtStep
99 0 0 do some
stuff
100 1 0.0 do some
other stuff
101 2 0.1.9 do different
stuff
102 1 0.1 and do some
more
103 0 73 yet more
stuff

Now things get hard.

I need to report subsets of these steps and want to sequence number the
subset of steps with indentured sequential numbering:

1 do some stuff
1.1 do some other stuff
1.1.1 do different stuff
1.2 and do some more
2 yet more stuff

I tried calling some VBA from a textbox on the report that the details
IndentLevel and then returned a text value incremented or reset in a stored
array of sequence numbers for each IndentLevel. No Joy. The textbox data
source gets calculated more than once per entry, particularly at the bottom
of pages, and the Retreat event didn't do what you'd think it might in these
cases. (Indeed, it never gets called.)

I tried calling the same code for producing the txtIndentSeqNum from the
query. Still No Joy. Just going page down/page up in the datasheet view gets
the incrementer called lots of extra times, plus there was no easy way to
initialize the array when opening the query.

Any thoughts how to produce the indented sequential numbers in a
deterministic--always start at 1.1.1.1.1.1...--and static--doesn't get
recomputed as the dataset is manipulated--way in the query?

Thanks in advance for your thoughts!

 

[Dick Watson]

2nd example 3rd row should be:
101 2 0.0.9 do different
stuff