• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Access Drop Down Boxes appearing if there is content

Hi,
I am trying to create a cascading combo boxes to filter data.  The data is being filtered by 2 or 3 levels of filtering.  I have setup the combo boxes as per the screen shot.  Screen shot    When the first category is selected from level 1, the level 2 combo appears and lists the options available in level 2 based on the level 1 selection.  This also works for level 3.

My fist issue is that there is not always a level 3 option.  How would I add an IF statement to count the number of items for the combo and only show the combo if it is not null.  I am controlling the combo boxes by the following code.  
Private Sub cboLevel1_AfterUpdate()
    Me.cboLevel2.RowSource = "SELECT L2ID, Description FROM CCatStructL2 WHERE L1ID = " & Me.cboLevel1
    Me.cboLevel2.Visible = True
    
End Sub

Private Sub cboLevel2_AfterUpdate()
    Me.cboLevel3.RowSource = "SELECT L3ID, Description FROM CCatStructL3 WHERE L2ID = " & Me.cboLevel2
    Me.cboLevel3.Visible = True
End Sub

Open in new window


After completing that task, I would like to have the selections from either 2 or 3 levels display the items in the catelogue.

I hope I have explained my problem clear enough.

Thanks
0
thomaswt
Asked:
thomaswt
  • 2
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this

Private Sub cboLevel2_AfterUpdate()
    Me.cboLevel3.RowSource = "SELECT L3ID, Description FROM CCatStructL3 WHERE L2ID = " & Me.cboLevel2
'    Me.cboLevel3.Visible = True

    Me.cboLevel3.Visible = Me.cboLevel3.listcount > 0
End Sub
0
 
Jeffrey CoachmanCommented:
Just a note here....

Sometimes it is needed to requery the dependent combobox

Ex: On the after update event of the Country combobox, would would need to requery the State/Province combobox.
0
 
Rey Obrero (Capricorn1)Commented:


when you are setting the Rowsource dynamically, it is REDUNDANT to do a requery.
0
 
Jeffrey CoachmanCommented:
didn't read through the entire post...

I can read quite well with out all caps ...btw...
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now