Help with using a subquery to filter on multiple tables using asingle form

[jcf378]

Hi all--
Does anyone have any insight as to how I might create a search form
that allows a user to select criteria based on any related table in
the whole database. The search form I have now only allows me to
filter based on variables in a single table. I would like to have a
search form where I can select multiple variables (from various linked
tables) to filter by, and return results based on this multi-table
filter.

Allen Browne has a wonderful description of how to create a simple
search form: <http://www.allenbrowne.com/ser-62.html>, and he even
alludes to a way "to filter on other tables that are not even in the
form's RecordSource, use a subquery" -- on his subquery page (<http://
www.allenbrowne.com/subquery-01.html#Search>), he describes using
subqueries in the context of his simple search form to create a search
form "where the user can select criteria based on any related table in
the whole database". I was wondering if anyone had some thoughts as to
how I might actually code something like that (basically, how to code
a search form similar to that which he has on the bottom of his
subquery-01.html page).

thanks,
-jason

[Allen Browne]

Hi Jason

There is a significant amount of code to write to handle such as search
form.

For example, the screenshot shows the Donations, so the code has to visit
each of the contorls you see on that page, and build a WHERE clause to use
in the subquery for the Donations table that covers each of those
conditions. You actually build up the WHERE clause for the subquery in the
same way you built the strWhere string for the search form here:
http://allenbrowne.com/ser-62.html

You then create a massive top-level query statement for the results. Its
WHERE clause contains all the subqueries for the tables the user chose to
put criteria in. So in the end, the query statement ends up like this:
SELECT tblClient.* FROM tblClient
WHERE EXISTS (SELECT DonationID FROM tblDonation
WHERE tblDonation.ClientID = tblClientID
AND tblDonation.DonationDate #1/1/2007#)
AND EXISTS (SELECT ...

--
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.

<jc****@gmail.comwrote in message
news:12**********************************@d21g2000 prf.googlegroups.com...

Hi all--
Does anyone have any insight as to how I might create a search form
that allows a user to select criteria based on any related table in
the whole database. The search form I have now only allows me to
filter based on variables in a single table. I would like to have a
search form where I can select multiple variables (from various linked
tables) to filter by, and return results based on this multi-table
filter.

Allen Browne has a wonderful description of how to create a simple
search form: <http://www.allenbrowne.com/ser-62.html>, and he even
alludes to a way "to filter on other tables that are not even in the
form's RecordSource, use a subquery" -- on his subquery page (<http://
www.allenbrowne.com/subquery-01.html#Search>), he describes using
subqueries in the context of his simple search form to create a search
form "where the user can select criteria based on any related table in
the whole database". I was wondering if anyone had some thoughts as to
how I might actually code something like that (basically, how to code
a search form similar to that which he has on the bottom of his
subquery-01.html page).

thanks,
-jason