Roger's Access Library Homepage
Forum Home Forum Home > Other Download Libraries > MS Access MVP Libraries > Tejpal, A.D.
  New Posts New Posts RSS Feed - Form_CascadedCombosOnDatasheets
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Please consider donating $1 per sample downloaded, (find out why here)


 Post Reply Post Reply
Message Reverse Sort Order
A.D. Tejpal View Drop Down
Microsoft MVP
Microsoft MVP

Joined: 30 Jun 2008
Status: Offline
Points: 192
Post Options Post Options   Thanks (0) Thanks(0)   Quote A.D. Tejpal Quote  Post ReplyReply Direct Link To This Post Topic: Form_CascadedCombosOnDatasheets
    Posted: 29 Mar 2011 at 12:07am

(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


Back to Top
Sponsored Links

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.