asked on

Can A Search Box On An Access Form Search Two Separate Tables On An Access Form At Once?

I have two separate queries on an Access form. A Company Name Query and a Contact Query.
I need one Search box that would Search both queries for the Company Name and then display
the results in both queries. See below for an example of what the queries look like.

Company Name # Quotes Company Name Contact Last Call Notes
BAE Systems 20 BAE Systems Inc. John Henry 4/1/20 Please quote
Synqor 35 Synqor Company Paul Smith 4/8/20 Product Talk
BAE Company Roger Adam 4/2/20 Specs

The challenge is that there is NO exact match between any fields in these two queries. Ultimately,
I would like to search the Company Name field as a "Like" statement and in the above example if
the user entered BAE the queries would display separately as shown below:

Company Name # Quotes Company Name Contact Last Call Notes
BAE Systems 20 BAE Systems Inc. John Henry 4/1/20 Please Quote
BAE Company Roger Adam 4/2/20 Specs

If this can be done, I need to know what SQL "Contains' or "Like" statement is as I am
not an Access Programmer.

Thank you in advance for any help!!

Rick

Jim Dettman (EE MVE)

<<I have two separate queries on an Access form. A Company Name Query and a Contact Query. >>

How is your form setup? Do you have a main form (company) /sub form (Contacts) combination, or two sub forms? or do you just ave two combo's on the form, one for company and the other for contacts.

The setup is not clear (a form can only have one recordsource at a time). A screen shot would help.

Jim.

ES-Components

ASKER

Thanks for your quick response! I am trying to figure out the best method of madness. I will attach screen shots of Form View and Design View.

ASKER CERTIFIED SOLUTION

Jim Dettman (EE MVE)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer