MS Access - Building a combobox that returns multiple values based on previous combobox selection

Question

I have two tables that share the the study protocol titles (string). In the one table "Study Events", I store reimbursement information related to each study protocol, but what concerns us are the two columns, "Protocol" and "EventDefinition". In this table, I may define multiple "events" for a single study protocol.

In the other table "Study Visits and Interactions", I have information related to when study events occur and have developed a form for end-users to enter that data. In that form, there is a combobox, "Study Protocol", that allows users to select from the unique list of protocols in the database. Then I have a second combobox that I would like to populate with the "EventDefinition" fields available for the selected protocol.

I am using the following query for my RowSource statement:

SELECT [Study Events].EventDefinition
FROM [Study Events]
WHERE Protocol= [Me].[Study Protocol];

When I define [Study Protocol] in the editor as one of the protocol names, the query returns the correct information. But when I try it in the actual form, there is nothing returned.

Have I used improper syntax for referencing the Combobox value if it's defined in the ControlSource table as a string?

Remove [Me]. It only works in VBA and reference to the form should not be needed. — June7
Removing [Me] works when I click view in the RowSource SQL editor, but does not update when in the form itself. Do I need to modify "AfterUpdate" or similar? — Custergrant

andrew andrew · Accepted Answer · 2018-03-13T03:12:55

To filter the second combo-box based on the selection of the first combo-box you need some VBA code to refresh the second combo-box when the first combo-box changes.

The below assumes the first combo-box name is cboStudyProtocol and the second is cboEventDefinition.

The RowSource of cboEventDefinition should be similar to the following:

SELECT [Study Events].EventDefinition
FROM [Study Events]
WHERE Protocol = [cboStudyProtocol];

Then, add an event for the second combo-box:

Private Sub cboStudyProtocol_AfterUpdate()
    cboEventDefinition.Requery
End Sub

This updates the list of values in the second-combo box when the selection in the first combo-box changes.

MS Access - Building a combobox that returns multiple values based on previous combobox selection

1 Answers