Option Compare Database 'Use database order for string comparisons
Option Explicit
Function FastSearch(SourceForm, ResultsForm)
'Compile and execute a search using the criteria in SourceForm$
'and displaying the results in ResultsForm$.
'SourceForm$ is closed before ResultsForm$ is opened.
'Only controls in SourceForm$ with the tag Search are used in
'the search. These controls must have a name identical to the
'coresponding field in the database.
'NOTE: The OpenArgs property of the results form will be set to
'Search by this code alowing a form to determine how it was opened.
On Error Resume Next 'Disable error messages
Dim Searchtag As String
Dim SearchFilter As String
Dim ControlNumber As Integer
Dim MainScreenNew As Form
Dim Recordset As Recordset
Searchtag = "Search"
SearchFilter = ""
For ControlNumber = 0 To Forms(SourceForm).Count - 1
If Forms(SourceForm)(ControlNumber).Tag = Searchtag Then
'We are interested in this control. What does it contain?
If Not (IsNull(Forms(SourceForm)(ControlNumber))) Then
If (Err = 0) Then
'It's not empty. Add an AND to the search filter
If SearchFilter <> "" Then SearchFilter = SearchFilter + " And "
'Does it contain a wildcard?
If InStr(Forms(SourceForm)(ControlNumber), "*") > 0 Then
'Yes, there's a wildcard here
SearchFilter = SearchFilter + "([" & Forms(SourceForm)(ControlNumber).Name & "] Like """ & Forms(SourceForm)(ControlNumber) & """)"
Else
'No wildcard. Look for an exact match
SearchFilter = SearchFilter + "([" & Forms(SourceForm)(ControlNumber).Name & "] = """ & Forms(SourceForm)(ControlNumber) & """)"
End If
End If
End If
End If
Err = 0
Next ControlNumber
DoCmd.Close A_FORM, SourceForm 'Close the search criteria form
If DCount("Titleid", "QMainScreenNew", SearchFilter) = 0 Then
Beep
MsgBox "No records matched the search criteria", vbOKOnly, "No Records Found"
DoCmd.Close acForm, MainScreenNew
DoCmd.OpenForm MainScreenNew
DoCmd.ShowAllRecords
Screen.ActiveForm.Recordset.MoveLast
GoTo Line1
End If
DoCmd.OpenForm ResultsForm, , , SearchFilter, , , Searchtag 'Open the search results form
Screen.ActiveForm.Recordset.MoveLast
'DoCmd.GoToRecord acDataForm, MainScreenNew, acLast
Forms(ResultsForm)("FilterSQL") = SearchFilter 'Make Filter available to other objects
Err = 0
Line1: Exit Function
End Function