I have two tables: tblSource and tblCompilation. tblSource has 2 fields: SourceName and SourceValue. (SourceName stores the name of a product and SourceValue stores the price of that product.) Within tblCompilation is a field called Source that stores the value obtained from the Combo Box. Herein lies my problem.

All of the products in tblSource are the same value (SourceValue) of $2.00. The SourceName is currently the primary key and both fields (SourceName/SourceValue) are being displayed in the combo box. There is a text box on the same form which obtains the currency value from the combo box. If I choose to store the SourceName from the combo box in tblCompilationource, I get "#Error" in the text box and the calculation is halted. If I choose to store the SourceValue in tblCompilationource, then the calculation is correct and $2.00 is recorded into the text box. However, at this point I'm only permitted to select the first option in the combo box. No matter which item I select from the drop down, it displays the first item in the box only and doesn't let me switch.

I need a way to pass the value ($2.00) from the combo box to the text box on the form, but record the SourceName in tblCompilationource. This way, I can use the value of $2.00 and store the unique name at the same time, which will allow me to sort for specific records. Anyone have any idea as to what I might be doing wrong?