Form_CascadedCombosOnDatasheets
(Updated On:  07-Apr-2011)
Explanatory Notes

    In a typical arrangement of cascaded bound combo boxes, the first column (bound one - typically the foreign key value) of slave combo box is hidden. As a result, use of filtered row source for the slave combo box on a datasheet or continuous form suffers from the limitation that whenever its row source gets altered as a result of changed value held by master combo box, the displayed values for this column in other records disappear - if the existing real values do not feature in the bound column of lookup list.

    In order to overcome the problem of blank display in slave combo, the traditional method usually involves one of the following:

    (a) Placement of a bound text box on top of slave combo box. This technique is applicable only to a continuous form (not feasible in datasheet).
    (b) Always retain all items in the look up list, ensuring that only those matching master combo's current value are selectable and are placed at top of dynamic sort order. This method suffers from the drawback that the user gets burdened with a bulky look up list, carrying redundant items.

    This sample db demonstrates an entirely new approach for smooth management of cascaded bound combo boxes on a datasheet or continuous form, without suffering from any of the limitations associated with either of the conventional alternatives mentioned above. There is no need to place any overlapping control on slave combo or to display redundant items in its look up list. This is achieved by binding the slave combo to display field of look up table and storing the foreign key value (fetched through this combo) in a hidden field / column. While such an arrangement exposes the displayable look up field to potential edit action in user interface, care is taken that in reality there is no interference to the values stored in the look up  table.

    User selection in slave combo is permitted only after having done so for master combo. As long as the master combo is blank, the slave remains locked and highlighted in color.

    Although the sample demonstrates the arrangement for a datasheet form, the solution is equally applicable to continuous form view.

    Note (For update of 07-Apr-2011):
    A blank dummy record has been placed in the look up table so as to serve as a transient place holder. This way, the need to perform form's undo action on a freshly dirtied new record gets eliminated, thereby preventing skipping of autonumbers as  encountered in earlier version. Form F_Sub, when opened, looks for the existence of blank dummy record in look up table and if not found, inserts the same. 

Version: Access 2000 file format.

References:  DAO 3.6