Binding a form combo box in Access 2007

Question

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

In the form, the CategoryID needs to be represented as a combo box that is bound to the Category table. The idea here is pretty straightforward: selecting a new Category should update the CategoryID in the Product table.

The problem I'm running into is that selecting a new Category updates the CategoryName 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 must be bound only to the CategoryName of the Category table.

What happens is if the current product has a CategoryID of 12 which is the CategoryName "Chairs" in the Category table then selecting a new value, let's say "Tables" (CategoryID 13) in the combo box updates the CategoryID of 12 with the new CategoryName "Tables" instead of updating the Product table CategoryID to 13.

How can I bind the Category table to a combox box so that the datatextfield (which I wish existed in Access) is the CategoryName and the datavaluefield is the CategoryID and only the CategoryID of the Product will be updated when the selected combo box item is changed?

Edit: See the accepted answer below. I also needed to change the column count to 2 and everything started 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 fist column, CategoryId. Set the column widths for the combo box to 0in (no second value need, so there is no limit). This will hide the first column which contains your selected value; all that shows it the description value, which is all you want to see. So now when you select a different option in the combobox, the value returned by the combo box will be the bound value, CategoryId, not CategoryName.

Ah, yes Alison, sorry, I forgot about setting the combobox columncount = 2.

Answer 2

You should also check that your categories table has a primary key on the CategoryName field. You original configuration should have thrown an error or message saying the update would violate the key. As it is it seems you can have 2 categories with the same name.