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

>
>However, it assumes that the base table was sorted on name. If it was not sorted at all, then we need to use recno() analogue.

Maybe I'm missing something, but any existing sort or index order on the base table should not matter, we're creating a new cursor. You'd need to set an overall ORDER for the final result set. But I think your sample code should work where:

- you want to find the "current" row by some PKey value
- you want to find Previous and Next by some other column (such as Name, in your example)

As long as there are appropriate index tags on the columns referenced the query should even be at least partially Rushmore optimizable.