Cascading drop-down fields issues

[nebergall]

I’m really struggling with this. I have been given several examples to do cascading drop-downs that are over my head using filter tables and queries. I do not understand them even after studying them for some time. There must be an easier way. So I have created a form with cascading drop-downs that should work but doesn’t using SQL.
Attached is the form with two drop-down fields and the related tables. I would like the two drop-down fields to be cascading (when I select a value from the 1st drop-down, I would like to show only the sub-values in the 2nd drop-down that relates to the selected value in the 1st). I have two problems:

1. Why am I getting the error “The data content could not be loaded - There exists no table “Income Table”” when I try to run the form. (I had an income table and income fields in my form, but removed them for this example).
2. Why isn’t the SQL with WHERE clause in the 2nd (lower) drop-down in the Data tab displaying only the related sub-values of the selected value in the 1st drop-down - its showing all sub-values for all the values in the 1st drop-down.
   Expense Accounting.odb (16.6 KB)

[RobertG]

Open the form navigator:

There is still a form “Income_Form”, which is connected to “Income_Table”.
.
Second question: SQL will show all values of the table. You have choosen a combo-box, which could be used for input new values and couldn’t be used for connecting to a second (foreign key) field.

The listboxes would never work in a cascading way without something like a filter table or a macro construction. You won’t get the value of the first listbox for setting the possible values in the second listbox.

[nebergall]

Robert
You are correct. I forgot to remove the second form even though it was not visible to the “user”.
I have attached the updated version of the two drop-downs without the income form.
Regarding the 2nd question, about using SQL in the 2nd drop-down, the SQL for List Contents is: 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 end result I desire is a list box for the first drop-down and a combo box for the 2nd drop-down so I can add items to it. I’d like the selected values to be saved to my Expense_table table.
My question is, why won’t the SQL (native) code in the 2nd dorp-down work using the WHERE clause?
Thanks
Expense Accounting.odb (16.6 KB)

[skyandrews]

• 1. Without using macros, you will need another table like “filterTable” with two fields like “ID” (Integer, Primary Key) and “filterID” (Integer).
• 2. The Expense Table needs to be a “join” type table. (having two pk fields like “categ_id” and “subcat_id”) To do this, deselect the current primary key. Save the table! Select both desired pk fields (ctrl+select) so both are highlighted, then mark them as Primary Key. Both row markers should show the key icons! Save and close the table!
• 3. In the relationships tool, create relations from the two pk fields in ExpenseTable to their related fk in each of the two related tables. When prompted select the Update Cascade option for each created relation. Save and close relationships!
• 3. The Accounting form must have a main form (based on the filterTable for categories) and a sub form (based on the ExpenseTable for subcategories) The sub form slave categ_id should be linked to the master filterID.
• 4. There will also need to be a push button (action = Refreh form) or a navigation bar control (NOT the form navigation bar at foot) placed in the main form to “refresh” the second listbox (in sub form) after selection in first listbox (in main form).
• 5. The main form listbox will have data field = “FilterTable”.“filterID” and the sql content to query the category table. The sub form listbox will have data field = “ExpenseTable”.“subcat_id” and the sql content to query the subcategory table.