Resetting a filtered combo box

[Wesland]

Hi all,

I am fairly new with Access and VBA and am having troubles with the following. I filter a second combo box "cboTagNumber" with the first combo box "Combo133". The problem is when I clear the first combo box, the second combo box remains filtered. Is there an easy way to clear this? Thanks for any help!

This is the code:

Private Sub Combo133_AfterUpdate()
Dim strSource As String

strSource = "SELECT ID,[Tag Number] " & _
"FROM [E&I Table] " & _
"WHERE System = '" & Me.Combo133 & "' ORDER BY [Tag Number]"
Me.cboTagNumber.RowSource = strSource
Me.cboTagNumber = vbNullString

End Sub

 

[pr2-eugin]

Hello Wesland, Welcome to AWF..

How about..

Private Sub Combo133_AfterUpdate()
    Dim strSource As String
    If Me.Combo133.ListIndex <> -1 Then
        strSource = "SELECT ID,[Tag Number] " & _
                    "FROM [E&I Table] " & _
                    "WHERE System = '" & Me.Combo133 & "' ORDER BY [Tag Number]"
    Else
        strSource = vbNullString
    End If
    Me.cboTagNumber.RowSource = strSource
    Me.cboTagNumber = vbNullString
End Sub

 

[Wesland]

Thanks for the quick reply! It still has the same result. The list remains filtered when the Combo133 is cleared?

 

[pr2-eugin]

OOPS my bad.. Try..

Private Sub Combo133_AfterUpdate()
    Dim strSource As String
    If Me.Combo133.ListIndex <> -1 Then
        strSource = "SELECT ID,[Tag Number] " & _
                    "FROM [E&I Table] " & _
                    "WHERE System = '" & Me.Combo133 & "' ORDER BY [Tag Number]"
    Else
        strSource = vbNullString
    End If
    Me.cboTagNumber.RowSource = strSource
    [COLOR=Blue][B]Me.cboTagNumber.Requery[/B][/COLOR]
End Sub

 

[Simon_MT]

The idea of cascading combiboxes is to requery the dependant combiboxes upon entry. That way any changes to the preceding combibox will reflect in the choices of the current combibox.

Simon

 

[Wesland]

It still remains filtered? Could it be something else that is causing it? When you exit and go back in, it goes back to default containing all the values under [Tag Number]. Thanks, Wade

 

[pr2-eugin]

It works for me.. I am not sure what are you doing different there..

 

[Wesland]

cboTagNumber is used with an macro after update to search for record, Combo133 is used with a macro on change to run a query to filter the results in the records... not sure if this would have an effect on that? Thanks again for the help

 

[pr2-eugin]

How can the same combo133 be using both a Macro and Event procedure? I am

 

[Wesland]

event procedure on after update and embedded macro on change... is this a bad thing?
Thanks, Wade

 

[pr2-eugin]

Have only the Event Procedure, you do not need the Macro.