Hi All.
I created search filter for continuous form. That is search/filter code:
The qSearchContinuousForm query joins 3 tables and 2 queries. My problem is when I run query by itself it works enough quickly but when I'm filtering continuous form by the code so the whole process occupy approximately 2-3 sec just for 20 records. Does exists way to optimize code to get best time filter execution? If yes. Can someone show how it to do?
Thanks.
I created search filter for continuous form. That is search/filter code:
Code:
Private Sub txtSearch_Change()
On Error GoTo errHandler
Dim filterText As String
'Apply or update filter based on user input.
If Len(txtSearch.Text) > 0 Then
filterText = txtSearch.Text
If InStr(Format(filterText, "\#mm\/dd\/yyyy\#"), "#") > 0 Then
Me.Form.Filter = "[qSearchContinuousForm].[RequestDate] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[FirstName] & ' ' & [qSearchContinuousForm].[FirstName] LIKE '*" & filterText & "*' " _
& "OR [qSearchContinuousForm].[TicketNo] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Department] LIKE '*" & filterText & "*' " _
& "OR [qSearchContinuousForm].[Device] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Model] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Location] LIKE '*" & filterText & "*'"
Else
Me.Form.Filter = "[qSearchContinuousForm].[Employee Name] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[FirstName] & ' ' & [qSearchContinuousForm].[FirstName] LIKE '*" & filterText & "*' " _
& "OR [qSearchContinuousForm].[TicketNo] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Department] LIKE '*" & filterText & "*' " _
& "OR [qSearchContinuousForm].[Device] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Model] LIKE '*" & filterText & "*' OR [qSearchContinuousForm].[Location] LIKE '*" & filterText & "*'"
End If
Me.FilterOn = True
'Retain filter text in search box after refresh.
txtSearch.Text = filterText
txtSearch.SelStart = Len(txtSearch.Text)
Else
' Remove filter.
Me.Filter = ""
Me.FilterOn = False
txtSearch.SetFocus
End If
Exit Sub
errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
End Sub
Thanks.