Complex? Update Query

[S. McCann]

I have 2 fields in my db that include the same data type. Field "A" includes a shortform code for a value in field "B". I enter the code for field "A" in a form via a combobox valuelist. This valuelist has 2 columns. Column 1 is the code and is bound. Column 2 is the field "B" equivalent.
In my update query I am trying to update field "B" from the code in field "A". I have adjusted the FIELD PROPERTIES of field "A" within my query to show the bound column to be 2.
The resulting query just places the codes from field "A" into field "B" rather than the values in column 2 from my valuelist.
I am puzzeled as to my error. Any ideas?

 

[jwindon]

A bit puzzled by your explaination. Are you trying to put the value of the second column in your first combo to the value of another field on your form?

AfterUpdate on ComboOne should be:

Me.ControlTwo=Me.ComboOne.Column(1)

columns start at 0

 

[S. McCann]

Thanks for your help. Your suggestion worked perfectly (and simply).

 

[Pat Hartman]

You are defeating the purpose of storing data in a relational database. It is incorrect to store both the code and its description. Only the code should be stored. To obtain the description, base your forms/reports on a query that joins the main table to the "lookup" table.

The reason for not storing the description value is that if it changes in its home table, that change will not be propagated to the table where you have erroneously stored its value. This is referred to in relational database lingo as an "update anomaly". Do some reading on relational databases. It will help you to understand the type of problem that you are making for yourself.

 

[S. McCann]

Thamnks for the info Pat. It would meet my needs ideally to use a query in the report. I will make the change in my db.