Filter form depending on subform contents (A2K)

[hufflehuffle]

Hi!

I want to filter my form's contents depending on the subform's
contents.

For example: On my main form I show one record from a table with car
types. On a subform I show all corresponding individual license plates
and their owners.

Now I want to filter, let's say, on the first name "Daniel". I do this
by setting the form into a filter mode:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 0, 0, acMenuVer70

What now happens is that Access filters all sub form records, but it
doesn't hide the main record if there is no corresponding suform
record. For example:

There is nobody named "Daniel" driving a Chevrolet Nova. If this is the
case, the Chevrolet Nova in the main form shouldn't show up at all.

Even more complicated: I'd like to build compound searches (e.g. Give
me all Mercedes models that are owned by girls named "Rita", so I have
a query consisting of both a main form control and a sub form table
column).

Do I make any sense?

Thanks
Andreas

 

[Allen Browne]

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

The article explains how to set the RecordSource of the main form to an
inner join statement, so that it contains only those records that have a
match in the subform. You can use that in cominbation with other filters in
the main form and/or subform, and/or setting the RecordSource of the subform
to filter it as well.

If you are applying filters to both the main form and subform
simultaneously, you might run into some of the bugs described in this
article:
Incorrect filtering of forms and reports
at:
http://allenbrowne.com/bug-02.html