Filter by form, subform

[Guest]

Access 2000 and XP

I have a main form with a subform for counties (each record can concern any
number of counties). The users want to be able to "filter by form" to get
the records in the main table that have a particular county listed in the sub
form (i.e. show me the records in the main form that have county A listed in
the sibform).

I've tried testing it out just using the regular filter by form capacity in
Access, but if I filter on the subform, it does not limit the records in the
main form. So I'm guessing I need to go ot a higher level of filtering. I
have some VBA experience, but haven't worked with fileters much. Can someone
point me in the direction of an example or sample code?

Thanks!

 

[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 that effectively filters it to show only the records
that have a match in the subform.

 

[Guest]

Allen,

thank you for the link. I've looked at it briefly and will see if I can
incorporate the code into my set of forms.
Nicole

 

[Guest]

One thing I wanted to check, the example on the website uses a link table
(tblProductSupplier, a link table with ProductID and SupplierID as foreign
keys). I do not have a link table set up in my database--is it necessary to
be able to have this type of thing work, or is there a way around it?

Nicole

 

[Allen Browne]

No. The link table is only needed if there is a many-to-many relation. (In
the example, one product is sourced from many suppliers, and one supplier
also supplies many parts.)

For a simple one-to-many relation where the "one" side is in the main form,
and the "many" side is in the subform, you can use the same technique.

 

[Guest]

Thank you. I have a simple one to many and will attempt to make it work with
the INNER JOIN sql in the query.
I appreciate your efforts in this group and on your web page.
Nicole

 

[Jeannie]

I need to do something similar. I want to stay on the same master record,
but filter the records in the embedded form.

In the sub form I have records marked as "Open" or "Closed". I want my
filter to display the sub form records that match the combo displaying "Open"
or "Closed".
Thanks!