Base form: Filter list box entries based on what is selected in another list box

[hiigaran]

I have a table called Vehicles. It contains columns “Registration” and “Type”.

In a form, I have created two list boxes, each containing the entries from “Registration” and “Type”.

Currently, if I select an entry for the “Type” list box, it is possible for me to select any entry in the “Registration” list box, even if it does not match the correct type, as it just lists all contents from the column in the table.

Is it possible to select an entry in “Type”, which then causes the entries in the “Registration” list box to only show entries that match the selected Type?

[Ratslinger]

Hello,
This can be done either using macros or not. These posts have samples and some links to other answers with probably more samples. This question has been asked many times and these are just a few of the many answers out there:

List Box results from another list box

Hello, Your question lacks many details to be able to give an answer specific to the results you want but the attached sample should give you a starting point to proceed. The sample deals with people in companies and their position. On the single form, The Select Person list box allows selection of an item in the People table. Then, using the button and based upon the selected item, the table control displays the record AND the second list box now contains all People who have the same posit…

Can I limit a list box based on previous list box data

Hello, There are a variety of methods to accomplish what you want. Some are more difficult that others and require macros to utilize. Best to avoid macros when possible. Using list boxes is fairly easy. You can get other records based upon a selection by using table filtering. For an explanation on table filtering see this post → Filter/Search with Forms (leveraging SubForms) Now, using a list box & table filter you can select a specific manufacturer and display the models in a subform t…

[hiigaran]

Apologies for the late reply. Ended up losing my password immediately after signing up. I’ve had a look at a couple of those links. Will leave it as a low priority fix for my databases as the solutions look more complicated than anticipated. Had an attempt at dissecting one of the example files in one of those links, but I think I need to read up a little more on some documentation before fully understanding how and why I set things up the way they are.

[Villeroy]

countries_cities.odb (65.6 KB)

Demonstrating 4 possible work-arounds and solutions filtering countries by continents and cities by countries. Turn on the form navigator while inspecting the forms.

1. A listbox of all cities can be filled with an additional country prefix like “FRA Paris”, so the user types "FRA " in order to jump into the French listbox entries. Hit Alt+Down arrow to drop down the listbox and then type a country prefix.
2. Cascading subforms with table controls instead of listboxes. This is how subforms are intended to be used. Forms allow to select a distinct record for editing or viewing, subforms are filtered by their parents.
   Contrary to subforms, listboxes are not designed to select and edit records. Listboxes write a distinct value into a field.
   In order to find an entry by name you need to scroll, filter or use the find tool which makes this solution cumbersome with large record sets. This works fine for the continents but not if you enter hundreds of countries, regions, sub-states.
3. A set of cascading listboxes for continents → countries → cities without using any macro code. The selected continent is saved in the “Filter” table where ID=0. When you click the blue [–>] button (which belongs to another form), you save the continent because the button moves the focus from the red to the blue form before it refreshes the blue form with its country listbox.
   Likewise, the selected country in the blue form is saved in the “Filter” table where ID=1. When you click the green [–>] button (which belongs to another form), you save the country because the button moves the focus from the blue to the green form before it refreshes the green form with its city listbox. In the green form you enter data for a new voyage to the selected city found by cascading search for continent and country.
4. Similar to 3 but very different. The [–>] buttons are hidden, they belong to the preceeding listboxes and they store the form’s record (continent or country respectively) when you hit Enter. Instead of hitting Enter you can also tab through the listboxes and save the previous form by moving the focus to the nex form.
   After the record has been stored, an event triggered auto-refresh macro refreshes the next form with its listbox. The Python macro is embedded in the database document. It is most useful when moved to the user profile because it can refresh any amount of forms, subforms, listboxes and combo boxes within any hierarchy of forms. The setup is done by means of “hidden controls”. You never need to edit that macro.