Cascading list box not working

English
#1

I have two list boxes, where the selection of an option from the first determines the options that are displayed on the second. The example I was given uses a filter table, but I’m trying to simplify and not use one. Each list box references it own table to populate its options. The SQL statement used in the second list box Data tab that is used to populate it is the following which doesn’t work. What am I doing wrong?

“SELECT “Ex_subcat_options”, “Ex_subcat_key” FROM “Ex_Subcat_Options_Table” WHERE “Ex_subcat_ID” = (SELECT “Ex_opt_key” FROM “Ex_Cat_Options_Table”)

The first (Ex_Cat_Options_Table) table has the following fields:
• EX_opt_key (the primary key)
• Ex-categories (an alpha-numeric field)
The second (Ex_Subcat_Options_Table) table has the following fields::
• Ex_subcat_key (integer)
• Ex_subcat_options (alpha)
• Ex_subcat_ID (integer)

#2

With very limited info given here, these are my assumptions -

  • Without the use of a macro
  • Ex_Cat_Options_Table (tbl1) is content of main form
  • Ex_opt_key (pk1) is the auto-increment primary key for tbl1
  • Ex_Subcat_Options_Table (tbl2) is content of sub form
  • tbl2 has no auto_increment primary key, but should have one (Ex_subcat_key, pk2)
  • Ex_subcat_ID (use like “ex_opt_key”, fk1) is the foreign key related to tbl1.pk1
    (Uploading a stripped down sample of your odb showing the issue proves most timely in gaining a solution as it eliminates the need for unneccessary assumptions.)
    Without using a macro, a third table will be necessary. Name like tbl_Filter - with 2 integer fields
  • “ID” INTEGER NOT NULL PRIMARY KEY
  • “filterID” INTEGER
    MainForm data content should be set to tbl_Filter
    Listbox properties located in mainform
  • Data field = filterID
  • Sql = SELECT Ex_categories, pk1 FROM tbl1 - add necessarry quotes - no WHERE clause
  • Bound column = 1
    SubForm data content set to tbl2, link slave fk1 to master filterID
    Listbox properties located in subform
  • Data field = pk2
  • Sql = SELECT Ex_subcat_options, pk2 FROM tbl2 - add necessary quotes.
  • Place a push button in the mainform with action Refresh form. Push to update second listbox.
    These are the minimum requirements to cascade a listbox based on another.
#3

Wow Sky, I appreciate the effort to explain this with what I provided.
Not knowing how to upload my odb tables… (Can I upload the tables or must I upload the odb that includes my form, etc?)
Here is the cleaned up table organization (the dashes are spaces):
tbl1 fields:--------------------------tbl2 fields:
• ex_cat_key (int- auto)--------• ex_subcat_key (int- auto)
• ex_cat_options (char)------- • ex_subcat_options (char)
---------------------------------------• ex_subcat_ID (int, related to ex_cat_key)
The category and subcategory list boxes reside in the same form. There is no subform. Unless, by definition, when fields refer to two different tables you have a form an subform?
tbl2 contains all the subcategories and is organized/filtered by the ex_opt_id.
For example: tbl1 contains the states as categories like 1, Ohio; 2, Kentucky and so forth. tbl2 contains the large cities in these states Cincinnati, 1; Columbus, 1: Louisville, 2, Lexington 2.
tbl2 does have an autoincrementing primary key. I thought every table required one.
As an aside, I don’t understand why SQL in the subcat field with an appropriate WHERE clause won’t work in relating the category and subcategory options?
Regarding the filter table, if I understand you correctly:
• It contains an autoincrementing integer primay key (filter_key)
• It also contains an ID (int) field (filter_ID)
I don’t understand what you mean by “…NOT NULL PRIMARY KEY”? Wouldn’t a primary key always be 0, 1, 2…?
I’m all about simple. You provide a simpler solution that I don’t fully understand - …replace the mainform listbox with a table control. If I understand this, I would replace the Data content for the category listbox from tbl_filter to tbl1 and enter the master and slave stuff you show?
Thanks, Erik

#8

One should not make assumptions on auto-generated keys. They may start at 1 or 0, but can have any offsets. (The example also told integer). I’ve also seen user-ids like @Wanderer used to have separate filters for different users.
.
For filter-tables, wich usually have not many rows, we can set the id ourself, avoiding autoincrement, especially as we will usually use a query WHERE ID=1 or similiar later.
.
As you will often not add rows to filter-tables later auto-increment is not necessary.
.
NOT NULL enforces a value in the ID, as some databases allow NULL (empty) id. This will also prevent you from adding a row by accident (unless you provide an ID also).

#4

I have decided to send you a working sample of cascading listboxes (Included in later post below). Use it as a template or workbench for your own odb. There are two forms with button type differences. I prefer the icon navigation bar over the normal push button since one is able to execute many built in actions from the same control. One can hide or show several different types of action groups in the bar by editing the “Show/Hide” properties of the nav bar control. For example, when “Acting on a record” is set to Show, the save, undo, delete, refresh, and control refresh icons will display on the nav bar. No need to assign actions or macros, they are preset with the bar. You may need to adjust the width of the nav bar control depending on how many “groups” are shown. (1 group is 1.25" wide with small size)

  • To add the navigation bar control to the form, in the edit form’s menu bar select - Form / Navigation bar. (Do not confuse this navigation bar Control with the form navigation bar at the foot of a form. They are independant of eachother.)
#5

I appreciate the examples. I do not see, though, that the first example (of lenses) that the 2nd drop down is based on the selection of the first - that they are cascading.
It appears they both are identical and independent, other than each update their own price. Or have I missed something? The macro version shows this but I’m trying to avoid macros due to the problems I’m having getting them to work.
Your examples also bear the question, how did you incorporate a functioning part of the footer menu bar in your form - Magic?

#6

The Main form’s navigation bar (footer bar) is enabled in the Form Properties / Data / Navigation bar = Yes. If you want it to also show when a subform has focus, then [sub]Form Properties / Data / Navigation bar = Parent Form.

If you are referring to the “miniature” navigation bar, that is the navigation bar Control which is explained in my previous post!

I went back and simplified the sample. Here is the updated working version for macro and non-macro cascading listboxes… (real listboxes) I completely revamped the base tables. Notice the two necessary temp Tables.
CascListBoxDemo.odb (25.3 KB)

#7

Sky,
I reviewed what you said about adding a navigation bar to a form and now have done it. Very cool.
I await your updated cascading list boxes - CascadeListboxes.odb.
Also, when you have time, if you could please respond to the several questions I had in my previous “post” starting “Wow Sky, I appreciate the effort…”:
• My category and subcategory list boxes reside in the same form, I believe. There is no subform. Unless, by definition, when two fields refer to two different tables you have a form and subform?
• My tables all have primary keys that are auto-incrementing – what do you mean by “…NOT NULL PRIMARY KEY”?
• You provide a “simpler solution” in your previous post that I don’t fully understand - …replace the mainform listbox with a table control. If I understand this, I would replace the Data content for the category (parent) listbox from tbl_filter to tbl1 and enter the master and slave stuff you show?
Thanks, Erik

#9

This is the hsqldb database script file language to create an integer type primary key field named “ID” with “Entry required” property set to Yes in a table. It is not auto-increment, however. Sometimes out of habit, I fall back into that style without regard for the many who may not understand it!. The previous post was updated to include a working demo of both macro and non-macro versions of cascading listboxes.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.