How to Reset Comboboxes

[bbarrene]

Hello,



I found a tutorial on how to use combo boxes to filter. I got it to work but I cannot reset the succeeding boxes when i change the initial combo box to another selection.

In the first picture below, everything flows smoothly as far as filtering but when I change the location to Madison, the Equipment and Station choices dont clear like the Plant does.

Can anyone help me with the coding to reset the succeeding boxes of combo box when a change a selection of a combobox that precedes them.

Thanks

Coding Used:
Option Compare Database

Private Sub cboLocation_AfterUpdate()
Dim sPlantSource As String

sPlantSource = "Select [tblPlant].[lngPlantID], [tblPlant].[lngLocationID], [tblPlant].[strPlantName] " & _
"FROM tblPlant " & _
"WHERE [lngLocationID] = " & Me.cboLocation.Value
Me.cboPlant.RowSource = sPlantSource
Me.cboPlant.Requery


End Sub


Private Sub cboPlant_AfterUpdate()

Dim sEquipmentSource As String

sEquipmentSource = "Select [tblEquipment].[lngEquipmentID], [tblEquipment].[lngPlantID], [tblEquipment].[strEquipmentName] " & _
"FROM tblEquipment " & _
"WHERE [lngPlantID] = " & Me.cboPlant.Value
Me.cboEquipment.RowSource = sEquipmentSource
Me.cboEquipment.Requery

End Sub

Private Sub cboEquipment_AfterUpdate()

Dim sStationSource As String

sStationSource = "Select [tblStation].[lngStationID], [tblStation].[lngEquipmentID], [tblStation].[strStationName] " & _
"FROM tblStation " & _
"WHERE [lngEquipmentID] = " & Me.cboEquipment.Value
Me.cboStation.RowSource = sStationSource
Me.cboStation.Requery

End Sub

[llkhoutx]

On the AftrerUpdate event of the first cbo, set each of the other cbos to Null.

[bbarrene]

I did that and the boxes become blank but when I drop down the combo box, the options are still available to pick. Does anyone know how I can reset the combo box to clear all of the choices similar to when it is first ran where there are no choices to choose from until you pick from the preceding combo box.

[llkhoutx]

You have to reset the "row source" property of each of the combo boxes. I believer that you probably need to requery the other cbos on the after update event of the 1st cbo - if the 1st cbo is a filter for the other cbos.

[RuralGuy]

I often will Requery the ComboBox in either the GotFocus or OnEnter events of the cbo in situations like this.

[bbarrene]

I have already tried adding "Me.cboEquipment.Requery" and "Me.cboStation.Requery" under the Private Sub cboLocation_AfterUpdate() section but still no success.

Man, this is stuff is harder than I thought it would be.

Thank you guys for trying to help me out.