Filter by form

  • Thread starter Thread starter Suzanne Knapp
  • Start date Start date
S

Suzanne Knapp

In one form with subforms, I can filter records based only on fields in the
parent form - criteria entered for the subforms is ignored. In another
form, I can filter on the subform fields without a problem. I can't see any
obvious difference in the way the two forms are defined - the underlying
tables are different but otherwise they seem similar. The relationships
between the parent form and the subforms are one to many. This is Access
2000. Thanks for any ideas about what is wrong.

Suzanne Knapp
 
Suzanne said:
In one form with subforms, I can filter records based only on fields
in the parent form - criteria entered for the subforms is ignored. In another
form, I can filter on the subform fields without a
problem. I can't see any obvious difference in the way the two forms
are defined - the underlying tables are different but otherwise they
seem similar. The relationships between the parent form and the
subforms are one to many. This is Access 2000. Thanks for any ideas
about what is wrong.
Suzanne Knapp
Click to expand...

First let's define what you want to see happen. When you apply a filter to a
subform you are in effect telling Access...

"As I look at main form records (not affected by the filter), only show subform
records that match this criteria".

Is that what you want? Or are you trying to use criteria in the subform to
limit which main form records you see? While this is possible to do, it cannot
be easily done with the built in filtering tools.
 
Rick Brandt said:
First let's define what you want to see happen. When you apply a filter
to a subform you are in effect telling Access...

"As I look at main form records (not affected by the filter), only show
subform records that match this criteria".

Is that what you want? Or are you trying to use criteria in the subform
to limit which main form records you see? While this is possible to do,
it cannot be easily done with the built in filtering tools.
Click to expand...

Rick,

Yes, I do want to limit the main form records, not just the subforms. And
in one of my forms, it seems to work that way. In that form, there is only
one subform record for each main form record (though the relationship is
defined as one to many), while in the one that limits only the subform
records there are multiple subforms per form. Would that explain the
different results?

You said it was possible, though difficult, to limit main form records
according to criteria in the subform records. Can you give me some
information on doing that?

Thanks very much for your help.

Suzanne
 
Suzanne said:
Yes, I do want to limit the main form records, not just the subforms.
And in one of my forms, it seems to work that way. In that form,
there is only one subform record for each main form record (though
the relationship is defined as one to many), while in the one that
limits only the subform records there are multiple subforms per form.
Would that explain the different results?
Click to expand...

Not really. Are you sure the form that does what you want is really a
form-with-subform and not just a form bound to a query based on both tables?
Normally a filter applied to a subform ONLY affects the subform.
You said it was possible, though difficult, to limit main form records
according to criteria in the subform records. Can you give me some
information on doing that?
Click to expand...

With the advanced filter tool you can create a filter based on a sub-query. For
example, given a main form of Authors (Primary key = AuthorID) and a subform of
book titles (Primary key of AuthorID, BookID) I could look for all authors where
a BookTitle has the word "Nation" in it with a filter of...

AuthorID In(SELECT AuthorID FROM BookTitles WHERE Title LIKE '*Nation*'")

The above could be implemented in code so that the user wouldn't have to know
how to write sub-queries, but it is difficult to do (for most users) using the
built-in tools and there is no way I know of to do this with Filter-By-Form.

One could also (in code) temporarily change the RercordSource of the main form
to a query that applies criteria when joined to the BookTitles table to get the
same affect.
 
Rick Brandt said:
Not really. Are you sure the form that does what you want is really a
form-with-subform and not just a form bound to a query based on both
tables?
Click to expand...

You are right - that form is based on a query. It came from someone else's
database and I jumped to a hasty conclusion!
With the advanced filter tool you can create a filter based on a
sub-query.
Click to expand...

But would I have to anticipate all the questions that an end user would have
and develop code for each? If I have misinterpreted this solution, please
let me know. I am looking for a way for the user to ask any question he/she
likes based on the fields of the form. Many of my users are used to
Filemaker and that's how its Find function works - very similar to Filter by
Form except that main records that don't have any related records matching
the related record criteria aren't selected. Basing the form on a query may
be the solution, though the multiple subforms per form may be a problem.
I'll try it and see how it works out.

Thanks again for straightening me out on how all this works.

Suzanne
 
Back
Top