MORE INFORMATION
Method 1
One way to remember the position of the current record according to an
index is to count the record's relative position (from the beginning or
end of the table) before changing to another index. This could be
accomplished by using either MOVEPREVIOUS, with the beginning of the
table as a reference point, or MOVENEXT, with the end of the table as
a reference point. However, because this method is sequential, it is
slow on larger data bases. Below is an example, using "One" and "Two"
as arbitrary index names:
Index1Pos = 0 'using index "One"
WHILE NOT BOF(1)
MOVEPREVIOUS #1
Index1Pos = Index1Pos + 1
WEND
SETINDEX #1, "Two"
The variable Index1Pos can then be used to find the same record when
the index is returned to:
SETINDEX #1, "One"
'to get to Nth record, MOVENEXT N-1 times:
FOR i% = 1 to (Index1Pos - 1)
MOVENEXT #1
NEXT i%
This will make the original record the current one, thereby achieving
the desired result. It is important to note that this method will only
work if no INSERTs, UPDATEs, or DELETEs have been done with the second
index that would affect the position of any record under the first
index. Otherwise, the counting used above may return to a different
record than expected, because the index may no longer be in the
original state.
Method 2
A temporary variable can be used to maintain the key value(s) for the
current record for the desired index. These key values could be used
in a SEEKEQ when the original index is returned to, making the record
with those key values the current record. Assume the following type
and index are used in the example below:
TYPE DataType
Element1 AS STRING * 15
Element2 AS CURRENCY
END TYPE
DIM Temp AS DataType
OPEN "test.mdb" FOR ISAM DataType "tabletype" AS #1
CREATEINDEX #1, "One", 1, "Element1", "Element2" 'Note Unique Index
SETINDEX #1, "One"
...
RETRIEVE #1, Temp 'Marker for record to return to
SETINDEX #1, "Two"
...
SETINDEX #1, "One"
SEEKEQ #1, Temp.Element1, Temp.Element2 'Return to marked record
When this is done, the unique index property ensures that the current
record is the same as it was when the index was changed away from
"One". This method is much faster than the previous one because ISAM
does the searching; however, this method requires that the index in
question be unique so there is only one possible record for the SEEKEQ
to find. With a non-unique index, this method may not find the desired
record, because there could be several records that match the seek
criteria.
Method 3
In some situations, you may want to know the physical position of a
record within an index. This position could be used to display the
relative position of the current record to the user of the database
application, giving them an indication of "where they are" in their
database file. Knowing the record's position also allows you to easily
return to that record when indexes are changed. To do this, keep a
position element in the type used to create the table as in the
example below:
TYPE DataType
LastName AS STRING * 15
PhoneNumber AS STRING * 12
RecordPosition AS INTEGER ' keeps track of record's position in index
END TYPE
DIM Temp AS DataType
OPEN "test.mdb" FOR ISAM DataType "tabletype" AS #1
' Misc. code here that would insert records into table, leaving the
' RecordPosition element empty (zero)
CREATEINDEX #1, "One", 0, "LastName"
SETINDEX #1, "One" ' forces first record to be current record
count = 0
WHILE NOT EOF(1)
count = count + 1 ' mark first record with a one, etc.
RETRIEVE #1, Temp
Temp.RecordPosition = count
UPDATE #1, Temp
MOVENEXT #1
WEND
...
The element Temp.RecordPosition can be used as a marker in correlation
with the second part of Method 1 (for example, Index1Pos =
Temp.RecordPosition). Index1Pos can be used to count to the record
that was current when the index was last used. Also, any time a
RETRIEVE is done with index "One," the associated record number can be
displayed (for example, LOCATE 25, 77: PRINT Temp.RecordPosition).
Note that the RecordPosition field of records would have to be updated
any time records are INSERTed, UPDATEd or DELETEed, because these
operations could change the order of the records in the index.