'Sanity Check' - combine macro, delete query and make table query

[Mike Webb]

Using Access 2K2; experience level: beginner
===============================================

Situation: Trying to create a parameter search form. My 'base' is 3 tables;
one contains our Contacts, one contains the locally assigned categories
(such as "donor", "US Fish & Wildlife Service", etc.), and the last is a
Join table. The parameters for the search form are: Name, a logical
operator (AND/OR), and Category.

My thoughts are: Create a Make-Table query to combine the data from my
'base'. Use this new table as my Form Record Source. In order to keep the
data in this table current, I plan to use a delete query to delete the table
on form close. I figure I can put it all together as follows: create a
macro to run on Form Open to run the Make Table query, and then another
macro on Form Close to delete the table.

I've looked at MS Access help, Googled the Access newsgroups on Macros and
Queries, and looked at the Access MVPS website for advice. I have drawn it
out on paper this afternoon and feel I've reached the only conclusion that
will work for my skill level.

What I'd like to know is: am I going down the wrong path? It seems a bit
cumbersome, but I can't figure another way to do it. I'd sure appreciate
any comments or advice the readers can give me.

Cordially,
Mike Webb
Platte River Whooping Crane Maintenance Trust, Inc.
a non-profit 501(c)(3) organization

 

[Allen Browne]

Hi Mike

You have a main form bound to the Contacts table, with a subform bound to
the junction table, and you want to filter the main form to just one Cntact
(easy) AND/OR a Category in the subform.

If so, see:
Filter a Form on a Field in a Subform
at:
http://members.iinet.net.au/~allenbrowne/ser-28.html

The article describes how to reassign the RecordSource of the main form to
an INNER JOIN statement so it contains only those records that have a match.
It could also be done with a subquery in the Filter property of the main
form, which may be simpler since you want to use AND or OR in the filter
property.

 

[Mike Webb]

THANKS>

Hi Mike

You have a main form bound to the Contacts table, with a subform bound to
the junction table, and you want to filter the main form to just one Cntact
(easy) AND/OR a Category in the subform.

If so, see:
Filter a Form on a Field in a Subform
at:
http://members.iinet.net.au/~allenbrowne/ser-28.html

The article describes how to reassign the RecordSource of the main form to
an INNER JOIN statement so it contains only those records that have a match.
It could also be done with a subquery in the Filter property of the main
form, which may be simpler since you want to use AND or OR in the filter
property.