Remain on a new record on a form whose recordsource is a sorted query

[daschrislydon]

Hello

This seems like such an obvious thing, and maybe I'm missing something terribly easy, but how do I get this behaviour:

I have a form whose recordsource is a sorted query (so the PK field autonumber of a new record isn't the last record in the recordset)

When I add a new record, and push the Record Selector to save, the form takes me to the last record of the recordset... rather than staying on the newly entered record.

How do I get it to stay on the newly entered record?

I've tried various things in BeforeUpdate and AfterInsert but can't quite seem to crack it.

There are suggestions of RecordsetClone and Bookmark being the solution but I couldn't get anything to work...

Thanks for your help!

Chris

 

[June7]

Fairly common topic. Review https://www.devhut.net/ms-access-vba-requery-a-form-while-remaining-on-the-same-record/

 

[Pat Hartman]

The new record is ALWAYS at the bottom of the form unless you are using unbound controls and doing the insert manually. Are you actually asking to reposition the recordset each time you enter a new record so that if the recordset is sorted by last name, you go to the new record, enter the name Carter and when you save the record, you want the form to resort and show you Carter as the first record? I actually thing you will find this behavior pretty disturbing and disorienting.

 

[daschrislydon]

Hello
Thanks both.
For some reason, the behaviour I was experiencing yesterday has now stopped. And the new record being added remains on screen after I push save!
Very odd...
Chris

 

[June7]

I have never used Record Selector to save so I just tested this. New record stays as last row since form is not requeried or refreshed.

 

[daschrislydon]

Yep, I think something odd was happening with my DB....
Since posting I've migrated the data to SQL Server so something has changed....
Thanks for your help anyway!