I am new to SQL and am getting the following error when I run my form with a list or combo box with the SQL shown in the error message below.
• Is there a length limitation to table names?
• Have I structured my SQL wrong or omitted something?
Here is the error message and my SQL: Table not found in statement [SELECT ““Ex_subcat_options””, “Ex_subcat_key”" FROM ““Ex_Subcat_Options_Table””]
This SQL also includes a WHERE clause, but was not mentioned in the error message.
Thanks
A “SELECT” statement should not be in the “WHERE” clause… that is a malformed sql. It appears you may be trying to update a combobox of tableB based on a selection from another listbox of tableA in the same form. I don’t think this can be done, especially without macros! There should be a linked sub form based on tableB where the combobox should be included.
I see to many quotation marks around the table-name.
.
Also check the spelling. Some databases need precise Upper/lowercase letters.
PS: For better reading enclose code in backticks: `
Sky,
You are correct. I am trying to update the combo box based on the selection of the parent list box. All the examples provided by the group that are close to what I’m trying to do have been for multiple cascading boxes and I really don’t understand all this, especially why I need a filter table when I am only cascading once. Also, I don’t understand why my child drop-down needs to be in a sub form? Thanks
I hear you my friend. I’ve been there and have had the same questions… A filter table is not mandatory, but without macros it is the simplest way to achieve what you are wanting. Else, although a little easier to create, the alternative will require some minor comprimises on which control types are used to acheive something which may or may not be “close” to what you expect. I will put a little demo together based on the sample you provided earlier that should help answer your questions… just need a little time to build it, then I will turn around and update this post with the demo file!
DemoExpensAcct.odb (29.3 KB)
1 Like
Sky,
Thank you.
Erik
Sky,
Sorry for the delay.
I didn’t realize you had updated the files - I didn’t receive any notification.
I will digest and attempt to implement your “demo”.
Thanks,
Erik
Sky,
Sorry for the long delay.
I have reviewed, in depth, both the revised control table and filter table solutions you provided. Many thanks. I have never used a control table, so that was an education. I do have a few questions, though?:
• Do I have to use control tables, or can I substitute them with combo boxes?
• Is a subform required for cascading drop downs or control tables?
• And If a subform is not required (for cascading “drop downs”), given that I would like both the category and subcategory selections to update the same table, is there a way to do this (without the use of a query)? Alternatively, (even thought it is not a good practice) is there a way to write the value from one table to another? I could only find answers that said use a query instead.
Thanks, Erik.
This is a many-to-many relation. Each category is associated with zero, one or many sub-categories and vice versa. The expense table maps which (sub-) categories belong to each other.
My understanding of categories and subcategories is different. IMHO, it should be a one-to-many relation where each subcategory belongs to one category and each expense belongs to one subcategory.
"Buy items of subcategory “fruit” in category “groceries”.
Villeroy,
Your second sentence is correct. As I stated in my earlier question, is it possible to have a category list box that controls the contents of a subcategory list box (cascading list boxes), both update the same table? Erik
Search for “cascading list box”. It’s not a built-in feature, therefore complicated.