cascading combo boxes and if else statements

[jd9913]

Hi,

I've been all over the internet trying to find an answer to this question and am hoping someone here has one.

I have 2 combo boxes, cboCatID and cboSubCatID

The values are based off of queries, qryCategory and qrySubCategory which are pulling values from underlying tables tblCategory and tblSubCategory.

I can create the boxes so that they are cascading, but my problem is a little more involved.

In the Category table, I have multiple categories, only some of which have a subcategory. The ones that have a subcategory, have multiple subcategories

What I want to do in my form is for the user to choose a category from cboCatID. I want access to then look at that choice and if it is a category that has a subcategory to populate the cboSubCatID with only the subcategories relevant to that Category. If the user chooses a Category with no subcategories, then I want the subcategory box to show "NONE" and have the user unable to change it, unless they change cboCatID.

I have a different set of cascading combo boxes based off of queries elsewhere in the DB and they work perfectly, but I have no idea how to alter the query or the code to get the modified Category/subcategoy boxes to work. I don't even know how to ask the question to even get the answer I'm looking for. I'm just learning VBA and some of the advanced functions of Access so please make the explanations as simple as possible.

Thanks in advance for any help.

 

[John Big Booty]

Welcome to the forum.

The easy way would simply be to add a dummy record to your Sub Categories table with the category "None" and select where appropriate.

Alternately you could dynamically change the Row Source of your cascaded Combo to reflect Sub categories, this will still require a dummy record but would not require it to be assigned as a sub category. Have a look at the Form's On Current event and On Change event of the master combo in the attached sample.

 

[Severin]

Private Sub Combo1_AfterUpdate()
If DCount("*", "[YourQuery]") = 0 Then
Me.Combo2.RowSource = "NONE"
Me.Combo2.RowSourceType = "Value list"
Else
Me.Combo2.RowSource = "YourQuery"
Me.Combo2.RowSourceType = "Table/Query"
End If
End Sub

 

[Severin]

...sorry you wanted NONE to be showed in the field...

Private Sub Combo1_AfterUpdate()
If DCount("*", "[YourQuery]") = 0 Then
Me.Combo2.RowSource = "NONE"
Me.Combo2.RowSourceType = "Value list"
Me.Combo2 = "NONE"
Else
Me.Combo2.RowSource = "YourQuery"
Me.Combo2.RowSourceType = "Table/Query"
Me.Combo2 = Null
End If
End Sub

 

[jd9913]

Thank you so much for your replies. I'll try both of these solutions and see which one works the best within my database. Thanks for the sample DB. it is extremely helpful.

I was actually looking for the Code, but I didn't think about approaching the problem in either of these ways; which is probably why I was having difficulty. I'll let you know when I have a solution or if I have additional problems. I hope to get to this tomorrow, life has gotten in the way today.

Thanks again.
Jennifer