MS Access Forms: Store number is table, display text in ComboBox

Question

I'm using Access 2010.

In trying to maximize both user friendliness and data efficiency, I'd like to store or reference a record by a reference number and display it in forms by the value in a different field in that record.

For instance, in a table for recording received/processed invoices, I'm storing the vendor account by Autonumber (because there are multiple accounts per vendor and the possibility of account numbers for different vendors being the same), yet when use a form to add records to the Invoice table I'd like the form to display the vendor and account number in the ComboBox, both when it is dropped down, and when it is simply displaying what has been selected.

When I use three columns (one each for number, vendor, and account), reference the first column, and give that column a 0" width, the form stores the first visible column (vendor) instead of the number.

How can I make this work?

Answer 1

This is one of the most common UIs in Access, i.e., a multi-column combo box with a hidden ID column being the bound column. Have you walked through the process of creating the combo box with the wizard? It has all the options you need to create what you are asking for.