Find Button in Form that looks at another table

[Guest]

Hi there, I have a form where people can input data, but I also want a button
that will act as a search facility for the users.

This button will then search within another table other than the one the
form is built on. I can get it to work with a query, but my second problem is
that if I want to search for a line in the table with 'Pain' in it, it will
only return values that start with 'Pain' and not for example 'Non-Specified
Pain'.

Thanks

 

[Allen Browne]

There could be several matches, Andy, so how about filtering the form so it
contains just the people who have the word Pain in the other table?

This example assumes:
- Your form is bound to tblClient, with ClientID as primary key.
- The other table is called tblClientSymptom, and has a matching ClientID.

Me.Filter = "EXISTS (SELECT ID FROM tblClientSymptom " & _
"WHERE (tblClientSymptom.ClientID = tblClient.ClientID) " & _
"AND (Symptom Like ""*Pain*""))"
Me.Filter = True

The query inside the EXISTS () is called a subquery. More info:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066