On a form how do I limit the content of a List or ComboBox dependent on the value selected in another field?
For example, I have a table with two fields. The first is the Province or State Name and the second is the Country Name.
On my form, I have a field to select the Country. I want the ProvState field to be populated with only the Provinces or States of the selected Country.
So, how do I do this?
Is there a way to reference the forms field in a SQL query’s Where clause?
You will need a macro for this.
An example is described in English Base Guide, Hierachical listboxes. There are more solutions in German Base Handbuch.
It is a little bit problematic. If the code for the list box is like SELECT "Name", "ID" FROM "Table" there is no problem to add WHERE … to the code. But if there is code in WHERE-clause you have to split and merge to get the content for the second list box and will get problems to get back to the content before.
Thank you
Do you know of anywhere I could find an example?
@p.schippers
There is an example in my answer here → Can I limit a list box based on previous list box data - #2 by Ratslinger
.
Sample is ProgressiveListboxes.odb in edit section of answer.
A combo box is nothing more than a text box with auto-complete functionality. You can enter arbitrary text into a combo box.
Each province belongs to exactly one country, so this is a one-to-many relation. Since states of a country are a limited item set, you want a list box rather than a combo box. A list box has 2 columns. The first column (index 0) displays some text, the second column (“bound field”, index 1) has another table’s primary key. When selecting a list box item by text, you select another table’s primary key into some foreign key field.
Only in German, is part of the German Base Handbuch:
Beispiel_hierarchische_Listenfelder_Hsqldb.odb (40.7 KB)
A collection of examples: Apache OpenOffice Community Forum - Database Examples - (View forum) including cascading listboxes with and without macros.