How to link the record in a list generated from a query to its counterpart in a form?

[lookingforK]

I am working with a data entry form application via MS Access 2007 for some users to add records or modify/update existing ones.

I create a blank form (Form1), and put a query object (Query1) on the upper part of it.

• The list generated from Query1 displays 3 fields: ID, User Name, Project.

I want to add another form (Form2), which is bound to a main table for data collection/entry, as a sub-form to Form1.
Form2 will be put below the list of Query1.

So, when I double click a record in the list of Query1, Form2 should display the details for the corresponding record, so the user could modify it if he/she wants.

• For example, if the user Mike double clicks one record in the list of Query1 with "ID = 23", "User Name = Mike" and "Project = Project 2", Form2 would present the details of this record, so Mike could update it if he wants.

Do I need to use VBA?
How to deal with it?

Thank you.

 

[Pat Hartman]

To have one subform control a second subform requires 2 things.

1. One line of code and the code must be placed in the controlling form's Current event.

Me.Subform2.Form.Requery

2. The RecordSource query for subform2 needs to reference the PK of subform1 for its criteria.

Where SomeField = Forms!mainform!subform1.Form!Somefield
Replace mainform with your mainform name.
Replace subform1 with the name of the list subform CONTROL
Replace SomeField with the name of the PK/FK that links the two tables.

Now comes the lecture - Before you start writing event code, make sure that all your objects have meaningful names. You don't want to end up with things named query1 or subform2.