Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2007
    Posts
    214

    Question Unanswered: Simple Separate Search Form

    Hi All,

    I'm going to see if we can try a different approach to what I posted before.

    I have a main form called "Claims Tracking" , well, from within that form, I have an "advanced search" button which opens up a separate pop-up form in which I can then type in criteria to search. Problem is I want to be able to search 3 or more fields if necessary. Searching one field from my main Claims database at at time is not useful as there are many records and I need to drill down.

    How can I make this search, filter the contents down to whatever data I enter in the search fields.

    For instance I have the following unbound search fields on the form.

    First Name:
    Last Name:
    Employer:

    Right now I can only get this to filter one, when I may need to drill down by all three, or even more.

    Is there a way to easily code this search form?

    Thanks!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you want to keep it simple, say:
    - Search in one to three defined fileds
    - Search with always the same operator (= or Like)
    then the solution is easy, here is an example (here with the "=" operator):

    - 3 unbound TextBoxes:
    * Text_Filter1 --> search in the field "Customer" (data type: Text)
    * Text_Filter2 --> search in the field "Order_Number" (data type: Long)
    * Text_Filter3 --> search in the field "Destination"(data type: Text)

    - 1 Command Button:
    * Command_Search

    All on the same form as the one that displays the records.
    Code:
    Private Sub Command_Search_Click()
    
        Dim strFilter As String
        
        If Len(Nz(Me.Text_Filter1, "")) > 0 Then strFilter = "Customer = '" & Me.Text_Filter1 & "'"
        If Len(Nz(Me.Text_Filter2, "")) > 0 Then
            If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
            strFilter = strFilter & "Order_Number = " & Me.Text_Filter2
        End If
        If Len(Nz(Me.Text_Filter3, "")) > 0 Then
            If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
            strFilter = strFilter & "Destination = '" & Me.Text_Filter3 & "'"
        End If
        Me.Filter = strFilter
        If Len(strFilter) > 0 Then
            Me.FilterOn = True
        Else
            Me.FilterOn = False
        End If
        
    End Sub
    If your needs are more complex, (various fields, various number of fields, various operators, etc.) you'll have to build an SQL generator that can be rather complex.
    Have a nice day!

  3. #3
    Join Date
    Oct 2007
    Posts
    214
    I'm looking to enter the search criteria on a separate pop-up form. Would the above apply to my separate search form and if so, how?

    Thanks for your help!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes except that:
    Code:
        Me.Filter = strFilter
        If Len(strFilter) > 0 Then
            Me.FilterOn = True
        Else
            Me.FilterOn = False
        End If
    would become:
    Code:
        Forms("<FormName>").Filter = strFilter
        If Len(strFilter) > 0 Then
            Forms("<FormName>").FilterOn = True
        Else
            Forms("<FormName>").FilterOn = False
        End If
    With <FormName> being the name of the form that displays the records.
    Have a nice day!

  5. #5
    Join Date
    Oct 2007
    Posts
    214
    Ok the code works great to filter text_Filter1 and Text_Filter3 but for some reason when I enter data into text_Filter2 and click search, a query box pops up and doesn't return any results. Is there something wrong with this field?

    I checked all my field names and it seems to be ok. Thanks!

    Dim strFilter As String

    If Len(Nz(Me.Text_Filter1, "")) > 0 Then strFilter = "[Last Name] = '" & Me.Text_Filter1 & "'"
    If Len(Nz(Me.Text_Filter2, "")) > 0 Then
    If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
    strFilter = strFilter & "[First Name] = " & Me.Text_Filter2
    End If
    If Len(Nz(Me.Text_Filter3, "")) > 0 Then
    If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
    strFilter = strFilter & "Employer = '" & Me.Text_Filter3 & "'"
    End If
    Forms![Claims Tracking System].Filter = strFilter
    If Len(strFilter) > 0 Then
    Forms![Claims Tracking System].FilterOn = True
    Else
    Forms![Claims Tracking System].FilterOn = False
    End If

    End If

  6. #6
    Join Date
    Oct 2007
    Posts
    214
    Just got it. It is. Thanks for all your help this works great.

    If Len(Nz(Me.Text_Filter1, "")) > 0 Then
    If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
    strFilter = strFilter & "[Last Name] = '" & Me.Text_Filter1 & "'"
    End If

    If Len(Nz(Me.Text_Filter2, "")) > 0 Then
    If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
    strFilter = strFilter & "[First Name] = '" & Me.Text_Filter2 & "'"
    End If


    If Len(Nz(Me.Text_Filter3, "")) > 0 Then
    If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
    strFilter = strFilter & "Employer = '" & Me.Text_Filter3 & "'"
    End If

    Forms![Claims Tracking System].Filter = strFilter

    If Len(strFilter) > 0 Then
    Forms![Claims Tracking System].FilterOn = True
    Else
    Forms![Claims Tracking System].FilterOn = False
    End If

    End If

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •