Change listbox contents based on combobox value

[lecoughlin]

I have created a form with a combobox that contains a list all 51
states. I also have a list box that contains all of the states and
their counties. If the user selects "Massachusetts" from the
combobox, I would like the listbox to only show counties in
Massachusetts. How would I go about doing this?

Thanks in advance.

 

[Sean Timmons]

Try this

Private Sub State_Name_Change()
Dim strSQL As String
strSQL = "SELECT DISTINCT [CityTable].[City] FROM [CityTable]" & _
"WHERE [State] ='" & Me.[State] & "' ORDER BY [City]"
Me.[City].RowSource = strSQL
End Sub

Assuming state_Name is your combo box name, CityTable is your table with
states and cities.

 

[lecoughlin]

I figured it out. Thanks!

Try this

Private Sub State_Name_Change()
Dim strSQL As String
strSQL = "SELECT DISTINCT [CityTable].[City] FROM [CityTable]" & _
"WHERE [State] ='" & Me.[State] & "' ORDER BY [City]"
Me.[City].RowSource = strSQL
End Sub

Assuming state_Name is your combo box name, CityTable is your table with
states and cities.

I have created a form with a combobox that contains a list all 51
states.  I also have a list box that contains all of the states and
their counties.  If the user selects "Massachusetts" from the
combobox, I would like the listbox to only show counties in
Massachusetts.  How would I go about doing this?

Thanks in advance.

 

[Beetle]

The row source of your list box should look something like the following;

SELECT CountyName From tblYourTable Where
tblYourTable.State = [cboYourComboBox]

Then, in the After Update event (not the Change event which fires at
*every keystroke*) of your combo box you would requery the list box;

Private Sub cboYourCombo_AfterUpdate()

Me!YourListBox.Requery

End Sub