Filter Upon Filter in Continuous Form (1 Viewer)

123dstreet

Registered User.
Local time
Today, 02:02
Joined
Apr 14, 2010
Messages
122
Hello,

I have a continuous form that shows all the records from TableA. In the form header I have added multiple filtering options using either text or combo boxes. Each filter uses the following VBA executed by a button:
Code:
 Me.Filter = "[Priority] Like" & "'" & Me.Combo94 & "*" & "'"
    If IsNull([Me.Combo94]) = False Then Me.FilterOn = True Else Me.FilterOn = False
    SendKeys "+{Down}"

The filters work fine one at a time, but is it possible to run FilterA, then in the header choose a different filter, FilterB, and run it to further filter down the records?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:02
Joined
Aug 30, 2003
Messages
36,118
Sure, test if they both have values and then:

Code:
Me.Filter = "[Priority] Like '" & Me.Combo94 & "*' And OtherField Like '" & Me.OtherCombo & "*'"
 

123dstreet

Registered User.
Local time
Today, 02:02
Joined
Apr 14, 2010
Messages
122
Thanks for the quick reply!

The problem is I don't want to run both filters at the same time. For example, the user filters by Product first, now they want to filter it even further with one of the other optional filters in the header...

Do you have to include the previous filter in the most recent filter?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:02
Joined
Aug 30, 2003
Messages
36,118
Isn't filtering it further the same as running both filters at the same time? I'd create a function that filtered by one, the other, or both as appropriate to what has been entered.
 

123dstreet

Registered User.
Local time
Today, 02:02
Joined
Apr 14, 2010
Messages
122
I don't think they are the same.

If the user wants to see what falls into FilterA, then they decide they want to narrow the search down even further, so they use FilterB, the result should show both FilterA and FilterB combined, but when I use FilterA, then decide to use use FilterB, it only uses FilterB and shows all records to FilterB only...
 

Beetle

Duly Registered Boozer
Local time
Today, 03:02
Joined
Apr 30, 2011
Messages
1,808
You could use something like the following (aircode);


Code:
Dim strWhere As String

If Nz(Me.Combo1, vbNullString) <> vbNullString Then
    strWhere = strWhere & "([Field1]=""" & Me.Combo1 & """) And "
End If
    
If Nz(Me.Text2, vbNullString) <> vbNullString Then
    strWhere = strWhere & "([Field2]=""" & Me.Text2 & """) And "
End If
    
If Nz(Me.Combo3, vbNullString) <> vbNullString Then
    strWhere = strWhere & "([field3]=""" & Me.Combo3 & """) And "
End If
    
[COLOR="Green"]'Remove the trailing operator and spaces[/COLOR]
strWhere = Left(strWhere, Len(strWhere) - 5)

Me.Filter = strWhere
Me.FilterOn = True

If you want to return only records that meet all criteria, use the And operator. If you want to return records that meet any criteria, use the Or operator (and adjust the trim length to 4)

You may have to trap for other things as well. For example, if someone were to not make any selections but still try to apply the filter (via a command button or whatever you're using) then the code would error at this line;

strWhere = Left(strWhere, Len(strWhere) - 5)
 

123dstreet

Registered User.
Local time
Today, 02:02
Joined
Apr 14, 2010
Messages
122
Beetle,

Worked like a charm, thank you very much!
 

Users who are viewing this thread

Top Bottom