Find record on parent form based on subform record selection

[darnnnel]

I have a form and a subform. The subform is displayed as a datasheet. When a user selects a record on the list, I would like the parent for to show the contents of the record selected. Access 2007, has the built in SplitForm, I am creating a 2003 database. I need something similar to the splitform in access 2003.

[puppydogbuddy]

I have a form and a subform. The subform is displayed as a datasheet. When a user selects a record on the list, I would like the parent for to show the contents of the record selected. Access 2007, has the built in SplitForm, I am creating a 2003 database. I need something similar to the splitform in access 2003.

If the subform and main form are linked correctly (parent-child relationship) they should synchronize correctly. So click on the border between the main form and the subform control and reclick on the link fields to make sure they are correct.

[darnnnel]

Thanks for the reply. The records in the subform are not related to the records in the form – meaning I want the subform to show all records in the table, not only the record that is shown on the form. If I link them, the subform will only show one record, the record that is on the form. How do I show all records on the subform, and only show the record selected on the form?

[missinglinq]

This is how forms/subforms are intended to be used! If you want all of the records in your subform to show, but only the associated record in your main form to show, you need to reverse your two forms! The recordset for your current subform should be the recordset for your main form and vice vesra!

Welcome to TheScripts!

Linq ;0)>

[puppydogbuddy]

Thanks for the reply. The records in the subform are not related to the records in the form – meaning I want the subform to show all records in the table, not only the record that is shown on the form. If I link them, the subform will only show one record, the record that is on the form. How do I show all records on the subform, and only show the record selected on the form?

To answer your question:
1. create separate queries for use as the data sources for your form and subform. The queries will determine what is displayed on the form and subform.
2. do not link the form and subform.

<<<If I link them, the subform will only show one record, the record that is on the form.>>>>

The above is not a true statement. If you establish 2 tables in a one to many relationship, for each record on the "one" side, you can display the many records corresponding to the many side....which is really what a subform is for.

If you want to display all records, than use a continuous form. There is no need for a subform. I suggest you take some tutorials available thru this site before proceeding.

[darnnnel]

Thanks for the quick reply and welcome. I have set the recodset for both the form and subform the same, my problem is the VBA code for the subform. When a user double clicks on a filed on the subform, I want the main form to show that specific record. I just don’t know the code.

[puppydogbuddy]

Thanks for the quick reply and welcome. I have set the recodset for both the form and subform the same, my problem is the VBA code for the subform. When a user double clicks on a filed on the subform, I want the main form to show that specific record. I just don’t know the code.

Try this (not tested). String datatype was assumed for the common link between the main and subform. If the link is a numeric data type,syntax would change accordingly. Replace object names used for illustrative purposes with the actual name of those objects in your application.

Private Sub YourField_DoubleClick()     'this code is behind the subform
 
 Dim rs As Object
 Dim strLinkValue As String     'value in link field of the subform
 
strLinkValue =  Me![NameOfLinkFieldOnSubform].Value  
 Set rs = Forms!YourMainForm.Recordset.Clone
   rs.FindFirst "[NameOfLinkFieldOnMainForm] = '" &  strLinkValue  &  "'"      
   Forms!YourMainForm.Bookmark = rs.Bookmark
 
End Sub

[darnnnel]

Thanks for your help everyone, it worked!!