ms access combo box question

[uscitizen]

to simplify this, say i have a table with a numeric field which we'll call numero. numero uses a combo box lookup table with two columns. one is a number field and the other is a text field. we'll call the two fields in combo box numero_1 and text_1. numero_1 is the leftmost field and text_1 is to the right in the combo box lookup table. numero_1 is the field that i've bound to the numeric field called numero in the access table.

when the user hits the drop down arrow (s)he sees a two column look-up list. on the left is the numeric column with numero_1's values and on the right is the text column with text_1's values. the values of text_1's are intended to help the user select the value to go into the numeric field.

and of course, numero_1's values are being stored in the the numeric numero field in the ms access table.

i more or less inherited this way of doing this and what i would really like the user to do as (s)he enters this information is to store the value of text_1 column in another text based column which i'd gladly add to the ms access table.

if you've read this far, what is quite important to know is that the values of numero_1 have no intrinsic value!!! i didn't create this paradigm, and if i had all i would have done was create a combo box with the values of text_1 for the user to pick from -- the information in the text_1 column is a list and the values in numero_1 were assigned to that column via the AutoNumber feature as the values of text_1 were entered into the combo box in a random sequence. however......

the ms access table happens to have thousands of rows with values in the numero field --- so it's no mean task to manually convert each to its textual equivalent.

my gut tells me that ms access can be coaxed into handling the awesome conversion.

open to suggestions.

 

[WildHare]

No - don't store the text1 description with each record.

Create a LOOKUP table with the individual unique numbers and the text for them, and link it to your main table via the Numero_1 field. It will be the ONE side of a ONE-MANY relationship.

Then, what you probably want to do is go back to the main table design, and alter the combo box lookup for "numero_1" from a "value list", to the LookupTable that you just defined.

This is the preferred method for handling this type of situation. If the description for numbero_1 value of "34" changes from "Gobbledygook" to "Razzmatazz", you only have to change ONE record.

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[uscitizen]

whoops!

terminological inexactitude, sir winston churchill called it -- when i said a 'two column lookup list' what i had in mind was the fact the user is looking at a two column combo box that's gotten opened up.

however, i don't/haven't as yet established this one to many relationship in wildhare's suggestion/reply. what i do have is the reference to the combo box established in the lookup 'tab' of the numero field. they are not equivalent then i take it?