Cascading Combo Boxes

[cch012]

Hello,

I am trying to create a Cascading Combo Box for my Form in Access 2010. I ahve written the code:

Private Sub Combo11_AfterUpdate()

cboCombo13.RowSource = "Select TblAcc.SubFamily " & _
"FROM me.TblAcc " & _
"WHERE me.TblAcc.Family = '" & cboCombo13.Value & "' " & _
"ORDER BY me.TblAcc.SubFamily;"
End Sub

But keep getting the same "Run time error message 424: Object required"
And when I define the error it says "Identifier under cursor is not recognized" (the yellow arrow comes up and is pointing at the "ORDER BY" line.

I would like Combo Box 13 to give me the SubFamilies of the certain Families choosen from Combo Box 11. (Each Family has different SubFamilies). But if I ignore the error, Combo Box 11 lets me choose a Family and Combo Box 13 gives me options to choose any SubFamily in my DB.

Any help would be apreciated. If you need any clarification, just let me know.


Thank you!!

 

[boblarson]

The syntax me.TblAcc.SubFamily is not correct.

If you are using a multi-value field then you need this instead:

cboCombo13.RowSource = "Select TblAcc.SubFamily.Value " & _
"FROM me.TblAcc " & _
"WHERE me.TblAcc.Family = '" & cboCombo13.Value & "' " & _
"ORDER BY TblAcc.SubFamily.Value;"
End Sub

 

[cch012]

Bob,

The Combo Boxes only have one feild in them. And when I put in your code, it gives me the same error and the same definition for the error.

 

[boblarson]

Bob,

The Combo Boxes only have one feild in them. And when I put in your code, it gives me the same error and the same definition for the error.

Okay, so first off, you should rename your controls to something meaningful instead of Combo13, etc. BEFORE adding any code as it will then have to be moved to the new name/event. But I would rename them ASAP as it also helps to understand just by looking at the name what it is. For example, if I have a combo box to select a name it would be cboName.

Next, without really seeing your database it is a real shot in the dark as to what you have. Can you upload a copy of the database with some fake data in it? If you do, make sure to run COMPACT AND REPAIR first and then ZIP the file by right clicking on it and selecting SEND TO > COMPRESSED FOLDER.

 

[cch012]

Bob,

Okay, I have renamed the Combo Boxes: 11= JobFamily and 13=SubFamily. I am not able to upload a fake data base at the moment.

 

[cch012]

Okay, now that I changed the Combo Box names, I am getting another error. After I select from cboJobFamily and try to choose from cboSubFamily the error is saying that it can not find my database "Could not find file C:\Users\Documents\me.mdb".

 

[cch012]

I have attached the DB that you requested. I also need the cboSubFamily to feed into the cboSkillSet.


Thank you for your help!

 

[boblarson]

I don't have 2010 at work but will try to remember to look when I'm at home later tonight, unless someone else who has 2010 cares to look and respond, which is quite alright.

 

[John Big Booty]

Your DB only has one table I think the first thing you are going to need to do is normalise your table structure, a tutorial may be helpful in this task.

As it's stands I have no idea what the relationship between your three combos is or how you would filter them down

 

[cch012]

So, what your saying is that I can not filter by Job Family to get the corresponding SubFamily to get the corresponding Skill Set with one table?

 

[billmeye]

I don't think you need to redo the SQL for each cbo rowsource, just requery as I have in re-spinning your example.

 

[cch012]

I'm a little confused on what you mean Bill. It works out fine when I choose "Administrative Services" on the first drop down, but the Sub Family is not changing when I choos "Finance".

The end result would be: Choose a Family (Finance)> Choose a SubFamily (Accountant) > Choose a Skill Set (Education and Experience)> In the bottom box the Education and Experience for an Accountant under the Finance Family would appear.

 

[billmeye]

I'm not sure why it did not work for you. When you select the Family, it filters the cbosubFamily and requeries the recordset to filter only what is selected. Same is true for cbosubFamily and cboSkillSet. Sorry it did not work for you. Did you download my example and try it?

 

[cch012]

It doesn't hold true only when I try and change the Family from Finance to Admin Services. Is there any other advise that you can give me?

 

[billmeye]

I'm just wondering if you downloaded my example, it still shows 0 views.

 

[cch012]

Yes, I have. I opened it and it is currently up on my screen.

 

[cch012]

So, I was def copying in the code wrong. Thank you so much for your help!! You have been marvolouse!