I have a single form with 2 subform , before migrating to sql I could search with below code my form. Record source of my form is a query is running fast.
Now when I click search button for filtering records in this form is runs very slowly after 3 minutes
Code:
DoCmd.OpenForm "frmDocuments", WhereCondition:=varWhere
before it was 1 second.
Code:
Private Sub cmdSearch_Click()
Dim varWhere As Variant, varWhere2 As Variant, varDateSearch As Variant
Dim rst As DAO.Recordset
' Initialize to Null
varWhere = Null
varWhere2 = Null
varDateSearch = Null
If Not IsNothing(Me.txtTransmittal_to_Site) Then
' .. build the predicate
' Must use a subquery here because the value is in a linking table...
varWhere = (varWhere + " AND ") & _
"[Owner Document Number] IN (SELECT [Owner Document Number] FROM tblTransmittals " & _
"WHERE tblTransmittals.[CT- Transmittals] LIKE '" & Me.txtTransmittal_to_Site & "*')"
End If
If IsNothing(varWhere) Then
MsgBox "You must enter at least one search criteria.", vbInformation, gstrAppTitle
Exit Sub
End If
' Open a recordset to see if any rows returned with this filter
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM tblDocuments WHERE " & varWhere)
' See if found none
If rst.RecordCount = 0 Then
MsgBox "No Documents meet your criteria.", vbInformation, gstrAppTitle
' Clean up recordset
rst.Close
Set rst = Nothing
Exit Sub
End If
' Hide me to fix later focus problems
Me.Visible = False
' Move to last to find out how many
rst.MoveLast
If IsFormLoaded("frmDocuments") Then
' Note: if form already open, this just applies the filter
DoCmd.OpenForm "frmDocuments", WhereCondition:=varWhere
Forms!frmDocuments.SetFocus
End If