Syntax: Filter Records in Subform based on Parent Form

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have a subform that is linked to the parent form through an ID field.

This subform contains a combobox that I want to filter based on the current
value of the control source for a combobox (cbo) in the _parent_ form.

For example, the parent form contains a cbo with a current id of 7. There
are multiple related records showing on the subform, each with a cbo that
displays a value. But when the user clicks on the cbo, it should list only
records where the id value is 7.

The syntax for the row source of the subform cbo should look something like
this:

SELECT id, field
FROM table
WHERE OtherFieldID=ParentFormName.Form.cboOtherField.value;

However, this reference is not recognized. What is the proper syntax to
reference the ID of the cbo in the parent form?

Thanks
Dave
 
Dave said:
I have a subform that is linked to the parent form through an ID field.

This subform contains a combobox that I want to filter based on the current
value of the control source for a combobox (cbo) in the _parent_ form.

For example, the parent form contains a cbo with a current id of 7. There
are multiple related records showing on the subform, each with a cbo that
displays a value. But when the user clicks on the cbo, it should list only
records where the id value is 7.

The syntax for the row source of the subform cbo should look something like
this:

SELECT id, field
FROM table
WHERE OtherFieldID=ParentFormName.Form.cboOtherField.value;

However, this reference is not recognized. What is the proper syntax to
reference the ID of the cbo in the parent form?
Click to expand...


Try this:

WHERE OtherFieldID=Forms!ParentFormName.cboOtherField
 
Thank you Marshall, that works well.

I'm still having a little trouble getting the WHERE clause to apply when I
move to the next record. For example, if the ID in record 1 is 7, the WHERE
clause operates on the cbo in the subform and only records with ID =7 show
up in the list. But when I move off record 1 to record 2 in the parent form
and record 2 has an ID of 10, then the WHERE is not applied; i.e., the same
listings displayed for record 1 are displayed for record 2 when they should
be limited to ID 10.

I need to look at this closer. I think I just need to do a Refresh or
ReQuery on the subform each time I move off the record in the parent form.

Thanks
Dave
 
Dave said:
Thank you Marshall, that works well.

I'm still having a little trouble getting the WHERE clause to apply when I
move to the next record. For example, if the ID in record 1 is 7, the WHERE
clause operates on the cbo in the subform and only records with ID =7 show
up in the list. But when I move off record 1 to record 2 in the parent form
and record 2 has an ID of 10, then the WHERE is not applied; i.e., the same
listings displayed for record 1 are displayed for record 2 when they should
be limited to ID 10.

I need to look at this closer. I think I just need to do a Refresh or
ReQuery on the subform each time I move off the record in the parent form.
Click to expand...


That's correct. To resync the combo box when the main form
record changes, use a Requery in the main form's Current
event:

Me.subformcontrol.Form.subformcombo.Requery
--
Marsh
MVP [MS Access]



 
Back
Top