Can Cascading Combo boxes work without VBA?

Question

Is there a way to populate second and/or third  level drop down boxes, based on the choices of the Above/Previous boxes without writing VB Code?

Im trying to make the second and third drop down boxes "Auto Populate" based on the choice from the previous drop down box in Access 2007 without writing VB code, can anyone help?

Answer 1

My problem with VBA is that i've never used it.. I have copied and pasted your code into my db and also replaced the names (titles) with the ones in my db

 

And does it work? And if so, do you understand how it works?

There was a time for all of us when we'd never used VBA.  Something simple like this is probably a good thing to start with as it is very simple.  As you see my code includes comment lines, so it should be self explanatory what it's doing.  But really the VBA is secondary to the RowSource queries of the combo boxes; that's where the real work is done.

Answer 2

My problem with VBA is that i've never used it.. I have copied and pasted your code into my db and also replaced the names (titles) with the ones in my db

Answer 3

Is there a reason why VBA code is absolutely ruled out?  It's pretty simple to do.  You'll find an example in the file ComboDemo.zip in my public databases folder at:

https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Take a look at the option to 'drill down through a hierarchy'.  If you look at the RowSource properties of each of the three unbound combo boxes in the form header you'll see that those for district and parish each reference the combo box above in the hierarchy as a parameter.  You need to do the same.

The code in the AfterUpdate event procedures of the county and district combo boxes simply sets those below to Null and requeries them so that they show the restricted lists.  As you can see below the code is quite straightforward, but if code is completely ruled out in your case you just need to reproduce the same for each with macros rather than code.

In the AfterUpdate event procedures of all three the form is requeried to progressively restrict it to those rows which match the selections in the combo boxes.  You may want to do something different, however.

Private Sub cboGotoCounty_AfterUpdate()

    ' set district and parish combo boxes to Null

    ' and requery controls to show districts in

    ' selected county

     Me.cboGotoDistrict = Null

     Me.cboGotoDistrict.Requery

     Me.cboGotoParish = Null

     Me.cboGotoParish.Requery

     ' requery form to show locations

     ' in selected county

     Me.Requery

End Sub

Private Sub cboGotoDistrict_AfterUpdate()

    ' set parish combo boxwes to Null

    ' and requery control to show parishes in

    ' selected district

     Me.cboGotoParish = Null

     Me.cboGotoParish.Requery

     ' requery form to show locations

     ' in selected district

     Me.Requery

End Sub

Private Sub cboGotoParish_AfterUpdate()

     ' requery form to show locations

     ' in selected parish

     Me.Requery

End Sub

Answer 4

how would I do this?

Im trying to narrow a location?..Ex. if loc A is choosen in the first box, I only want a couple of choices in the second box......

I have created a table with all the locations possible

Answer 5

You should be able to use a parametrised Query with the full reference to the first ComboBox as the Parameter as the RowSource of the second ComboBox.

In the AfterUpdate Event of the first ComboBox, use Macro to requery the second ComboBox.