mtrussell
asked on
Open a form and go to a specific record in a subform
In the database a form open when I click on a button from another form. It should d go to a specific record in a subform. Right now I am able to open a form and go to a record on a main form using the code:
DoCmd.SearchForRecord , "", acFirst, "[x] = " & Str(Nz(Screen.ActiveContro
How do I do this but go to a record in a sub-form? The main form is called Company and the subform is called Employee.
I need to open a form and go to an employee in a particular company (an contact can work for multiple companies so it is more than a 1-to-1 relationship in this which is making it a bit tricky from a coding perspective so I need to use the company ID also).
DoCmd.SearchForRecord , "", acFirst, "[x] = " & Str(Nz(Screen.ActiveContro
How do I do this but go to a record in a sub-form? The main form is called Company and the subform is called Employee.
I need to open a form and go to an employee in a particular company (an contact can work for multiple companies so it is more than a 1-to-1 relationship in this which is making it a bit tricky from a coding perspective so I need to use the company ID also).
A subform is designed to display info from a One to Many relationship.
...One Customer (Main)-->many Orders (sub)
If you are using a suboform for any other relationship (1 to 1, Self Join, many to Many, or the infamous "more than a 1-to-1") you need to be very careful on how your tables are related and how the Linking fields are set up for the subform.
With this in mind, can you tell us a bit more about this mysterious "more than a 1-to-1 relationship"? and how the main/subform is designed?
;-)
JeffCoachman
...One Customer (Main)-->many Orders (sub)
If you are using a suboform for any other relationship (1 to 1, Self Join, many to Many, or the infamous "more than a 1-to-1") you need to be very careful on how your tables are related and how the Linking fields are set up for the subform.
With this in mind, can you tell us a bit more about this mysterious "more than a 1-to-1 relationship"? and how the main/subform is designed?
;-)
JeffCoachman
I think what they're saying is the Conact-Company relationship is a Many-to-Many relationship. If that's the case, you do need to insure that your Master/Child link fields are setup correctly (you'll have more than one).
ASKER
Let me provide some more detail. I have my contacts in one table with personal information and the contacts are then are assigned to companies as employees. The contact can work for multiple companies. On the contact screen I am using a subform that pulls information from a query and displays the company names where the contact is assigned.
What I want to do is from the contact screen if you click on the company name, the companies form opens and go to the specific record of the contact in the employees subscreen.
The parent/child relationship works fine.
What I want to do is from the contact screen if you click on the company name, the companies form opens and go to the specific record of the contact in the employees subscreen.
The parent/child relationship works fine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!
Dim rst As DAO.Recordset
Set rst = Me.Employee.Form.Recordset
rst.FindFirst "EmployeeID=" & YourEmployeeID
If not rst.NoMatch Then Me.Employee.Form.Bookmark = rst.Bookmark
If you need to use CompanyID as well, just alter the FindFirst to include all needed fields.
Note also that you must use the correct name for the Subform CONTROL. I'm assuming it's named "Employee", but it may be named differently. This is the Subform CONTROL on the Main form, the one that holds the Employee Data.