Continuous Form Combo Box

  • Thread starter Thread starter Jinee
  • Start date Start date
J

Jinee

Hi all,

I have this form which is a continuous form, and there are
2 combo boxes that related to each other.

one combo box is called : cboWorkArea
the second one is called: cboTasks

The cboTasks 's rowsource should be based on the value
selected in the cboWorkArea.

Now in Access XP, it is not valid to set the row source
for cboTask like:

SELECT tblTasks.TaskID, tblTasks.TaskDescr,
tblTasks.WorkArea
FROM tblTasks
WHERE (((tblTasks.WorkArea) = [forms] !
[frmTrainMaster].[cboWorkarea]));

However, If i set the rowsource in cboWorkArea's
afterUpdate() Event, there is no initial value displayed
in cboTasks.

The other problem is that since it is a continuous form,
when I update the value for cboWorkArea for one particular
record, all the records changed.

How can i get around with these 2 problems? Any
suggestions appreciated.

Cheers

Jinee
 
Yes, this is a limitation of combo boxes that have the bound column hidden.

You can change the RowSource in the Enter event of the combo (or the
subform's Current event) so it only contains just the rows appropriate to
the current record. When you do that, the other rows of the continuous form
don't have anything they can display if the row they need is not in the
combo's RowSource any more.

The problem doesn't occur if the bound column of the combo is displayed,
since the other rows have that value and don't need to depend on values from
other columns of the RowSource. Consider using a Text field (20 characters
should be enough) as the primary key of the lookup table instead of an
AutoNumber. The foreign key is also a text field (same size). If you want a
sort order other than alphabetical, add a numeric field to the lookup table
to specify the order.

Another option is to change the RecordSource of the subform to a query that
includes the lookup table. You can then display the lookup value in a
(non-editable) text box on your form beside the combo. The combo permits
only the right entries for the row, and the text box helps make up for the
fact that the combo is blank on some rows.
 
Back
Top