none
Cascading Combo boxes filtering on different values than their bound value RRS feed

  • Question

  • Hello,

    I'm writing a data entry form in Access and I am needing to use cascading comboboxes (comboboxes that filter based on previous selections). So far I have been able to successfully create these comboboxes by using their bound values to filter the next selection. In my case, I have 3 comboboxes: Course combobox, Question combobox, and Response combobox. The idea is that you select a class that has a certain set of questions, which then you can select a question and get a certain set of available response options. So far this works well but the value I need to store in my control source table is not the value in which the comboboxes are being bound by. For instance, the Course combobox is currently bound by a field called FormID which identifies the form in which the questions for that course were listed on but I would like to actually store the selected course's ID field instead in the control source table. Further, the Question combobox is bound by a QuestionType field in order to filter the Response combobox but the value I would like to store is actually the question's ID field in the control source.

    To summarize, I was wondering if it is possible to store a different value in the control source than what the combobox is bound to or rather is there a way to filter comboboxes by a different value than the bound value in order to achieve my desired result above.

    Thank you

    Thursday, March 1, 2018 2:28 PM

All replies

  • Hi,

    Assuming you are using something similar to this in the AfterUpdate of the preceding combobox to cascade the items in the succeeding combobox using the bound column:

    Me.Combo2.RowSource="SELECT FieldName FROM TableName WHERE SomeField=" & Me.Combo1

    If so, you can simply change the bound column of the combobox to the value you want to store in the table and modify your code to filter by the column matching the next combobox. For instance, let's say the old bound column was the first one and you changed the bound column to store the second column, you can change the code to something like this:

    Me.Combo2.RowSource="SELECT FieldName FROM TableName WHERE SomeField=" & Me.Combo1.Column(1)

    Just remember the Column() property is zero-indexed, so the first column is zero (0).

    Hope it helps...

    Thursday, March 1, 2018 3:56 PM
  • I think it might be helpful to go back to first principles with regard to the use of correlated combo boxes which model a tree-structured hierarchy as seems to be the case here.

    The following applies to combo boxes in a form in single form view.

    The first and most important principle is that the only the lowest level combo box, response in your case, should be a bound control.  The others, course and question in your case should be unbound controls.  This is a matter of basic normalization, in that answer determines question, and question determines course.  Consequently, to have columns for question and course in the referencing table would introduce transitive dependencies, and the table would therefore not be normalized to Third Normal Form (3NF), and consequently open to the risk of update anomalies.  With the answer combo box bound to a foreign key AnswerID column, code in the form's Current event procedure would, if the form is not at a new record, assign a value to the question combo by looking it up in a Questions table, and similarly would assign a value to the course combo box by looking it up in a Courses table on the basis of the value assigned to the question combo box.  The code would also requery the combo boxes so that the rows in each combo box's list reflect the value in the combo box above it in the hierarchy.

    You'll find an example of this in DatabaseBasics.zip in my public databases folder at:

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

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the section on 'entering data via a form/subforms' includes a bound form in which there are correlated combo boxes for selecting a country, then a region and finally a city, with only the last being bound.  The RowSource of the country combo box simply lists all countries:

    SELECT Countries.CountryID, Countries.Country
    FROM Countries
    ORDER BY Countries.Country;

    That for the region combo box references the city combo box as a parameter on the foreign key column:

    SELECT Regions.RegionID, Regions.Region
    FROM Regions
    WHERE Regions.CountryID=Form!cboCountry
    ORDER BY Regions.Region;

    And that for the city combo box references the region combo box as parameter:

    SELECT Cities.CityID, Cities.City
    FROM Cities
    WHERE Cities.RegionID=Form!cboRegion
    ORDER BY Cities.City;

    Note the use of the Form property to reference the current form rather than referencing it by name as a member of the Forms collection.

    The code in the AfterUpdate event procedures of the combo box's is as follows:

    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

    The form's Current event procedure assigns values to the unbound comb boxes as follows:

    Private Sub Form_Current()

        ' if new record set unbound combo boxes to Null,
        ' for existing record call functions to return
        ' region and country for current value of CityID
        If Me.NewRecord Then
            Me!cboCountry = Null
            Me!cboRegion = Null
        Else
            Me!cboRegion = GetRegion(Me!cboCity)
            Me!cboCountry = GetCountry(Me!cboRegion)
        End If
        
        ' requery unbound combo boxes to list
        ' only regions and cities for current city, if any
        Me!cboRegion.Requery
        Me!cboCity.Requery
        
    End Sub

    This code calls the following functions in the form's module::

    Private Function GetRegion(varCityID)

        ' get RegionID for current value of CityID field
        If Not IsNull(varCityID) Then
            GetRegion = DLookup("RegionID", "Cities", "CityID = " & varCityID)
        End If
        
    End Function

    Private Function GetCountry(varRegionID)

        ' get CountryID for current value of Region combo box
        If Not IsNull(varRegionID) Then
            GetCountry = DLookup("CountryID", "Regions", "RegionID = " & varRegionID)
        End If

    End Function

    In your case courses are analogous to my countries, questions to my regions, and responses to my cities.

    If you do download my file, note also the NotInList event procedures of each control are used to add a city and/or region and/or country bot yet represented in the database.  This probably won't be necessary in your case, as the sets of course, questions and responses will be predefined.

    In each of the combo boxes the first numeric column is hidden by setting the ColumnWidths property to zero.

    Correlated combo boxes as described above work perfectly well in single form view, but will not do so in a form in continuous forms view where, as in the above, the first column is a hidden 'surrogate' key.  The controls will in some rows appear to be empty, though no data is lost, merely hidden.  In a continuous form recourse must be had to 'hybrid' controls, in which a read-only text box is carefully superimposed on a combo box to give the appearance of a single combo box control.  The form's RecordSource in this case is not the referencing  table, but a query which also incudes the referenced tables.  This allows the text boxes superimposed on the combo boxes text columns to be bound to the text columns from the referenced  tables.   You'll find examples in ComboDemo.zip in the same OneDrive folder.

    Where 'natural' keys are used on the other hand, the problem of apparently empty controls in a continuous form does not arise, so simple conventional combo boxes can be used.  My demo also includes an example of this.

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Thursday, March 1, 2018 6:55 PM Hyperlink added.
    Thursday, March 1, 2018 6:52 PM