MS-Access unbound ComboBox in DataSheet?

Question

In ms-access 2007, i'm trying to make a form for a table. this table has foreign keys from 2 parent tables. so i thought i would make these fields a lookup. but i couldn't create a single lookup for each parent table because they are composite keys.

I decided to create a query in which for each of these parent tables and the child table with an extra field for each composite key. this works fine with a normal form using an unbound ComboBox... but the unbound ComboBox does not work in a DataSheet Subform. when i make changes to a ComboBox in the Subform code, they are applied to all the other ComboBoxes in the same column as well.

My questions:

• is there a way to change the values of the individual unbound ComboBox?

• is there a different control i should be using other than the ComboBox or the DataSheet Subform?

• what is the normal work around for this situation?

I cannot bind the ComboBox's because the field from the query is calculated/an-expression as I said.

Answer 1

Continuous forms and datasheets do not work well for editing in situations where combo boxes need to be changed conditionally. The problem is that if you use the OnCurrent event to set the Rowsource of the combo box, it will be OK for that row, but will then hide the stored values for other rows.

The solution is to never use continuous forms/datasheets for editing data when this is the case (I hardly ever use them for editing, in fact). You can create two subforms, a continuous/datasheet subform that functions as a list, and a detail subform, that displays one record. Make the list subform uneditable, and the detail subform editable. You can link the two of them by using the Link Child/Link Master property of the detail subform control, and set it to the PK of the list subform.

If your list subform is Me!List and your detail is Me!Form, and the PK field is MyID, your link properties for the detail subform would be:

  Master: Me!List.Form!MyID
  Child:  MyID

When you move to a different record in the list form, it will be automatically loaded in the child form. Any edits to the previously displayed detail will be saved before record departure.