Setting ScreenLine Index for Record

[InAFix]

Hi -

I hope I can explain this throughly enough:

I have a table, imported, that needs to have line numbers assigend
that will correspond to the line numbers displayed on the screen of
the source program. The table is imported via an ODBC link. The line
numbers assigned will be used in an Access report.

When the rows/records of the table are added (in the source program),
they are assigned a unique value called LineIndex, a link to the
previous line called PrevIndex and a link to the following line called
NextIndex. The lines collectively make up the details of an order.
So the first line of the order may have a LineIndex of 10, a PrevIndex
of zero (the first line has no previous line), a NextIndex of 21.
(Lines are added to the end of the source file, so they aren't
consecutive) It is easy enough to know that if the PrevIndex is zero,
then the LineNumber should be 1.

Here is a sample of what I've come up with so far, but I'm having many
troubles "walking" through the set of records. I think I'm missing
some way of determining the total number lines per order, so that the
loop knows when I've reached the end for that order, and then I'm
obviously missing some way of moving to the next order (I'm no expert
- but I think that's what I'm missing!)

I need to be able to:

Find the first line (with order number, PrevIndex = zero) and assign
it line number 1. Then using that line's NextIndex, find the line
where the LineIndex equals the NextIndex and assign it number two, and
so on, until I've numbered all the lines related to that order, then
move onto the next order and repeat the process, eventually coming to
the end when all the order's lines have a line number.

I'd be ever so greatful for any input/ideas.

tia


'-----------------------
Function LineNumber(Order, LineIndex, NextIndex, PrevIndex) As Long

Dim i As Integer
Dim NextLine As Integer
NextLine = (LineIndex = NextIndex)
Dim Rs as Recordset

i = 1

If Not IsNull(Order) Then
If Not IsNull(LineNumber) Then
If PrevIndex = 0 Then
LineNumber = 1
ElseIf PrevIndex <> 0 Then
rs.MoveFirst
With Order
Do Until NextIndex = 0
With LineIndex
If LineIndex = NextIndex Then
LineNumber = i + 1
End If
End With
rs.MoveNext

'move next
'start at top


Loop
End With


End If
End If
End If
End Function
'---------------------------

 

[John Nurick]

Hi,

I get the impression from what you say that the detail lines for
multiple orders are interleaved among each other, but that the lines for
any singler record appear in the file in the order in which they were
created ("Lines are added to the end of the source file").

What are the other fields in this table? In particular, is there a field
or a combination of fields that lets you tell from a single record which
order it belongs to?

If so, then each detail line for a given order with have a higher
LineIndex than the previous line for the same order, and you can get a
sorted list of the records in an order with something like

SELECT * FROM tblTheTable
WHERE OrderID = Something
ORDER BY LineIndex;

or in the whole table with

SELECT * FROM tblTheTable
ORDER BY OrderID, LineIndex;

Having done this, you can use any of the standard techniques for
assigning row numbers to a report or query.

 

[InAFix]

John, thank you for your response. Yes the detail lines are
interleaved among each other. The order number is part of the line
detail record and I will be able to use in the sample sql statement
you have provided, thank you. A sticky point though, the lines can be
deleted and new lines added (truly inserted anywhere in the source
program's screen display), so that while most records for a singular
order would be in ascending order by line index, the possibility still
exists that a new line 1 may have an index higher than say the last
line 5, due to the user deleting the original line 1 and inserting a
new line 1.

Due to this I really need to work back and forth through the detail
records to find all the lines associated with the order and number
them according to their position based on the PrevIndex and NextIndex
values. That's what I'm struggling with - I suupose each loop will
require moving back to the top of the file and then woriking down
through each record until the NextIndex = 0?

 

[John Nurick]

If sorting on LineIndex doesn't reliably return the records for a single
order in the correct sequence, then the simple queries I suggested won't
hack it.

I think there are three basic approaches, according to taste.

1) Cunning SQL. If you go to http://groups.google.com and search for
SQL "usual adjacency list approach" author:Joe author:Celko
you'll find a thread on how to construct SQL queries that do this sort
of job. That's in a SQL Server newsgroup but the principles apply to Jet
SQL, and Joe's solution has been posted in Access newsgroups too.


2) Use the Next and Previous values in the table to select the records.
I'll assume the table tblT has fields LineNumber (to receive the line
number you want to generate), LineIndex, NextIndex, PrevIndex, OrderId
(and perhaps others). It will go something like this air code:

Dim lngOrderID
Dim lngNextLine As Long
Dim lngLineNum As Long
Dim dbD As DAO.Database
Dim strSQL As String

Set dbD = CurrentDB()

For each lngOrderID in the table
lngLineNum = 1
'Set line number of first record of this order
strSQL = "UPDATE tblT SET LineNumber =" & lngLineNum _
& " WHERE (OrderID=" & lngOrderID & ") AND " _
& "(PrevIndex=0);"
dbD.Execute strSQL, dbFailOnError

'Get the next line index
lngNextLine = DLookup("NextIndex", "tblT", _
"(OrderID=" & lngOrderID & ") AND (PrevIndex=0)

Do While lngNextLine <> 0 'loop until we get to the last
'Set line number of next record
lngLineNumber = lngLineNumber + 1
strSQL = "UPDATE tblT SET LineNumber =" & lngLineNum _
& " WHERE (OrderID=" & lngOrderID & ") AND " _
& "(LineIndex=" & lngNextLine & ");"
dbD.Execute strSQL, dbFailOnError

'Get next line index
lngNextLine = DLookup("NextIndex", "tblT", _
"(OrderID=" & lngOrderID & ") AND " _
& "(LineIndex=" & lngNextLine & ");"
Loop
Next 'OrderId












3)