Search on Multiple tables

[Nessan123]

Split from thread:
http://bytes.com/topic/access/answer...do#post3790250

1 - How do I search more than one table in a keyword search? (I know I need a join or a UNION but I'm not sure how to implement it)

Essentially I have this code to search a set of tables:

Private Sub btnSearch1_Click()
   On Error GoTo problem
   Dim strsearch As String
   Dim strText As String
'
   strText = Me.TxtSearch1.Value
   strsearch = "SELECT * " & _
      "FROM [tblCompany] " & _
      "WHERE (([Company_name] LIKE ""*" & _
         strText & "*"") OR(Quote_Type LIKE ""*" & _
         strText & "*""))"
   Me.RecordSource = strsearch
'  
End Sub
 

And I'm wondering how do I incorporate a UNION to add a second table to the search? The 2nd table I want to search is tblContact

[zmbd]

You may not need a union.
Without your table structure and relationships it will be difficult to provide any guidance.
-z

[Nessan123]

My table structure is relatively simple.

there are three tables - tblCompany, tblSupplier and tblContact.

tblSupplier contains five fields "Company_ID", "Contact_ID", Order, Price and ID. The first two fields are linked to tblContact and tblCompany.

tblCompany has 3 fields - ID, Company_Name, Quote_Type
tblContact has 7 fields - CompanyID, First_Name, Second Name, title, email, phone, address.

Connections
ID field in tblCompany is connected to Company_ID in tblSupplier

Company_ID field in tblContact is connected to Contact_ID in tblSupplier

[Nessan123]

I have attached an image of the relationships here

[zmbd]

To be totally honest with you here, reading all of the threads, looking at what you have here... I'm Guessing at what you are actually after.

So from what it looks like to me, how I would handle this would be:
Two combboxes
One with recordsource to your tblCompany
One with recordsource to your tblContacts

Both CboBox set to show friendly name and return the primary key.
Now use the values of the the CboBoxes to search against tblSupplier building your string as needed... something along the filtering and cascade-CboBox articles:

• Microsoft Access / VBA Insights Sitemap
• How to select Records in an Access Subform from a Combo Box
• Cascaded Form Filtering
• Multiple Combobox Filter with Clear Option
• Example Filtering on a Form.