Opening a specific record in a form and subform from a continuous form

[BobPaul01]

GOAL: Select an employee and project from a continuous form and open the associated employee form and project subform.

I have a table (Employees) with a primary field EmpID. And I have a related table Projects that includes the EmpID. It also has the primary field ProjID)

I have a parent form (frmEmp) and a subform (frmSubProj) related by EmpID.

I have a continuous form (frmEmpProj) that includes details for all EmpID and ProjID records

I want to select a record from the continuous form, then open both frmEmp and the appropriate frmSubProj

At the moment, I am doing this by using macro1 and opening the frmEmp with the WHERE clause: [EmpID]=[Forms]![frmEmpProj]![EmpId].

This opens up the correct employee record , but is showing the first Project record. How do I modify my code to open the project record selected?

An example would be very useful, as I am just learning how to do VBA coding.

 

[Jon]

Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!

 

[pbaldy]

There is no macro1, but generally:

[EmpID]=[Forms]![frmEmpProj]![EmpId] AND [OtherField]=[Forms]![frmEmpProj]![OtherField]

FYI I'm going to move your thread out of the introductions forum. Welcome to AWF!

 

[Sarah.M]

At the moment, I am doing this by using macro1 and opening the frmEmp with the WHERE clause: [EmpID]=[Forms]![frmEmpProj]![EmpId].

Try this in Macro1 OpenForm WHERE clause
Try1
"EmpID="&EmpID

Try2
="EmpID="&EmpID
If it is works give it like

 

[BobPaul01]

You cannot select a specific subform record in the OpenForm or OpenReport methods. The Where clause and Filter refers to the main form/report ONLY.

Thank you. Your first examples is opening a continuous subform. I want to open a specific subform in edit mode. If I can't use Where clause or Filter, how can I open a specific subform in edit mode?

 

[BobPaul01]

There is no macro1, but generally:

[EmpID]=[Forms]![frmEmpProj]![EmpId] AND [OtherField]=[Forms]![frmEmpProj]![OtherField]

FYI I'm going to move your thread out of the introductions forum. Welcome to AWF!

Thank you Paul. This does work in general. But in my case "Otherfield" is a field on the subform. It defaults to opening the first subform record.

 

[Sarah.M]

Thank you Paul. This does work in general. But in my case "Otherfield" is a field on the subform. It defaults to opening the first subform record.

Try this
"[EmpID]="&[Forms]![frmEmpProj]![EmpId] AND [OtherField]=[Forms]![frmEmpProj]![OtherField]

 

[BobPaul01]

Thanks to all. Pat - you led me down the correct path. It turned out I was making this more complicated than need be. The solution is to create a copy of the subform and use the WHERE clause to open ONLY the subform to the specific record desired. Then because the the two tables are related, I was able to add the necessary fields from the parent record to the copy of the subform. Thanks again for all the ideas.