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!

 

[Pat Hartman]

You should probably let the wizard build the combo so that it is properly bound. Or:

ControlSource = column in your table where you want the selection to be stored.
RowSource = query that selects the departmentID's and their names from the lookup table.
BoundColumn = usually 1 which is the first column in the RowSource query.
ColumnCount = the number of columns in the RowSource query (usually 2)
ColumnWidths = 0",3" --- which hides the first column because the width is 0 and shows the second column.

If you need to show more than one column from the lookup table, it is common to add the lookup table to your form's RecordSource query so you can select whatever columns you want to display. In this case, you should set the locked property of these controls to yes to prevent accidental updating of the lookup table.

Take a look at this Sample db

 

[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.