Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Open a form and go to a specific record in a subform

Posted on 2010-11-14
6
Medium Priority
?
740 Views
Last Modified: 2012-06-27
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.ActiveControl, 0))

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).  
0
Comment
Question by:mtrussell
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 85
ID: 34132363
You can move to a record like this:

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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34134073
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
0
 
LVL 85
ID: 34135290
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).
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:mtrussell
ID: 34143703
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.  
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 34144019
So you're opening the Company form, and you then want to move to a record in THAT form's subform?

Open the form like this:

DoCmd.OpenForm "MyCompanyForm", , , , "CompanyID=" & ME.CompanyID

Now use the code as above to navigate to the record in that form's subform. You'll have to refer to the form a bit differently:

Dim rst As DAO.Recordset
Set rst = Forms("MyCompanyForm").Employee.Form.Recordset
rst.FindFirst "EmployeeID=" & YourEmployeeID

If not rst.NoMatch Then Forms("MyCompanyForm").Employee.Form.Bookmark = rst.Bookmark

You'd obviously have to change the names of Forms, Tables, Fields, etc to match those in your project.
0
 

Author Closing Comment

by:mtrussell
ID: 34144723
Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question