Cascading Combo Box Woes...

[sonosam]

I am new to Access and am having a problem. I have three cascading combo boxes in Access 2007 on a form. Two of them work perfectly, but the third one gives me an error "The expression is typed incorrectly, or it is too complex to be evaluated." I have tracked it down to the WHERE statement in the Query, but the statement is the exact same as the other Queries. Maybe I need different code to get a third cascade?

Tables are Assets, Category, Make and Model.
Queries are Make Query and Model Query.
Form is Asset Details.
First combo box is cboCategory that pulls from the Category table. It has an AfterUpdate event to requery the cboManufacturer combo box. Coded like this:

Private Sub cboCategory_AfterUpdate()
    Me.cboManufacturer.Requery
End Sub
 

Second combo box is cboManufacturer that uses the Make Query to look at cboCategory to determine what to show. SQL looks like:

SELECT Make.MakeID, Make.Make, Make.Category
FROM Make
WHERE (((Make.Category)=[Forms]![Asset Details]![Category]))
ORDER BY Make.Make;
 

This combo box works perfectly. It has an After Update event to update cboModel. Code:

Private Sub cboManufacturer_AfterUpdate()
    Me.cboModel.Requery
End Sub
 

Third combo box is cboModel that uses the Model Query to look at cboManufacturer to determine what to show. SQL looks like:

SELECT Model.ModelID, Model.Model, Model.Make
FROM Model
WHERE (((Model.Make)=[Forms]![Asset Details]![Manufacturer]))
ORDER BY Model.Model;
 

The WHERE statement above is the problem, but it looks like it should work. Maybe you just cant have two dependant combo boxes on one form?

I have attached a copy of a simplified version of the database. I am in the process of modifying a template and still weeding out parts I don't need. So far everything else works fine.

Any help is appreciated. Thanks.

Marc

[Seth Schrock]

There is no such thing as a Make Query. There is a Make Table query, but that is obviously not what you are trying to do. This is a Select query. You can have two dependent combo boxes on the same form.

There were a few mistakes in your database. First, I changed both cboManufacturer and cboModel to be bound to column 1 (control properties, data tab). Then I changed the cboModel's row source to be the query Model Query. I had to try a few things, but I think that is what you need.

[NeoPa]

I assume you are having trouble with Cascaded Form Filtering. Have a look at a working version in the link and see where and how yours differs.

[sonosam]

Thank you Seth. That fixed the problem. It's always something simple.