Hide a cascading combobox if there are no values to choose from

[fire2ice]

The title pretty much says it all. I have a series of cascading comboboxes. However, if combobox 2 has no values to choose from, I would like to turn Visible = False so that the users don't have to click on it to find that there are no choices.

Essentially, I have cboCategory and cboSubcategory. Not every category has a subcategory. Thus, that combobox will sometimes result in nothing to select. If it has nothing in it, I want it out of sight and out of mind.

Your assistance in this matter is greatly appreciated as always. This forum has been my sole educational tool in VBA and I've learned so much. Keep up the great work all that contribute.

 

[WayneRyan]

Fire,

I'm assuming that you're using the AfterUpdate event of cboCategory to set the
RowSource for cboSubcategory.

Why not just use the DCount function to check if there are any records.

If DCount("[subCategory]", "SubCategoryTable", "[Category] = " & Me.cboCategory) = 0 Then
   Me.subCategory.Enabled = False
   Me.subCategory.Visible = False
Else
   Me.subCategory.RowSource = "..."
   Me.subCategory.Requery
   Me.subCategory.Enabled = True
   Me.subCategory.Visible = True
End If

Wayne

 

[fire2ice]

Good idea. I'm not sure why I didnt think of that. Is Friday a good excuse?

Bad execution. I'm getting a syntax error stating that I have a missing operator in the query expression. I'll have to take a look at it on Monday as I'm free for the weekend. Yay.

Thanks for rattling my brain and letting one of the marbles fall back in place.

 

[WayneRyan]

Fire,

If your criteria for the DCount function is a string, you need to add the
single-quotes around it.

Have a good weekend,
Wayne

 

[fire2ice]

Problem solved.

I figured the syntax out on the drive home (you know how it goes when something is stuck in your head). You confirmed it. Thanks. However, my DCount value ended up always being zero. Therefor, I had to set up an external query to base the DCount instead of building the WHERE statement inside the DCount.

Works like a charm now. Thanks for your guidance.