If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Configuring a lookup field to display a different column
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? |
#2
|
|||
|
|||
Configuring a lookup field to display a different column
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. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "esn" wrote in message ... 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? |
#3
|
|||
|
|||
Configuring a lookup field to display a different column
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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "esn" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|