Filter on a Subform

[HServ]

I have a form (Agency) that has a subform (Contacts) and they are linked by
the field AgencyID.

The Contacts subform has a field called Programs, which has 6 choices in a
drop-down menu. The agency form doesn't have the programs field because one
agency has contacs in many programs.

I'd like to create a filter so that only agencies who have a contact in
program 3 are displayed.

I've been told a filter won't work with a subform and I have to change the
RecordSource. How?

Thank you

 

[Rick Brandt]

I have a form (Agency) that has a subform (Contacts) and they are
linked by the field AgencyID.

The Contacts subform has a field called Programs, which has 6 choices
in a drop-down menu. The agency form doesn't have the programs field
because one agency has contacs in many programs.

I'd like to create a filter so that only agencies who have a contact
in program 3 are displayed.

I've been told a filter won't work with a subform and I have to
change the RecordSource. How?

A "simple" filter won't work, but you can use a filter that utilizes a
sub-query.

Me.Filter = "AgencyID IN(SELECT AgencyID FROM Contacts WHERE Programs = 3)"
Me.FilterOn = True

 

[Allen Browne]

See:
Filter a Form on a Field in a Subform
at:
http://allenbrowne.com/ser-28.html

The article explains how to change the RecordSource of the main form to an
inner join statement, so it is effectively filtered to those records that
have a match in the subform.

 

[Joan Wild]

I have a form (Agency) that has a subform (Contacts) and they are linked by
the field AgencyID.

The Contacts subform has a field called Programs, which has 6 choices in a
drop-down menu. The agency form doesn't have the programs field because
one agency has contacs in many programs.

I'd like to create a filter so that only agencies who have a contact in
program 3 are displayed.

I've been told a filter won't work with a subform and I have to change the
RecordSource. How?

Have a look at
'Filter a form on a field in a subform'
http://allenbrowne.com.au/ser-28.html

 

[HServ]

Can you explain a sub-query?
Is that VBA or SQL code?
Thanks

 

[HServ]

Thank you Allen and Joan, looks like what I want to do. Am I correct that
the form will open and then the user chooses which filter to run from a
combo box?

I'm also a little confused on how the link table becomes a subform because I
have only ID numbers in my link table and would like names and words to show
up in the subform.
Thanks

 

[Joan Wild]

Thank you Allen and Joan, looks like what I want to do. Am I correct that
the form will open and then the user chooses which filter to run from a
combo box?
Yes

I'm also a little confused on how the link table becomes a subform because
I have only ID numbers in my link table and would like names and words to
show up in the subform.

Just change the recordsource of the subform to a query based on the tables
involved.

 

[Allen Browne]

Yes, you could place an unbound combo on the main form, and use its
AfterUpdate event procedure to set the RecordSource of the main form.

To show the names in the combo, set its bound column to zero width. For
example, you could set these properties for the combo:
RowSource: SELECT CategoryID, CategoryName
FROM tblCategory
ORDER BY CategoryName;
Column Count: 2
Column Widths: 0

 

[Rick Brandt]

Can you explain a sub-query?
Is that VBA or SQL code?
Thanks

A sub-query is a query used within another query.

My example was VBA code that includes SQL because the Filter property of a
form is a valid SQL WHERE clause with the word "where" stripped off the
front.

 

[HServ]

Thank you both. Somehow I made it work and my problem is solved (for today!)

 

[HServ]

How would I go about creating a second filter in the subform?

I have made the first combo box (cboShowSup) enable a second combo box. I'd
like the second one to further filter the subform.

For example: Choosing "Fruit" from cboShowSup filters the records so only
fruit suppliers are shown. Also, another combo box opens listing different
fruits. I want this to create a filter so only records of fruit suppliers
selling THAT fruit is shown.

I've tried applying the same code to the second combo box but it will only
filter for fruit #5. Can you help me with the modification for a second
filter?

Thank you!

 

[Allen Browne]

What is the relationship between Fruit and Suppliers?

Does each fruit type come from multiple suppliers as well as one supplier
providing multiple fruit? If so, you will have a link table between the 2 to
resolve the many-to-many relationship. So it will be this link table that is
the related table you are trying to filter your form on, and the main form
is for fruit? Or for suppliers?

Depending on the relation, you could change the SQL statement to use the
inner join as suggested in the article. Or, you might need to use the
subquery in the Filter string, as Rick Brandt suggested in his reply.