Connected table search (1 Viewer)

Neo003

New member
Local time
Today, 18:07
Joined
Dec 18, 2021
Messages
19
I have the following items in the database

Tables
1. tbl_VendorList (ID=PrimaryKey, Vendors(Names)
2. tbl_VendorInvoice (Vendor field = [ID from Table 1] you can see the names but they're all connected to VendorList tbl, also contais other information)
Queries
1. frm_Search
Forms
1. frm_VendorInvoiceInput (Here I have a combobox with the list from tbl_VendorList.
2. frm_Search


So on to my question, In the search form where I have multiple text box that I can fill information that I need and have a query list. But I am having problem with the vendor list field name since they are number data type in tbl_VendorInvoice.

In the query it added Like "*" & [Forms]![frm_Search]![src_Vendorname] & "*", but the query shows like following.
Lets say I have the following company's in the vendor field
1. Goolge
2. Amazon
...
11. Walmart
Then I have a combobox in the search form and have data sourse connected to tbl_VendorList and if I search for Google, it shows me
Google & Walmart. Since they all have 1 in them.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:07
Joined
Aug 30, 2003
Messages
36,118
Are you wanting to limit the result to the selected vendor? Presuming so, just have the combo reference in the query criteria:

[Forms]![frm_Search]![src_Vendorname]

You only want Like and wildcards if you're trying to match part of the name.
 

Neo003

New member
Local time
Today, 18:07
Joined
Dec 18, 2021
Messages
19
Are you wanting to limit the result to the selected vendor? Presuming so, just have the combo reference in the query criteria:

[Forms]![frm_Search]![src_Vendorname]

You only want Like and wildcards if you're trying to match part of the name.
That solves the problem with vendor but as none of the other field's results shows up now.
What I would like is to type in any one or multiple fields like invoice number, date, vendor name etc., and if they all match with one or multiple records they show up in the query.
But that doesn't happens, hence me adding like in the query.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:07
Joined
Jul 9, 2003
Messages
16,245
I have a set of videos on my website that demonstrate how to build a dynamic SQL statement. You can see the videos here:-

Want to build yourself a Search Form?​


The videos show the process I went through in creating this Search Form...

Nifty Search Form​


Nifty Access Search Form DEMO​

 

Neo003

New member
Local time
Today, 18:07
Joined
Dec 18, 2021
Messages
19
I am not much familiar with sql in vba, I'm still learning. This is what I currently have in the db,
tbl_vendor
-ID (PK)
-Vendor Names

tbl_vendorinvoice
-Vendorname (Dropdown list from tbl_vendor, saves number as a referance to one of the record from tbl_vendor Vendor Names).

how should I put in a sql statement.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:07
Joined
Feb 19, 2002
Messages
42,977
You are using a table level lookup and that is what is causing the problem with the data type. Combo boxes belong on forms NOT on tables. They are a crutch for people who don't know how to build a query. Once you do ANYTHING with the lookup field except to simply select it, you're going to run into issues. So, REMOVE the table level lookup for starters. #2. It isn't cool or informative to name your PK "ID". Use names which relate to the table and use ID as the suffix. If the table name is tblVendorList, then the PK should be VendorListID. Then use the same name when you need to add a FK to tblVendorList in some other table.
 

Neo003

New member
Local time
Today, 18:07
Joined
Dec 18, 2021
Messages
19
Reason for the combo box was for new vendors to be added later, and available right away for entry. But I understand.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:07
Joined
Feb 19, 2002
Messages
42,977
Users NEVER access tables or queries. They only have access to forms/reports so putting a table level lookup on the table is a crutch for YOU, not help for THEM. The combo on the form helps the users.
 

Users who are viewing this thread

Top Bottom