By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,623 Members | 1,773 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,623 IT Pros & Developers. It's quick & easy.

Search on Multiple tables

P: 7
Split from thread:

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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch1_Click()
  2.    On Error GoTo problem
  3.    Dim strsearch As String
  4.    Dim strText As String
  5. '
  6.    strText = Me.TxtSearch1.Value
  7.    strsearch = "SELECT * " & _
  8.       "FROM [tblCompany] " & _
  9.       "WHERE (([Company_name] LIKE ""*" & _
  10.          strText & "*"") OR(Quote_Type LIKE ""*" & _
  11.          strText & "*""))"
  12.    Me.RecordSource = strsearch
  13. '  
  14. 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
Apr 21 '15 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 5K+
P: 5,397
You may not need a union.
Without your table structure and relationships it will be difficult to provide any guidance.
Apr 21 '15 #2

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

ID field in tblCompany is connected to Company_ID in tblSupplier

Company_ID field in tblContact is connected to Contact_ID in tblSupplier
Apr 21 '15 #3

P: 7
I have attached an image of the relationships here

Attached Images
File Type: jpg relationships_supplier.jpg (18.7 KB, 162 views)
Apr 21 '15 #4

Expert Mod 5K+
P: 5,397
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:
Apr 21 '15 #5

Post your reply

Sign in to post your reply or Sign up for a free account.