Next record

[Barry Beiles]

I have a subform which has a primary key (Admission ID) with a random
autonumber and another field (Date of Admission). I have a command
button which should find the last admission by date, but despite sorting
by Admission date in ascending order, the record returned is always in
order of the autonumber of the primary key. How can I obtain navigation
by the Admission date (either next or last)?

 

[tedmi]

The record source for your subform should be not a table but a query, with
this clause at the end:
ORDER BY [Date of Admission]

Then your subform will navigate by date instead of Autonumber.

 

[Jim Bunton]

"I have a command

button which should find the last admission by date"

without seeing the code for this it's impossible to say what's going on.

However why not define the subform's query (recordsource) in date descending
order - the most recent record should then be the one which is shwn first.
Then going next next (using the subforms navigation buttons) should show
records for older and older admissions.