Multiple 'entry' combobox. Is it possible?

[MackMan]

Hi all.

Is it possible to have a combo box look in two different fields in a table, to return the same data based on PK?
I'm only talking hypothetically as the systems we use for work incorporate this method and it makes for easier data entry, but I'm stumped as to how this would work, based on a comboboxs' bound field.

For instance.. In my table I have a supplierID, SupplierName, and SupplierABBR (amongst others)

Can a user type in 'either' the abbreviated supplier code (SupplierABBR ) OR (SupplierName) name to allow the combobox to return the details as necessary (SupplierName)

If so, would this be taken care of using VBA, a query or expressions?

As always, appreciative of your time, and wisdom.

 

[jdraw]

This is the basic structure of what you are looking for. This is a quick query that will ask twice for the string (just because it is working with a query>

Tell us more about the combobox. You are talking about a Form, not a lookup field in a table, right? It could be done with a form and ask only once for [enter value].

SELECT <the fields you want here>
FROM <your Supplier table name here>
WHERE
 SupplierName Like  "*" & <some entered string here> & "*"  OR
 SupplierABBR Like  "*" & <some entered string here> & "*"

 

[MackMan]

Hi JDraw. Thanks for the fast reply. Like I say I was only thinking hypothetically, but would be able to utilise it in some sort of way.

So I'd have on a form a combobox and have the combobox look in two fields as the user started typing, so if the user only know the abbreviated code of a supplier, type that instead of the name, and vice versa.

We use a similar lookup method for work using part codes from manufacturer and our own part codes. Two different entries but returning the same record.

 

[sneuberg]

If I understand what you are asking for I think this can be done by using a Union query as the row source. Please check out the attached database where I tried this out and it seems to be working. Note that more columns could be added to the union query.

 

[jdraw]

So I'd have on a form a combobox and have the combobox look in two fields as the user started typing, so if the user only know the abbreviated code of a supplier, type that instead of the name, and vice versa.

I think there is a miscommunication in the above, or I'm not understanding your use of the combo.
I could see a text box in which the user enters some data, then, depending on your details, your could apply a filter to your Supplier table to find records in which the SupplierName or SupplierABBR either equals, or starts with or contains the value entered in the text box.

Are you looking for a "Find as you type" approach? If so, see Allen Browne's approach

I did find another method that uses class module. The class concerns a combobox can be added to any form and allows a "find as you type" and other features. I am trying to decipher it to learn more about class modules.

I'm attaching a jpg to show the concept.

I recently was helping a user with an issue. I created a table with 200 random voters. In my form (attached), I set parameters to use tblVoters; and to look for a string anywhere in the fields VoterFirstName, VoterLastName, VoterAddress or VoterCity.

My search string is Jen, and if you look closely, you can see that sequence in at least one of the fields.

 

[arnelgp]

maybe this will enlighten you a bit.