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?