Resetting a filtered combo box (1 Viewer)

Wesland

Registered User.
Local time
Today, 05:37
Joined
Sep 11, 2013
Messages
11
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

Super Moderator
Local time
Today, 11:37
Joined
Nov 30, 2011
Messages
8,494
Hello Wesland, Welcome to AWF.. :)

How about..
Code:
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

Registered User.
Local time
Today, 05:37
Joined
Sep 11, 2013
Messages
11
Thanks for the quick reply! It still has the same result. The list remains filtered when the Combo133 is cleared?
 

pr2-eugin

Super Moderator
Local time
Today, 11:37
Joined
Nov 30, 2011
Messages
8,494
OOPS my bad.. :eek: Try..
Code:
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

Registered User.
Local time
Today, 11:37
Joined
Feb 26, 2007
Messages
2,177
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

Registered User.
Local time
Today, 05:37
Joined
Sep 11, 2013
Messages
11
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

Super Moderator
Local time
Today, 11:37
Joined
Nov 30, 2011
Messages
8,494
It works for me.. I am not sure what are you doing different there.. :eek:
 

Wesland

Registered User.
Local time
Today, 05:37
Joined
Sep 11, 2013
Messages
11
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

Super Moderator
Local time
Today, 11:37
Joined
Nov 30, 2011
Messages
8,494
How can the same combo133 be using both a Macro and Event procedure? I am :confused:
 

Wesland

Registered User.
Local time
Today, 05:37
Joined
Sep 11, 2013
Messages
11
event procedure on after update and embedded macro on change... is this a bad thing?
Thanks, Wade
 

pr2-eugin

Super Moderator
Local time
Today, 11:37
Joined
Nov 30, 2011
Messages
8,494
Have only the Event Procedure, you do not need the Macro.
 

Users who are viewing this thread

Top Bottom