Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Forms FAQ

Mega Search

Search multiple fields simultaneously and set form to record found by DougP
Posted: 16 Oct 01 (Edited 12 Apr 07)

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:
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]"
        MyRecordSource = MySQL & MyCriteria & " ORDER BY [CustomerName]"
    End If

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

    Exit Sub

    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.

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close