Update Using Combo Box in Datasheet Form

[Samoan]

I have a datasheet form that is used to update existing data in a table. One of the fields that can be udpated is Department. On the table I'd like to update, the DepartmentID is stored. The DepartmentName is stored in a seperate table.

I would like to use a combo box so users can select from a list of Department Names, then update the DepartmentID field in the main table. However, it appears the combo box can not be "bound" to the DepartmentName field in the Department table - every time the combo box is changed it tries to update the Department table. If I make the combo box unbound, no data appears in the combo box so users can't determine what the current Department is.

Using the Form View I could set a default value for the combo box based on the current record, but that doesn't seem possible in the Datasheet View. Is there a way around this?

 

[Samoan]

Well, I've come up with a work around, although it seems a bit tedious.

I added code to the Get Focus event of the Department combo box to unbind that field (set the ControlSource to ""). I then added code to the On Change event to grab the Department ID that is now in the field and update my table with that ID. I then set the ControlSource back to what it should be.

I've got to assume there is a better way!

 

[Samoan]

You know, I see a million posts about the whole combo box bound column thing, but I never really understood how it worked. All it took was for Pat to pretty much tell me exactly what to type. Embarrassing, but I appreciate the help.