Keyword search combo box in Ms Access

Question

I want a combo box that can do a query search from multiple fields and return the product ID. The function of this combo box is that there are three fields product ID, Name and bar code. Now the user searches from any one of those fields and the option should come from those three fields, after the user selects an option it should store it as the product Id. I tried working on this problem but the only far I could go was that i could only search from one field for example by only its name. So is this function possible to code and if it is then how to code it?

June7 June7 · Accepted Answer · 2020-04-19T15:57:20

If you want user to enter ProductID, Name, or bar code into same combobox and return the ProductID, then make the combobox RowSource a UNION query of the three fields:

SELECT ProductID, ProductID AS Data FROM tablename
UNION SELECT ProductID, ProductName FROM tablename
UNION SELECT ProductID, BarCode FROM tablename;

Set other combobox properties:
ControlSource: field to save ProductID into
BoundColumn:1
ColumnCount: 2
ColumnWidths: 0";2"

Strongly advise not to use spaces nor punctuation/special characters in naming convention. Also, Name is a reserved word and should not use reserved words as names for anything.

Keyword search combo box in Ms Access

1 Answers