Configuring a lookup field to display a different column

[esn]

I have a lookup field that references a list of plant codes. The
lookup table has these relevant fields - ID, accepted symbol, synonym
symbol, and symbol. These serve to accomodate changes in the code
used for a plant over time - the "symbol" field is the unique
identifier present for all entries. By looking up values based on the
"symbol" field I can allow either the correct code (accepted symbol)
or a synonym to be entered and correlate the code to the correct
plant. This is helpful because often the codes that appear on paper
are the synonym codes, not the current accepted symbol, and because
people using the database are often more familiar with the synonyms.
I have all 4 columns in the row source statement for the combobox and
all but "ID" are displayed as you scroll through the dropdown list.

My problem is that I don't care about the synonyms - other people
using the database do, but I would rather just see the "accepted
symbol" once the data is entered. The way I have the lookup set up
it always displays the "symbol" field - the one that the values are
referenced in when data is entered. Is there any way to have it look
up values in "symbol" but display the "accepted symbol" value?

Example:
ID Symbol Synonym Symbol Accepted
Symbol
1 CHANC CHANC
2 EPANC EPANC CHANC

As I said, looking up the "symbol" field allows someone to enter the
synonym code (EPANC) if it appears on a datasheet and automatically
correlates that with the correct code (CHANC) via the ID stored in the
table, so how do I get it to display CHANC instead of EPANC when I
look at the data in the table?

 

[Arvin Meyer [MVP]]

By using a list box you can see all the columns. A combo can also display
all the columns if dropped down. You can also add a text box to see what's
in the fourth column and set its controlsource to:

= cboComboName.Column(3)

Column(3) is the 4th column in a zero (0) based index.

 

[Allen Browne]

If you use a combo box for the symbol, you might set its properties like
this:
Row Source SELECT ID, Nz([Accepted], [Symbol]) AS TheSymbol
FROM Table1;
Bound Column 1
Column Count 2
Column Widths 0
If there is a value in the Accepted column, the combo will show that. If
not, it will show the value from the Symbol column.

I take it that Symbol is unique (no 2 rows have the same value) and required
(you can't have a record where this column is blank), so I'd be tempted to
make it the primary key (i.e. drop the ID.)