Share via

Access 2013 filtering following drop down list based on the selection of the previous drop down list

Anonymous
2013-08-01T03:38:27+00:00

Hi All!

I am developing a form and would like to restrict the input values by using multiple drop down lists. I would like to make 2 drop down lists and they are associated. I hope the results of 2nd drop down lists can be referred from the selection of the first drop down list.

For example, districts in Hong Kong (I am from Hong Kong):

In Hong Kong, there are 18 districts in 3 areas (HK Island, Kowloon, and New Territories). What I want to do is - when I select the option "Hong Kong Island" in the first drop down list, only the following options appear in the 2nd drop down list:

Central and Western

Eastern

Southern

Wan Chai

Instead of showing all 18 options/districts

The same applies to the options "Kowloon" and "New Territories"

Would the Access experts please shed some lights on this issue? Thanks a lot!!

Jack

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2013-08-01T13:29:15+00:00

    In combobox1's AfterUpdate event add a line:

    Me.combobox2.requery

    (replace the control names with your names)

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2013-08-01T11:08:35+00:00

    You'll find an example of this sort of thing in the file  ComboDemo.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This demo includes a form for 'drilling down' through a hierarchy, using the local administrative areas of county, district and parish in my neck of the woods.  If you look at the RowSource properties of the unbound combo boxes in the form header you'll see how for the district and parish controls, these reference the control above in the hierarchy, e.g. the parish combo box references the district combo box.  In the AfterUpdate event procedures of the county and district combo boxes the combo boxes below in the hierarchy are set to Null and requeried to limit their lists to the appropriate items, e.g. the district combo box's AfterUpdate event procedure requeries the parish combo box.

    The form is based on a query which references the combo boxes as parameters, so also in the AfterUpdate event procedure of each combo box the form is requeried so that it shows only the rows which meet the selections in the combo boxes.

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2013-08-01T08:06:25+00:00

    This feature is called "cascading dropdowns", and it is a FAQ so you should have no problem locating relevant articles in your search engine.

    Hi Tom and other Access experts.

    Really appreciated for your (really) quick reply!

    I tried but failed on the second drop down list. I used "tab control" function to put multiple forms into different tabs in a single form for other members of staff to use easily.

    My Access 2013 settings are as follows:

    Name of the table containing information for the 2 combo boxes: area_district

    Name of the column for area: area ([area_district].[area])

    Name of the column for district: district ([area_district].[district])

    Name of the master form (Form): Master form

    Name of the subform: 1_Demographics

    Name of the associated table with the tab: 1 Demographics (Latest)

    Name of combo box 1 (area): area ([1 Demographics (Latest)].[area])

    Name of combo box 2 (district): district ([1 Demographics (Latest)].[district])

    Syntaxes for the combo boxes:

    Combo box 1 (area):

    SELECT [area_district].[area] FROM area_district GROUP BY [area_district].[area];

    Combo box 2 (district):

    SELECT [area_district].[district] FROM area_district WHERE ((([area_district].[district])=[Forms]![1_Demographics]![area])) ORDER BY [area_district].[district];

    The first drop down menu works, but the options in the second drop down menu wouldn't change when I choose another option in the first combo box.

    Thank you for your great help again!!

    Jack

    1 person found this answer helpful.
    0 comments
  4. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2013-08-01T03:51:07+00:00

    This feature is called "cascading dropdowns", and it is a FAQ so you should have no problem locating relevant articles in your search engine.

    0 comments