Search multiple fields simultaneously and set form to record found

This shows how to add 2 unbound text boxes, a subform to return results, and search button to a form. Any number of text boxes can be added we are using 2 in this example.
The user type's in partial search criteria in either or both text boxes. Or if they don't key in anything it returns all records. The matches are returned in a subform.
If no matches are found the sub is empty. If several are found then the one needed is clicked on and the Main form syncs to it.
The key to this powerful search routine is the 'AddToWhere' SUB shown below builds a SQL statement. The 'AddToWhere' sub is placed in a module and therefore is global for any form which calls it.

There a several things involved. First you cannot have a Primary key field or it will never work. You must remove the Primary key from a key field.

1. Need 2 or more Unbound text boxes on form
2. Need 1 Button to search for items
3. Add a Sub Routine to a module called 'AddToWhere' which builds the SQL to search
4. Add the following code to the sub other form in the propertys record source:
'SELECT * FROM [OPENSYSTEMSCUST] WHERE False'
5. Add code below in the sub forms click event

------------------------ Put his code behind the button to search -------

Private Sub VIEW_Click()
'On Error GoTo Err_VIEW_Click
æ------------------------------ Starting here ----------------------
Dim MySQL As String, MyCriteria As String, MyRecordSource As String
Dim ArgCount As Integer
' Initialize SELECT statement.
MySQL = "SELECT * FROM [PhoneLog] WHERE "

' Use values entered in text boxes in form header to create criteria for WHERE clause.
'text box name on form 'Field name in Table 'blank info ' number of times run Addt
AddToWhere [Find1], "[CustomerName]", MyCriteria, ArgCount
AddToWhere [Find2], "[City]", MyCriteria, ArgCount

' If no criterion specifed, return all records.
If MyCriteria = "" Then
MyCriteria = "True"
End If

' Create SELECT statement.
MyRecordSource = MySQL & MyCriteria

' Optional Order By clause
If Me![Find1] <> "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [CustomerName]"
ElseIf Me![Find2] <> "" Then
MyRecordSource = MySQL & MyCriteria & " ORDER BY [City]"
Else
MyRecordSource = MySQL & MyCriteria & " ORDER BY [CustomerName]"
End If


' set record source to Subform
Me![YOURsubform].Form.RecordSource = MyRecordSource
-------------------------------------------------------------

Exit_VIEW_Click:
Exit Sub

Err_VIEW_Click:
MsgBox Error$
Resume Exit_VIEW_Click

End Sub

--------------------------------------------------------------
This is the AddToWhere SUB "copy and paste in a module"
--------------------------------------------------------------
Public Sub AddToWhere(FieldValue As Variant, FieldName As String, MyCriteria As String, ArgCount As Integer)

' Create criteria for WHERE clause.
If FieldValue <> "" Then
' Add "and" if other criterion exists.
If ArgCount > 0 Then
MyCriteria = MyCriteria & " and "
End If

' Append criterion to existing criteria.
' Enclose FieldValue and asterisk in quotation marks.
MyCriteria = (MyCriteria & FieldName & " Like " & Chr(39) & FieldValue & Chr(42) & Chr(39))

' Increase argument count.
ArgCount = ArgCount + 1
End If
End Sub


Put this in the Click Event of the sub form.
After the items are found on the sub form you can select an
item on the sub form and the main form will sync to it..
---------------------------------------------------------------
Dim SyncCriteria As String
Dim f As Form, rs As Recordset

'Define the from object and recordset object for the AutoCAD form
Set f = Forms(Screen.ActiveForm.FormName)
Set rs = f.RecordsetClone

' define the criteria used for the sync
SyncCriteria = "[YourField]='" & Me![YourField] & "'"

' find the corresponding record in the Parts table
rs.FindFirst SyncCriteria
f.Bookmark = rs.Bookmark
---------------------------------------------------------------

I use this code for every database I ever create or will ever create, Period.
There is no better search routine on the planet.