Binding a Combined Form Field in Access 2007

Question

I created an Access 2007 form that displays, for example, "Products from the product table." One of the fields in the Product table is the CategoryID corresponding to this parent product category.

In the form, the category identifier should be presented as a combo box associated with the Category table. The idea here is quite simple: when choosing a new category, you must update the CategoryID in the Product table.

The problem I ran into is that when choosing a new category, updating the CategoryName category of the Category table instead of updating the CategoryID in the Product table. The reason for this is that it seems that the combo box should only be bound to the CategoryName of the Category table.

What happens if the current product has CategoryID 12, which is the CategoryName "Chairs" in the Category table, and then selects a new value, say "Tables" (CategoryID 13) in the combo box, updates CategoryID 12 with a new "Tables" category table instead Updates the Product CategoryID table to 13.

How to bind the Category table to a combox block, so that the datatext field (which I want to exist in Access) is CategoryName, and the datavalue field is CategoryID, and only the CategoryID field of the product will be updated when the selected combo box item is changed?

Edit: See accepted answer below. I also needed to change the number of columns by 2, and everything began to work perfectly.

Answer 1

You need to use both values ​​in the query for the combo box.
e.g. SELECT CategoryId, CategoryName FROM CategoryTable ... Bind the combo box to the first column of CategoryId. Set the column width for the combo box to 0in (there is no need for a second value, so there is no limit). This will hide the first column that contains the selected value; all of this shows the meaning of the description you want to see. So now, when you select another option in combobox, the value returned by the combo box will be the associated CategoryId, not CategoryName.

Oh, yes, Alison, I'm sorry, I forgot about setting combobox columncount = 2.

Answer 2

You should also verify that the category table has a primary key in the CategoryName field. The initial configuration was supposed to cause an error or message that the update would violate the key. It looks like you can have 2 categories with the same name.