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?
0
esn
1/8/2010 1:14:10 AM
access.forms 6864 articles. 2 followers. Follow

2 Replies
548 Views

Similar Articles

[PageSpeed] 9

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" <ericnewkirk@gmail.com> wrote in message 
news:fa6c006a-3655-4525-8aa9-f8314c57a926@f5g2000yqh.googlegroups.com...
>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? 


0
Arvin
1/8/2010 2:18:47 AM
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" <ericnewkirk@gmail.com> wrote in message 
news:fa6c006a-3655-4525-8aa9-f8314c57a926@f5g2000yqh.googlegroups.com...
> 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? 

0
Allen
1/8/2010 2:34:47 AM
Reply:

Similar Artilces:

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

update column
How would I update a column with numeric values so that there are 3 leading zeros for each row? hi it is not possible to add leading zeros to a numeric value. Mathematically, this is redundent and unnecessary. "brian" wrote: > How would I update a column with numeric values so that there are 3 leading > zeros for each row? opps. hit the post button too quick. option 1. custom format if your numeric value is 12345 then see the custom format to 00000000. note. format do not change data - it just changes the way it looks in the cell. option2. format to text then use the c...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Steps to Share Outlook on 2 different drives
This is a problem created by a dual boot of XPPSP2 on one drive C:\ and Vista on another drive E:\ that I use to format for new builds of Vista on the same box. I want to take my Outlook 2003 in box and folders on two different drives on one box and combine them so that all the emails go to one account and all the folders can be shared or used on each drive. I want to do the same with Outlook Express as well, and since this is an *Outlook newsgroup, I'll just take what I learn here and apply it to the ..dbx folder(s) in it. The idea is to receive email on both drives in one in one...

forward to: field
Hi Everyone, im looking for the AD attribute in which the 'forward to:' field of the delivery options is stored. Thank you in advance kind regards marc -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Its in the altRecipient attribute. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Marc Wenger" <haga(at).iesg--nspm-noa9h.gmx.ch> wrote in message news:opr64hs7z6fyi4rt@news.microsoft.com... > Hi Everyone, > > im looking for the AD attribute in which the 'forward to:' field of ...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

Change the Exchange Virtual Directory to different website
I would like to remove the exchange virtual directory default website and move it another website which is currently redirecting to the website I want to delete. Meaning rather than logon to OWA as http://www.wheresmylunch.com/exchange (current default website) I want to move to http://www.getyourownsandwich,com/exchange. I am using Exchange 2000 server. Rube You would change the host header on the current website. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply t...

Display of UML State Transition Event
Dear Group, I am attempting to use the UML Statechart with a couple of states and a transition between them. I select properties, Events, Change Event type, say, and can see ChangeEvent1 in the property window but... ....when I Ok back to the drawing the ChangeEvent1 is not displayed by the transition. I can enter actions in a similar way and they are displayed. How cna I get the event visible too in the drawing? Surely this is the most important aspect of a transition i.e. what caused it, regards, Colin Smith ...

Getting the BCC field by default.
I would like the BCC field to show by default but cannot find the setting for this in 2007. Anyone know where this is located? In a new message window, use the Options chunk to display the "Show BCC." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JC HARRIS asked: | I would like the BCC field to show by default but cannot find the | setting for this in 2007. Anyone know where this is located? Thanks Milly. I had...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Mailboxsize is different
hi! i've got a crazy problem. when you look at the mailboxes in the exchange system manager, then you see, e.g. user xy with 220 mb mailboxsize. but when you look at the outlook, then the mailbox size is only 150 mb. but there are no filters or something else activated. and the problem is on the local outlook and on the terminalserver outlook. i hope someone has a solution about my problem. thanks. stefan ...

How do I insert space between 2 consecutive columns of an XL Shee.
I want to have space between two consecutive columns of a worksheet (of course, without having inserted another column between the two) in order to have separated the Border Lines of the adjacent cells/columns. Please guide me if it can be done in XL. Can you achieve the effect that you're looking for by using a double vertical border down the right side of the left column and having no border down the left side of the right column? Rgds, ScottO "Shamshad Butt" <Shamshad Butt@discussions.microsoft.com> wrote in message news:1222EE13-11A9-4354-9F12-D1F1155D3902@microsof...

COUNTIF on Summary Fields
Can't use on a cell that "sums" with arithmetic operators "+". Is there a workaround, etc.? a bit more explanation? -- Don Guillett SalesAid Software donaldb@281.com "DLC" <dlcopesr@yahoo.com> wrote in message news:117v954kaf4s2f6@corp.supernews.com... > Can't use on a cell that "sums" with arithmetic operators "+". Is there a > workaround, etc.? > > ...

Can't configure Outlook Express
I had Outlook express set up for the Money newsgroup, but somehow I lost it. Now I even forgot how to set it up again. I always get the error message saying "server cannot be found". The server name I typed in was microsoft.public.money, and I am not sure what should I type in here Thanks for help In microsoft.public.money, wj wrote: >I had Outlook express set up for the Money newsgroup, but >somehow I lost it. Now I even forgot how to set it up >again. I always get the error message saying "server >cannot be found". The server name I typed in was &g...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

isinteg reports configuring tstmgr failed : ecBadVersion
we have lost our mail after NAV did it's worst. have performed eseutil /p and /d as suggested elsewhere now get error from isinteg help please Update - have now sorted this thanks ...

Problem displaying expected results with CString
I am writing a MFC program to import data from a single table database into a normalized database with numerous tables. The first component opens a recordset object to the database and performs some basic tests on the field value, and the plan is to write the records out to a spreadsheet that fail to meet any of the criteria defined for the field. Along with writing out the record, I want to populate a comments field describing what failed. I have tried to implement this with a CString variable; I initialize it to a blank string each time a new record is examined, and then as each field is che...

CRM Email Displays Size=2>
One of my users is experiencing an issue when they save an email that is tracked within CRM the email displays in CRM with "Size=2>" directly in front of certain lines of the email. So for example "Size=2>" will appear in front of someone's comments in the email or in front of their name. Does anyone know why "Size=2>" is displaying in front of the lines of an email? Thanks. Mike H. "Mike H." wrote: > One of my users is experiencing an issue when they save an email that is > tracked within CRM the email displays in CRM wit...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

Sum items in a lookup table.
I work for a bus company that has 240 different routes. I also have a table in Excel that lists monthly ridership for each route. I use it as a lookup table to get data for another report. The problem? There are 6 of the 240 routes that are served by more than one garage so they appear twice in the lookup table and I would like to get the sum of the ridership for that particular route from the table. Is this possible and if so how do I do it? I gather you're using a lookup function now to find the ridership from a given route. VLOOKUP will return (0 or) 1 value. If you use SU...

Column searching problem
I have a worksheet called "net" containing the following: NETWORK AVG MIN MAX STD SAMPLES ABC 17.17 16.26 17.71 0.4469 19 CBS 12.99 11.69 14.56 0.6524 30 NBC 15.39 14.08 16.70 0.7323 38 NBC* 15.12 14.08 15.99 0.4910 31 Fox 10.63 9.07 12.09 0.9374 8 HBO 10.94 9.07 13.22 1.2507 19 Showtime 11.52 6.84 13.23 1.9017 11 HDNet 18.14 14.22 18.85 1.1619 14 HDNet* 18.45 18.13 18.85 0.2847 13 HDNet-Movies 1...