>>>>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.

Would this work?

select * from myTable where key = myKey ;
union ;
select top 1 * from myTable where name < (select Name from myTable where key=myKey) order by name descending;
select top 1 * from myTable where name > (select Name from myTable where key=myKey) order by name ascending