Customizing Two or More Combo Boxes
This shows how to create a
custom drop down list that is dependant on a previous custom combo box choice on the same form.
For instance the user has chosen a country from a dropdown list and now you want
the cities box to only show cities in that country.
You can create your own database or use an existing
one or download the example which has the table already in it.
combobox.mdb
or zipped combobox.zip
Open the database and create a new form.
Don't link it to any tables.
Create an unbound combo-box on the form,
using the wizard. You want the combo-box to look up values in a table or query.
Choose CityList as the table to get the list from. Put just the Country field
into the list. leave the label as Country.
Create a second combo-box the same way this
time choosing Country and City for the fields and City as the unique field, and
City as the label.
Right click the country combo box and choose
properties. On the 'Other' tab change the name to PickCountry , and then select
the 'Data' tab ad click on the Row Source to highlight it then click the small
button with three dots. This opens the query the combobox is based on and we
need to modify it slightly to group the Countries so it doesn't display
multiples. Choose Menu -> View -> Totals A little box with groupby
should have dropped into the query. that's all we need to do here so close the
query and choose yes to save changes.
Close the properties box and save the form as
Form1.
Bring up the properties for the City combobox
and change it's name to city and then bring up it's row source again by
selecting the data tab, highlighting the row source box and clicking the little
button with three dots on it. Okay this one is a little more complex. In the
criteria section for the Country field type the following
forms![Form1]![PickCountry]
|
Close the query saving the changes.
Open the form and choose a country from one box and notice the
second box lists only cities in that country. Now change the country box again
and choose a city. Wait a minute you say it's not right. When access loads a
list into a combo box it only does it once. You need to tell it to do it again.
Back into design mode and bring up the properties for the City
combo Box. Choose the event tab select On Enter and click that button with three
dots then choose code builder. Access will write the start of the code for you
all you have to do is type the following.
this tells access that every time the city box is given the focus
requery it to get the latest list. Close the code window save and open the form.
Give it another go. Et Voila.
Don't want the city box clutered up with the list of countries.
Go into the properties and choose the format tab and see the section column
widths. Change the first col to 0cm so it looks like this 0cm;2.542cm
That will hide the column.
Happy coding.
|