If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
limiting list box selections
I am building a table with several list boxes. When entering a record, I
want the user's selection in one list box to effect the choices in another list box. How do a create a list box that displays particular values based on another list box |
#2
|
|||
|
|||
limiting list box selections
First off you can't do it in a table or query. You'll need to do it in a form.
Roger Carlson has an example of cascading combo boxes in his library: http://www.rogersaccesslibrary.com/f...ts.asp?TID=389 -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "RNicole" wrote: I am building a table with several list boxes. When entering a record, I want the user's selection in one list box to effect the choices in another list box. How do a create a list box that displays particular values based on another list box |
#3
|
|||
|
|||
limiting list box selections
Correlate the controls by referencing the first as a parameter in the
second's RowSource. Say you have two combo or list boxes (the former is more usual with bound controls) cboStates and cboCities the RowSource property of the second might be: SELECT CityID, City FROM Cities WHERE State = Form!cboStates ORDER BY City; Note the use of the Form property to qualify the control rather than a full reference to the form by name. This is possible where both controls are on the same from. In the AfterUpdate event procedure of cboStates you'd requery cboCities with: Me.cboCities = Null Me.cboCities.Requery Setting the control to null caters for it already containing a value which is not in the selected state. In a bound form you'd also requery the control in the form's Current event procedure, but not set it to Null: Me.cboCities.Requery However, there are a couple of possible problems when correlating combo boxes like this: 1. If used in a continuous form, and the second combo box's value is a hidden surrogate key, as with CityID in the above example (city names are not suitable as keys as they can be duplicated), then this approach won't work as once a state is selected in one row the city combo box in other rows in which a different state is selected will go blank. They'd still have the correct CityID values, so no data is lost; its just not seen. This problem doesn't arise with 'natural' keys, but these are only possible where the values are distinct, which, as noted, is not the case with city names. 2. If the above example were used in a form bound to a table of addresses which included both State and CityID columns the table would contain redundancy and not be correctly normalized. It should only contain a CityID column as this determines the State, which can be shown in a computed control on the form. Its still possible with such a normalized design to use a 'top- down' data entry sequence, selecting state first, then city, but it requires the use of a hybrid control for the city, superimposing a text box on a combo box to give the appearance of a single combo box control. You'll find examples of how to handle both of the above pitfalls, using the local administrative units of county, district and parish in my area, at: http://community.netscape.com/n/pfx/...g=ws-msdevapps Ken Sheridan Stafford, England RNicole wrote: I am building a table with several list boxes. When entering a record, I want the user's selection in one list box to effect the choices in another list box. How do a create a list box that displays particular values based on another list box -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|