Syntax: Filter Records in Subform based on Parent Form

[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

 

[Marshall Barton]

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?

Try this:

WHERE OtherFieldID=Forms!ParentFormName.cboOtherField

 

[Dave]

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

 

[Marshall Barton]

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.

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]