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
