access go to record from subform

Question

I have a database in Access 2010 for contacts. I have made a form where all the contact information is displayed, but I need a way to easily navigate between contacts. I have made a search box, and I have a query that updates a continuous subform with a list with the names that match the search. Now I need a way to be able to click on a name in the subform to go to that record in the main form.

With a split form I would get what I need, but it does not work when I have tabs or navigation form. Also the continuous subform looks and works better for my purpose.

The contacts template in Access 2010 is pretty much what I am looking for, with the search box and the result list on the left, but I am not able to use the template or copy it.

Answer 1

If you use a continuous subform, simply use an on-click event on the subforms details section.

1.Click inside the details section and go to the Event tab of the property side bar.

2.Go to "On Click" and choose the code generator to enter vba to automatically get the basic environment for your process

3.Paste the following code and modify it to your needs.

DoCmd.GoToRecord acDataForm, "yourmainform", acGoTo, sub_fieldname

Imagine you have the following settings: frm_contact is your main form.

sub_contactlist is your subform showing all the listed names

contact_id is the unique identifier of your forms (both main and subform)

The final result of sub_contactlist's detail section should look like this.

Private Sub Detail_Click()
DoCmd.GoToRecord acDataForm, "frm_contact", acGoTo, contact_id
End Sub

"contact_id" refers to the current contact entry inside the subform. It might also be named differently. You could also use "Me.contact_id" for clarity purposes. The code will jump to the entry where the unique identifier matches the offset (in this case: contact_id).

In case you would not use the detail section subform but a button or anything alike on the mainform, you could even leave the "acDataForm" and "frm_contact" blank.

Note: I generally advice to omit spaces when dealing with codes whenever possible. In case you have spaces in your names, put them in square brackets. Otherwise you'll get an error. Some people use square brackets in general to somehow highlight their fields within the code. I don't.