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.

Answer 2

maybe this help you: try to open form in dialog:

DoCmd.OpenForm ("Basic Personal Information"), , , , , acDialog

Answer 3

See this page: http://msdn.microsoft.com/en-us/library/bb237964(v=office.12).aspx

I was hoping to find some kind of indexOf() method, but I don't know what the containing class is called. So, here's a work around instead, because I'm not all that familiar with VB:

You can use GoToRecord to go to the first record by passing in 0 for the fourth parameter. Then, loop through all the records using acNext, until you find the entry with the correct S_ID value. Then you can stop there and your entry will be the current one in the list.

It's far from perfect or optimal but it'll work if no other options are presented