Limit option in Combobox2 based on Combobox1 choice

[corrinebean]

I would like to limit the options to choose from in a secondary combobox based upon what is selected in the first combobox. Have set up a table (Incident_Types) for combobox 1 that contains: Falls, Medication Variance, Treatment/Procedure Variance, HAI/Wound, Eqpt/Product-Related, Miscellaneous.

If Falls is chosen, for example, would like the second combobox to display only a list of choices related to falls (Unwitnessed Fall, Near Fall, Fall); if "Medication Variance" is chosen, would like the combobox to then display only the options related to Medication Variance.

 

[SkipVought]

Hi,

What applicaton?

If MS Access, then please post in one of the very many MS Access forums like forum702.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gammachaser]

If your database is properly Normalized, that one is easy. The RowSource for the first ComboBox should have the Primary Key (such as ID) and the text for the Incident Type. Like:

SELECT ID, IncidentType FROM Incident_Types

If IncidentID is the Foreign Key that connnects the two tables (i.e. Secondary_Choices.IncidentID = Incident_Types.ID) use:

Private Sub FirstComboBox_AfterUpdate
SecondCombo.RowSource = "SELECT ID, IncidentID, Choices FROM Secondary_Choices WHERE IncidentID = " & FirstComboBox &" ORDER BY Choices"
End Sub

The Bound Column for FirstComboBox has to be 1. You can hide the extraneous columns by using the Column Widths property.

Clear as mud?