Access Multiple Level Cascading Combo boxes

Question

I have a form that has 3 different combo boxes. Area, Class, Modification. When I chose a selection from the Area combo box, I want it to automatically update the Class combo box. Based on the selection from the class combo box I need it to limit the options in the Modification combo box. 

This looks dumb and complicated because Area and Class describe the same thing, but with different codes (I'm working with two groups that need to be able to use their own codes). People that will be using the database only know the codes related to Area. However the Modification combo box is dependent on the codes used in Class. So I need to find a way to translate the Area codes into Class codes, so that it can limit the combo box for Modification. (I hope I am explaining this clearly enough)

I currently have VBA that automatically updates the Class box based on Area. 

Private Sub cmbArea_Change()

Me.cmbClass.Value = Me.cmbArea.Column(2)

End Sub

Under the value list for Modifications I have 

WHERE (((ModificationCodes.Class)=[Forms]![DataEntry]![cmbClass]))

And then I have another code under "On Change" that should requery Modifications if Class is changed

Private Sub cmbClass_Change()

DoCmd.Requery (cmbModification)

End Sub

The problem that I run into is that when I select the Area the first time it works great, and it limits the Modifications list correctly. But if I try to change the Area code again it doesn't update the list (I keep the limited list from the previous selection). So I think the requery vba isn't working correctly for some reason. I would really appreciate some ideas if there is a better way of doing this, or how to fix the problem that I have. Thanks!

Answer 1

As the relationship type from Classes to Areas is one-to-many you can keep the model down to three tables by the insertion of a Class foreign key into Areas, referencing the primary key of Classes.  The model would thus, in broad outline, comprise tables like this:

Classes

….Class  (PK)

Areas

….Area  (PK)

….Class (FK)

ModificationCodes

….Code  (PK)

….Class  (FK)

The tables can of course include further non-key columns.  Note that when correlated controls are used, the use of 'natural' keys, as above, has significant benefits, particularly in forms in continuous forms view.

As your users will be selecting an area, not a class, no correlation is required between the first two combo boxes, cboClass and cboArea, so their RowSource properties would be:

SELECT Class

FROM Classes

ORDER BY Class;

SELECT Area,

FROM Areas

ORDER BY Area;

The RowSource property for the cboModificationCode combo box would need to restrict the results to those rows from ModificationCodes where the Class foreign key column is the value determined by the selected area:

SELECT Code

FROM ModificationCodes

WHERE Class = Form!cboClass

ORDER BY Code;

In the cboArea control's AfterUpdate event procedure the cboClass control's value would be set to the Class for the selected area, and the cboModificationCode control then requeried:

Dim strCriteria As String

strCriteria = "Area = """ & Me.ActiveControl & """"

Me.cboClass = DLookup("Class", "Areas", strCriteria)

Me.cboModificationCode.Requery

Me.cboModificationCode = NULL

I've assumed in the above that Area is of short text data type.

As the user will not need to select a value in the cboClass combo box, that control can have its Enabled value set to False (No) or the control can even be hidden by setting its Visible property to False (No).

Answer 2

One problem was with the way the cboAreaHabClass and cboNHabClass controls are set up.  Each has a RowSource property which returns a single column, so in each case the ColumnCount property should be 1, and the BoundColumn property should be 1.  The Columnwidths property should be left empty in each case.

The biggest problem was that, when calling the DLookup function, you'd named the Column 'Class' in the code when it should have been 'PNWClass'

I've posted an amended copy of your file as Camila.accdb to my public databases folder at:

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

As far as I can see the form is operating correctly in the amended file.

Answer 3

Thank you so much for your reply. I am still working on testing different things out, but would the description above work if it wasnt a one-to-one relationship, but rather one-to-many? Different "Areas" can be classified under the same "Class". That is also in part why I'm using all the different tables.

Answer 4

So I need to find a way to translate the Area codes into Class codes, so that it can limit the combo box for Modification.

I didn't really address this issue in my last reply.  The appropriate solution would be have an adjacency list table, AreaClasses say, with two columns such as AreaID and ClassID each referencing the primary key of the Areas and Classes tables.  Assuming that the relationship type between Areas and Classes is one-to-one and that each table has an equal number of rows, when a user selects an area code from a combo box whose BoundColumn property points to an AreaID column, then a Classes combo box, whose BoundColumn points to a ClassID column, would be updated with code like the following in the area combo box's AfterUpdate event procedure:

    Dim strCriteria As String

    strCriteria = "AreaID = " & Me.ActiveControl

    Me.cboClass = DLookup("ClassesID", "Classes", strCriteria)

    Me.cboModification.Requery

    Me.cboModification = Null

You can of course cater for a value being selected in the class combo box with similar code in its AfterUpdate event procedure.  It would of course be even simpler if the Areas and Classes tables where coalesced into a single table, in which both ClassID and AreaID are candidate keys, i.e indexed uniquely (no duplicates).

Answer 5

When correlating combo box controls the AfterUpdate event procedure should be used, not the Change event procedure, to requery all combo boxes below the current one in the hierarchy.  The Change event occurs when each individual character in a control is changed, not when the complete value of the control is changed.  The latter causes the AfterUpdate event to occur.

Also, when calling the Requery method of the DoCmd object the Control Name argument is a string expression, so should be wrapped in quotes characters when using a literal string.  I'd normally the control's Requery method.  The following is an example of the code in the AfterUpdate event procedures of the two upper correlated combo boxes in a three level hierarchy,:

Private Sub cboCountry_AfterUpdate()

    ' if Country is updated then erase current values

    ' for Region and City and requery combo boxes

    ' to show regions of selected country and empty list

    ' of cities (pending selection of a region)

    Me!cboRegion = Null

    If Not Nz(Me!cboCity) = 0 Then

        Me!cboCity = Null

    End If

    Me.cboRegion.Requery

    Me.cboCity.Requery

End Sub

Private Sub cboRegion_AfterUpdate()

    ' if Region is updated then erase current values

    ' for City combo box.

    ' requery city combo box to restrict list

    ' to cities from selected region

    If Not Nz(Me!cboCity) = 0 Then

        Me!cboCity = Null

    End If

    Me!cboCity.Requery

End Sub

The RowSource properties of the two lower combo boxes are:

SELECT RegionID, Region

FROM Regions

WHERE CountryID = Form!cboCountry

ORDER BY Regions.Region;

SELECT CityID, City

FROM Cities

WHERE RegionID = Form!cboRegion

ORDER BYCity;

Note the use of the Form property to return a reference to the current form, rather than a fully qualified reference to the form as a member of the Forms collection.  This is particularly relevant where the controls are in a subform, which is not a member of the Forms collection.

You'll find the above examples in DatabaseBasics.zip in my public databases folder at:

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

In this little demo file the section on 'entering data via a form/subforms' includes correlated combo boxes to select a country, a region and then a city.  Another important point to note is that only the city combo box is bound to a column in the form's Contacts table.  As this CityID column functionally determines both region and country, to include RegionID and CountryID column in the table would result in it not being normalized to Third Normal Form (3NF), and consequently open to the risk of update anomalies.