How to show 3 values but display 3rd from drop down box

[Pharma Down]

Hi
I have created a table (data entered/selected via a form) which includes a drop down box, for 'Ethnicity' to pick a value from another table (ie used Lookup wizard to for options from another table).

The row source looks like this:
SELECT [Lookup_Tbl_ethnicity].

, [Lookup_Tbl_ethnicity].[Category], [Lookup_Tbl_ethnicity].[Sub-category] FROM Lookup_Tbl_ethnicity ORDER BY [Code];[/COLOR]
 
Setting the ORDER BY [Code] displays the options in the drop down box in the correct sensible order.
 
The [B]'Bound Column'[/B] is column 3 - [COLOR=blue]Sub-category[/COLOR].  I assumed that this would be the 'value' stored, searched and displayed... but the table (and related form) display the [COLOR=blue]Code[/COLOR].  
 
Why and please can someone advise me on how to chage this so that the [COLOR=blue]Sub-category[/COLOR] is displayed?!
 
Thanks
 
Andy

 

[pr2-eugin]

Hello Andy, did you use the Wizard to obtain the values? or did you make a manual query? Also the Combo Box Control source.. i.e. its bound field on the table are the data types Text?

 

[Pharma Down]

Yes - used the wizard! Yes - the data type is text; the lookup table that I was accessing via the wizard did have 4 values: ethnicity_ID (autonumber/primary key); code; category; sub-category. However, using the wizard with this set-up made the data type = number... whether or not I added the ethnicity_ID using the wizard, so I deleted the ethnicity_ID from the lookup table, leaveing the 3 fields that are now included.

Andy

 

[boblarson]

The value stored in the table SHOULD be the CODE, not the text value. But for display, you would need to place, as the first field selected in the combo's row source to be the field you want displayed. That is, if you want all three fields to be visible when selecting. If you just want the text value of the 3rd column displayed, you don't have to change your row source's field order but instead set the column widths property to 0";0";2".

 

[pr2-eugin]

You are right, Bound Column is the one that will be stored but NOT ALWAYS displayed and searched upon.. Some simple checks,
1. Make sure the Column Count is 3,
2. Check the Size of the combo Box.. Make sure it is something like 0cm, 0cm ,3cm

Simple solution would be to recreate the Combo Box, delete the old one and replace it with a new combo box..

 

[Pharma Down]

OK...
If I want to be able to see all three fields when selecting, but display the third field once selected, then I have to switch round the order, so that that the third field is displayed first? Hmm... might be messy.

If I reorder the column widths to 0cm, 0cm, 5cm then the selected third field will be displayed, but I won't be able to see the first of second fields (columns) at any point - is that right? So what would be the point in including the first or second fields at all?

Why should the stored value be the code? NB: the 'code' is the enthnicity code, eg:
Code A; Category: White; Sub-category: British. The 'code' is not an autonumber or primary key - this lookup table (from where the enthicity options are sourced) does not have a primary key.

Andy