Continuous Form Filter Slow VBA

bl4met

New Member
Joined
Dec 15, 2011
Messages
19
I have a continuous form that shows 1000 or so records that I am trying to filter based on Combo Boxes. I'm using the me.filter property of the form and it will filter it just takes a minute or two. The form is bound to a Query pulling from two tables and the fields I'm filtering on are indexed. If I have the filter criteria in the form set when I open the form, it opens quick. Its when i change it that i have issues. I have tried adding me.filteron = false and me.filteron=true and that hasn't seemed to help. Below is the sub that I'm calling to change the filter.

Code:
Sub FilterMe()


Dim Video As String
Dim HSD As String
Dim RealTCs As String
Dim FollowUp As String


Video = "(((TroubleCalls.Status) Not Like 'C*' And (TroubleCalls.Status) Not Like 'Follow*') AND " & _
        "((TroubleCalls.[WO Reason Code]) Not Like 'B*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Not Like 'DC*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Not Like 'DD*'  Or " & _
        "(TroubleCalls.[WO Reason Code]) Not Like 'EI*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Not Like 'EN*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Not Like 'I*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Not Like 'MA*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Not Like 'MS*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Not Like 'ST*'))"


HSD = "(((TroubleCalls.Status) Not Like 'C*' And (TroubleCalls.Status) Not Like 'Follow*') AND " & _
        "((TroubleCalls.[WO Reason Code]) Like 'EI*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Like 'EN*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Like 'I*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Like 'MA*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Like 'MS*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Like 'ST*'))"


RealTCs = "(((TroubleCalls.Status) Not Like 'C*' And (TroubleCalls.Status) Not Like 'Follow*') AND " & _
        "((TroubleCalls.[WO Reason Code]) Like 'DC*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Like 'DD*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Like 'IN*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Like 'EI*' Or " & _
        "(TroubleCalls.[WO Reason Code]) Like 'IM*'))"


FollowUp = "((TroubleCalls.Status) = 'WIP' OR (TroubleCalls.Status) Like 'Follow*')"


Me.FilterOn = False


Select Case Me.cboQue.value
    Case "All"
        If Me.cboRegion.value = "All" Then
            Me.Filter = ""
        Else
            Me.Filter = "[SPA Info].REGION = '" & Me.cboRegion.value & "'"
        End If
     Case "Internet/Phone"
        If Me.cboRegion.value = "All" Then
            Me.Filter = HSD
        Else
            Me.Filter = HSD & " AND [SPA Info].REGION = '" & Me.cboRegion.value & "'"
        End If
    Case "Video"
        If Me.cboRegion.value = "All" Then
            Me.Filter = Video
        Else
            Me.Filter = Video & " AND [SPA Info].REGION = '" & Me.cboRegion.value & "'"
        End If
    Case "Follow Up"
        If Me.cboRegion.value = "All" Then
            Me.Filter = FollowUp
        Else
            Me.Filter = FollowUp & " AND [SPA Info].REGION = '" & Me.cboRegion.value & "'"
        End If
    Case "Real TCs"
        If Me.cboRegion.value = "All" Then
            Me.Filter = RealTCs
        Else
            Me.Filter = RealTCs & " AND [SPA Info].REGION = '" & Me.cboRegion.value & "'"
        End If
    Case "Work Order Reason"
        If Me.cboRegion.value = "All" Then
            Me.Filter = "[TroubleCalls].[WO Reason Code] = '" & Me.cboWOReason.value & "'"
        Else
            Me.Filter = "[TroubleCalls].[WO Reason Code] = '" & Me.cboWOReason.value & "' AND [SPA Info].REGION = '" & Me.cboRegion.value & "'"
        End If
End Select
       
Me.FilterOn = True


End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,129,580
Messages
5,637,214
Members
416,961
Latest member
sigrid6940

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top