How to create a lookup field which accepts entries from both fields in the linked table

[ABB125]

Good morning,

Apologies if the title is a bit vague.

I have a table with two fields - one for the name of each record, and one for its 3-letter code, eg: Raspberry Jam = RBJ.
I am trying to create a lookup field in another table where I can enter either the 3-letter code if I know it, or the item name if I don't. Whichever I do, I am hoping that I can store the 3-letter code value in the cell.


However, I can't make it work. Can anyone help?

Here is an example:

Someone has purchased a product off me, 50mm thick insulation board. I don't know the 3-letter code for this, so I begin typing "50mm insulation board" into the lookup field. As I type each character, the lookup field automatically updates the drop-down list with possible fields; when the one I am looking for appears, I select it, and the 3-letter code, let's say INB, is stored as the cell value.
Conversely, if I know that 50mm insulation board is INB, I can type "INB" into the lookup field, and the drop-down list will show what INB is, to confirm that it is the correct 3-letter code.

Many thanks for your help.

[CJ_London]

lookup fields in tables is a bad idea - use a form

with regards your combo, suggest use a union query as a rowsource - something like

SELECT ProdID, ProdDesc FROM tblProducts
UNION SELECT ProdID, ProdID FROM tblProducts

however typing in 'INB' will show any products that begin with INB as well - Access won't know which field you want to look at

Alternative is to have two combos, both bound to ProdID one based on ProdDesc and the other of ProdID and let the user choose

[ABB125]

lookup fields in tables is a bad idea - use a form

with regards your combo, suggest use a union query as a rowsource - something like

SELECT ProdID, ProdDesc FROM tblProducts
UNION SELECT ProdID, ProdID FROM tblProducts

however typing in 'INB' will show any products that begin with INB as well - Access won't know which field you want to look at

Alternative is to have two combos, both bound to ProdID one based on ProdDesc and the other of ProdID and let the user choose

Thanks very much.