filter by form/subform

[dhowell]

In reading some of the posts on this group, it appears as though it is
not strait forward at all to filter a form, which has subforms, by
criteria which are either on subforms or span more than one
subform.....

This MSDN article however makes it sound as simple as clicking "Filter
by Form", entering the search criterial in the form and/or it's
subforms, then just clicking "Apply Filter"......

http://support.microsoft.com/default...b;en-us;304259

I know from experience that this doesn't work.
Searching by items on the main form is fine....once you try to search
by criteria on the subforms, it just gives you ALL the records.......

Am I just missing something?

(Using Access 2003)

Doug

[Rick Brandt]

dh*****@daubert.com wrote:

In reading some of the posts on this group, it appears as though it is
not strait forward at all to filter a form, which has subforms, by
criteria which are either on subforms or span more than one
subform.....

This MSDN article however makes it sound as simple as clicking "Filter
by Form", entering the search criterial in the form and/or it's
subforms, then just clicking "Apply Filter"......

http://support.microsoft.com/default...b;en-us;304259

I know from experience that this doesn't work.
Searching by items on the main form is fine....once you try to search
by criteria on the subforms, it just gives you ALL the records.......

Am I just missing something?

You do realize that when you apply a filter to the subform it does NOT restrict
what records you will see in the main form right? Filtering the subform simply
tells Access "As I am looking at main records, limit the records in the subform
to this filter".

It is possible to filter the main form based on criteria of records found in the
subform, but none of the built in "quick and easy" filter tools will accomplish
this. You have to use the advanced filter tool and use a subquery or create the
filter in code or change the RecordSource of the main form to a query that joins
to the table for the subform records so that criteria can be applied to those
fields.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Smartin]

dh*****@daubert.com wrote:

In reading some of the posts on this group, it appears as though it is
not strait forward at all to filter a form, which has subforms, by
criteria which are either on subforms or span more than one
subform.....

This MSDN article however makes it sound as simple as clicking "Filter
by Form", entering the search criterial in the form and/or it's
subforms, then just clicking "Apply Filter"......

http://support.microsoft.com/default...b;en-us;304259

I know from experience that this doesn't work.
Searching by items on the main form is fine....once you try to search
by criteria on the subforms, it just gives you ALL the records.......

Am I just missing something?

(Using Access 2003)

Doug

You can show only records on the main form that contain items on a
subform by setting the RecordSource of the main form with something like
this in code:

Form_Main.RecordSource = CurrentDb.QueryDefs(MyQuery).SQL

....with the appropriate JOIN in MyQuery.

I suppose you could further refine this by setting the RecordSource of a
subform, but I've never tried it.
--
Smartin

[Allen Browne]

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

The article describes how to create a quzai-filter on the main form by
changing its RecordSource to an inner join statement, such that it contains
only those records that have a match in the subform.

An alternative approach is to use a subquery in the Filter string. Since the
Filter is effectively a WHERE clause, anything you can use in a WHERE clause
is allowed in the form's Filter string. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<dh*****@daubert.com> wrote in message
news:11********************@o13g2000cwo.googlegrou ps.com...

In reading some of the posts on this group, it appears as though it is
not strait forward at all to filter a form, which has subforms, by
criteria which are either on subforms or span more than one
subform.....

This MSDN article however makes it sound as simple as clicking "Filter
by Form", entering the search criterial in the form and/or it's
subforms, then just clicking "Apply Filter"......

http://support.microsoft.com/default...b;en-us;304259

I know from experience that this doesn't work.
Searching by items on the main form is fine....once you try to search
by criteria on the subforms, it just gives you ALL the records.......

Am I just missing something?

(Using Access 2003)