A list of 5 continents.
A list of countries which belong to continents with names and short codes (USA, MEX, JAP etc)
A list of cities which belong to countries.
A list of dummy data "Voyages" where we want to enter a city and some dummy info.
A filter table. Search this forum for "power filtering".
Form "Voyages_simple"
This expample works entirely without any subforms, filter forms etc. It demonstrates an alternative way how you can utilize a single list box to select a city by its country. I prefer this very simple solution when I can memorize the categories (country codes) and the list box entries are not too many. Some dozend of categories (countries) with 10 or 20 subcategories (cities) works just fine.
The list box is filled with entries like "FRA Paris" (country prefix and city name) in alphabetic order. The listbox writes the corresponding CityID to the underlying "Voyages" table.
Code: Select all
Visible text --> CityID
AUS Sidney 1
BRA Brasilia --> 4
BRA Curitiba --> 2
BRA Rio --> 3
FRA Lyon --> 10
FRA Paris --> 9The following forms require that you are familiar with the concept of "power filtering" and with form design in general (relations, forms/subforms, listboxes, the form navigator).
- Form hierarchy with refresh buttons (no macros)
First listbox writes the continent ID into row #0 of the filter table. The [-->] button refreshes the next filter form including its listbox.
Second listbox writes the country ID into row #1 of the filter table. The [-->] button refreshes the next filter form including its listbox.
Any previous form is saved automatically when the next form's [-->] button gets the focus.
Third listbox writes the city ID into row #2 of the filter table. The [Select] button refreshes the subform which enters a new Voyage record. Type some dummy info and hit Enter to save or click the save button. To see all entered Voyage info, cick the refresh button on the grid.
- Form hierarchy with hidden controls "AutoRefresh"
Same form hierarchy as "Voyages". All 4 refresh buttons have been replaced with hidden form controls named "AutoRefresh" and the form's "After Record Action" calls a very simple Python macro. The macro is designed to refresh any combo, listbox or form anywhere in the forms hierarchy after the current record has been updated, deleted, inserted. The "After Record Action" event is triggered whenever a modified record is saved or removed. The way how you save it does not matter. The macro searches for the hidden control "AutoRefresh" and reads its text value. The text value specifies the location of the refreshabel element by means of a relative URL. You can specify more than one element separated by semicolon. The object names are case-sensitive. No extra spaces or other characters allowed.
In order to use form "Voyages_AutoRefresh" you need to open AutoRefresh.odt which lets you install the Python source to the right location with one click. Then you should be able to run this form.
Topic on the AutoRefresh macro in the code snippets forum
My AutoRefresh.py is just a couple of code lines which do not require any customization in the code. All you need to set up is the AutoRefresh control for the configuration and choose the right form event. I think that "After Record Action" is the right one in most cases but you may find some use cases where another event does the right thing for you.
Form "Voyages_AutoRefresh2"
The second variant of the cascading AutoRefresh macro has all filter criteria in one form which is bound to row #3 of the filter table.
The hidden control "AutoRefresh" specifies the 2 dependent listboxes for country and city plus the subform. All 3 items are refreshed when you save the record. There is a save button which reacts on the Enter key (property "Default button"=yes), so you can pick a continent, hit Enter, pick a country, hit Enter, pick a city, hit Enter, enter voyage info, hit Enter.
Contrary to form "Voyages_AutoRefresh" which refreshes a whole filter form with one listbox, this one refreshes 2 dependent listboxes in the same form and a subform.