Search feature

[Josh]

Can someone get me started on making a search feature for my database.

Tables and fields:

tbPeople: PeopleID (PK), PeopleName
tblAnimals: AnimalID (PK), PeopleID (FK), AnimalName
tblColors: ColorID (PK), PeopleID (FK), ColorName

Relationships:

I have One-to-Many relationships from the People table to the other tables
using PeopleID as the foreign key:

tblPeople -> tblAnimals
tblPeople -> tblColors

Form:

I want to create a search button on the form that will work database wide,
so a user can search for an Animal or Color and the search results will be
all the People who have that search term. The user should then be able to
scroll through the result people by using the built-in navigation bar at the
bottom of the form.

 

[Allen Browne]

Use a subquery in the Filter of your form. You will need two skills to
achieve this:
a) Some understanding of how to use VBA to create a filter string;
b) An understanding of subqueries.

Here is an example database that explains how to create form with several
unbound controls where the user can enter search criteria. You then build a
filter string from the boxes where the user entered something, and display
the results. The article is:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Since you need to search other tables as well, your filter string will need
to contain subqueries. If they are new, here's an introduction:
http://allenbrowne.com/subquery-01.html
The last part of that article illustrates how to use a subquery in a filter
string:
http://allenbrowne.com/subquery-01.html#Search

If these are both new concepts, I would encourage you to work through
stand-alone example of each before combining them.

 

[Josh]

Thank you! This is exactly what I am looking for.

Your tutorials are great: clear, well written, with relevant examples. I
like the "notes" part where you put in tips, exceptions, and quirks.

I will try your advice this week. Check back here at the end of the week,
as I may have questions.

Thanks again.

 

[Josh]

Hopefully, you will see this late reply. Remember, I am trying to build a
search feature that returns the Foreign Key from various tables based on user
input.

I have had some issues that you don't mention in your tutorials. The first
problem is where I kept getting the error that the subquery was returning
more than one record. I solved that by using the SQL "IN" operator in the
filter instead of "=", for instance: "Filter = [MyID] IN (subquery...)". I
am able to query multiple fields in a single table doing this.

The second problem is where I am trying to build a sql statement that will
query multiple tables and filter my main form with it. I keep getting the
'more than one record' error. How do I join the multiple subqueries to query
different tables and get one result I can pass to the form filter?

 

[Josh]

The subquery tutorial works great when working with a single table. However,
I must query several different tables to have a complete search feature.

How do I subquery several tables and return a single field I can pass to the
form filter?