How to open a form at a specific record in Access

Question

I have a form with records for individual people with a button to view/edit a persons clearances. When I finish editing the clearance and press the Back button I want the original form (Basic Personal Information) to open at the record I have just been working on, rather than going back to record 1.

The code I have at present is

DoCmd.Close
DoCmd.OpenForm ("Basic Personal Information")

I have tried changing the OpenForm to

DoCmd.OpenForm ("Basic Personal Information"), , , "S_ID=LinkRef"

Where S_ID is the name of the field which has the persons unique ID and LinkRef is an integer of that ID. I tried a few small variations of this and eventually got it to sort of work, but it opened Basic Personal Information with only that one record available, so I couldn't look at any other people on the form (i.e. in the bottom left record navigation it was 1 of 1, when it should be for example 5 of 32).

Another thing I tried was adding the line

DoCmd.GoToRecord(acDataForm,"Basic Personal Information",acGoTo,"[S_ID] = " & LinkRef)

But obviously, the problem here is that the Offset for AcGoTo should just be a record number, so it should in this case be 5. But I don't know how to tell the program to figure out what number the record will be from the LinkRef.

I hope that makes sense, if not feel free to ask me questions and I will try to explain better, otherwise any suggestions/methods will be appreciated.

Thanks

Answer 1

I would go about your problem this way :

DoCmd.OpenForm ("Basic Personal Information")
Forms("Basic Personal Information").Form.Recordset.FindFirst "[S_ID] = " & _
            Forms("PreviousForm").[LinkRef] & ""

Meaning, I would first open the form, and then move the recordset's cursor to the desired row.