Cascading Combo Boxes not working at all, vague syntax error on use.

[Alhassani]

I'm having trouble getting cascading combo boxes to work. I think it is due to the table set up. I'm sure it could be consolidated a bit, but I'm unsure of how to go about doing that. Here's the set-up:



tblCategories: Column 1 is the ID, and column 2 is the 9 Categories to chose from.
tblFields: Column 1 is an ID, column 2 is the Categories which link up to column 3 which is the fields.

I did it like this because I don't know another way to make the categories line up correctly to the fields, while also only giving the 9 options to choose from in the categories box.

cmbCat: Lists the 9 categories (correclty thus far). After Update code as follows.

Private Sub cmbCat_AfterUpdate ()
Me.cmbFields.RowSource = "SELECT Fields FROM" & " tblFields WHERE Category = " Me.cmbCat"
me.cmbFields = Me.cmbFields.Itemdata(0)
End Sub

cmbFields: is supposed to take the 57 fields and narrow by categories (obviously). Displays nothing.

When I make a selection in cmbCat, I get a Compile error: syntax error message and enter the debugger. The RowSource line is highlighted in all red with no indiciation of specifically which part is incorrect.
I know I am pretty far off base here. But, even after reading through forum posts here, watching the datapig videos, using the microsoft help pages, and searching other forums I can't figure out where exactly this is going wrong.

[orange]

see this video tutorial
Good luck

[Alhassani]

Orange,

Thanks for the link, but as stated in the original post I've already reviewed that video. I've built these combo boxes first using that video as a guide, then as a vba statement, and I just rebuilt again based on the video in case I missed something. Also, in Access 2010 I am unable to add the "Where" portion of the query because that line is not present.

I'm thinking more and more that this is in how the tables are set up to provide the selections, but can't figure out how to fix them.

[orange]

Can you zip and post your database?
This isn't syntacticly correct

Private Sub cmbCat_AfterUpdate ()
Me.cmbFields.RowSource = "SELECT Fields FROM" & " tblFields WHERE Category = " Me.cmbCat"
me.cmbFields = Me.cmbFields.Itemdata(0)
End Sub

Please show your true SQL.
Also, please show the table structure.

[Alhassani]

Zipped database is attached. Due to employee information I had to remove basically all of the records, I left a few in there for demonstration purposes. This is still very much a work in progress and nowhere close to being complete.

[orange]

It would be helpful to readers if you could give an overview of What the database is about in plain English, and on which form your cascading combo issue occurs. I don't deal with macros so am not able to provide much assistance.

Your

tblCategories: Column 1 is the ID, and column 2 is the 9 Categories to chose from.

is a little misleading. You have a table with 2 columns. The table contains 9 records.

You should adopt a naming convention that does not allow special characters (or spaces) in table and object names.
eg: PCH: Sponsor SSN
I agree that your table structure may need some work.

Good luck with your project.

[Alhassani]

The database is intended to replace the current system of excel workbooks I use to track quality statistics. The workbook compliation pulls from anywhere between 10 and 35 workbooks based on the report that I am running. I am trying to consolidate all of this into a single access database. I am currently in the first step of setting up an input form for the quality data on the project. There are numerous queries and reports to generate after this initial stage has been completed. The boxes in question are on the "RebSubFrm" form. It is the last one on the list. QualityDb.zip

The attached zip is the same file with the original code worked back into it. I prefer not to work with macros as well, but the video guide from datapig pointed me in that direction.

[orange]

DataPig doesn't deal with macros, the video uses vba code.
What happened to tblCategories?
Fields is a reserved word in Access and may cause you issues.

There's info on comboboxes here.