Experts Exchange connects you with the people and services you need so you can get back to work.

Limit Selection in a combo box

In my form, I wish to limit the selection in a combo box based on a value in another another combo box. The source of the data is a table which has two fields, SalesProv and SalesCity. What I want to happen is, when the user selects an entry in the SalesProv combo box, the selection of cities in the SalesCity combo box is limited to the cities belonging to that province.

Any ideas?

Thanks!

axxess

Asked: 1998-08-25

Advertise Here

Microsoft Access

1 Solution

JCresonCommented: 1998-08-25

I would try this:

Since the first combo box contains the province, you want to use the AfterUpdate event to load the second combo box on the fly. Use code to set the Row Source of the second combo box to a query or SQL Statement, whichever way you feel more comfortable with, ("SELECT SalesCity from <tablename> WHERE SalesProv = '" & Combo1.Text & "') using the Text property of the first box as a parameter. Then requery the second box to load it up with the city data. I think you'll get the idea...

LVL 1

LostSoulCommented: 1998-08-25

I always use the "On click" event rather that the "After Update" as it implies the action is triggered only after the data is saved to the table. Anyways...

While JCreson's solution is acceptable, you must prepare for all contingencies. For example, the user might want to put the city in before the province. This should be expected as it is the "usual" order of address entry. To allow for this, as users hate being told how to do things by a computer, is to allow the list of cities to contain ALL cities, then have it filtered once a province is selected so, for example, if the user picks "Toronto" then picks "Newfoundland" the "Toronto" selection is changed to a null.

LVL 5

tuviCommented: 1998-08-25

Also, prepare for users who enter new province and new city that are not in the list.

JCresonCommented: 1998-08-26

Sorry folks...was only looking at the basic problem at hand, not application wide. You both are correct and I thank you for commenting so quickly.

axxessAuthor Commented: 1998-08-26

JC: Thanks, but I'm rather new to VBA. Appreciate details of your solution re "using code to set the RowSource...requery to load City data..". Thanks as well for the helpful comments(Tuvi,LostSoul).

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Limit Selection in a combo box

Join & Write a Comment Already a member? Login.

Featured Post