Cascading list boxes with and without macros

Some examples to be used directly
Forum rules
No question in this forum please
For any question related to a topic, create a new thread in the relevant section.

Cascading list boxes with and without macros

Postby Villeroy » Sat Jul 21, 2018 1:09 pm

The database
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   Expand viewCollapse view
Visible text   --> CityID
AUS Sidney   1
BRA Brasilia   --> 4
BRA Curitiba   --> 2
BRA Rio   --> 3
FRA Lyon   --> 10
FRA Paris   --> 9

You can easily select a city by its country by typing the country code, a space and then the city. Just for demonstration, I added an alternative multi-line list box. The drop-down type of list box in the grid can be dropped down with Alt+DownArrow. For easy searching in a drop-down box press Alt+Down, type the country code and then navigate with Up/Down to the wanted city in that area.

The 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)

Form "Voyages"
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"

Form "Voyages_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 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.
Cascading listboxes with and without macros plus grouped listbox.
(52.32 KiB) Downloaded 117 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Posts: 26141
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Return to Database Examples

Who is online

Users browsing this forum: No registered users and 0 guests