Unanswered: MS Access Dynamically Changing Combo Box
In the following scenario, I cannot get step #4 to work. I have tried two different methods and neither has fully succeeded.
1. I have a MS Access form where the record source is client, and various fields from that database table are filled in.
2. One field is a Combo Box which lists, from a country database table, all of the countries.
3. The next field is also to be a Combo Box, which lists, from a city database table, cities.
4. I want it to list not ALL cities, but rather ONLY those cities of the currently selected country. (The city database table has a field indicating which country each city is in.) This means that every time the user changes the country, the choices available for the city combo box should change correspondingly.
In other words, what I want is a dynamically changing combo box.
A. Setting RowSource Type to "Table/Query".
B. Setting RowSource Type to "User-Defined Function".
Both of these succeeded -- but only once. That is, when the form is opened, the city options are set correctly. However, they then remain fixed throughout the run of the form. Nothing happens when the country is changed.
I would think that this is not such an atypical situation, and that therefore there must be some solution. The only thing I can think of is to call my User-Defined Function explicitly with the necessary parameters whenever the city gets focus.
I would welcome suggestions of things I can try to get this working. Thank you.
Friendly advice... "abandon all hope ye who enter there." Microsoft Access combo-boxes are positively froggy. You can run into some funky timing-problems. As I recall the best event to trap for requerying the combo-box is when the user tabs into it. Set one flag, when the state value changes, to remember that you need to requery. OnEnter to the combo-box, do the requery.
manviln has the right idea ... In the country combobox have the After_Update method update the table for the cities combobox to make "visible" only those cities associated wit that country ... What you'll need is a table with the city, country ID, and a flag say "IsVisible" ... Then your initial state is all cities are invisible, and when you select a country you will set the visible flag for the associated country id and requery the combobox. The RowSource of the city combobox should be something like: "SELECT * FROM YourTableNameHere WHERE (IsVisible=True);"