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!

 

[Pat Hartman]

When you open the Employee form from the Project list, you can use the open args to pass in the project. In the Current event of the Emp form, you can check the OpenArgs. If it isn't null, then add a filter to the subform to show only that project.

What you have is a many-many relationship. I've attached a link that shows how to manage the m-m from both directions From the class side, the sample uses a popup but from the employee side, it uses a subform. Maybe, in your case, it might be better to use popups from either direction.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[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

 

[Pat Hartman]

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.

 

[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]

 

[Pat Hartman]

I want to open a specific subform in edit mode.

Continuous form view is editable. If you have a single record form that you want to use for editing, then open THAT form directly rather than a different form where your data is in a subform.

I also told you how to position the subform once you open the main form by using a value passed in the OpenArgs.

If you don't want to use the continuous form for editing, make sure to set its "Allow" properties to prevent edits/additions and deletions. You should NEVER have multiple forms that allow editing the same record. If you do that you would need to duplicate your validation code and that is not a road you should go down. It is too dangerous to forget to update all instances of the duplicate code.

 

[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.

 

[Pat Hartman]

You're welcome. Did you change the "Allow" properties of the subform version to prevent accidental updating?