>>>Here is an interesting question. Is it possible to construct a sql-select statement that will return 3 adjacent records with the wanted keyfield in the middle? That is, a specified record WHERE keyfield = lnVal plus the record before and the record after. The 2 boundary records would be for the purpose of identifying the previous and next records for navigation purposes.
>>
>>You might be able to brute-force it by UNIONing 3 separate SELECTs, one each for the previous, current, and next rows. Rough pseudo-code:
>>

>>SELECT ;
>>  ... ;
>>  WHERE PKey = lnVal ;
>>  UNION ;
>>    SELECT TOP 1 ;
>>    ...
>>    WHERE PKey < lnVal ;
>>    ORDER BY ... DESC
>>  UNION ;
>>    SELECT TOP 1 ;
>>    ...
>>    WHERE PKey > lnVal ;
>>    ORDER BY ... ASC
>>
>>

>>
>>You'd need special handling when previous or next rows don't exist, or if another user INSERTs a new record that would be the "true" previous or next record while the first user is pondering her initial result set.
>
>Thanks Al. This might work if the base table sort was on the keyfield but if the sort was on something else like NAME we'd have a problem.

Do you mean you want physical previous and next records ? Do you have deleted records in the table?

Theoretically you can slightly modify Al's idea to use based on recno() select, but it would not work exactly if you have deleted records...

UPDATE, Tried to figure this recno() based select and failed so far.

I don't think we can use

select * from ... where recno() between (one select) and (another select).

Doesn't seem kosher to me.