How can I jump a form to a specific record in Access?

Question

I have an Access Form which pulls records from a table. The user will interact with the records, sometimes in chronological order, and sometimes by jumping direct to a specific record.

The table uses a field called ID as the primary key which auto-numbers when a record is created. This is what the team will use to jump directly to a record.

I need to create a textbox where the ID number can be inputted and then a button to carry out some code to jump to the specified record if it exists.

I have attempted to put bits of code together what I have found online but so far have had no such luck in making anything work.

I would be very grateful if anybody could advise me and provide a code sample to make this work! :)

Answer 1

Make use of the RecordSetClone and Bookmark properties.

Use the RecordSetClone property to obtain a copy of the form's recordset that you can manipulate without affecting the "live" recordset used by the form.

Locate the appropriate record in the cloned recordset (perhaps using the FindFirst method), and obtain the Bookmark which uniquely identifies such record in the recordset.

Set the Bookmark property of the form to the Bookmark value obtained from the cloned recordset in order to change the current record displayed by the form to the record found.