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

    Question Unanswered: Filter Multiple records using a separate search form

    Hi All,

    I have a separate search form which I have a combo box to select the field on my Claims Tracking system in which I want to search then I have a unbound text box to enter in criteria. Well, I then click search and it displays the information. I would like to be able to then select another field, type in criteria and hit search again to have it drill down to those results and so forth. Below is the code I have and it only allows me to apply one filter.

    How can I apply more than just one?
    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
    MsgBox "You must select a field to search."

    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
    MsgBox "You must enter a search string."


    Else

    'Generate search criteria
    GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"


    'Filter frmCustomers based on search criteria

    Forms![Claims Tracking System].RecordSource = "select * from [Claim Tracking System] where " & GCriteria
    Forms![Claims Tracking System].Caption = "[Claim Tracking System] (" & cboSearchField.Value & "*')"



    MsgBox "Results have been filtered."

    End If

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    All you have to is remember the GCriteria form the last search but the only thing you have to think about is a AND or OR in the next Pass

    Something a long this line


    Dim GCriteria as string
    Dim GCriteriaSoFar as string
    At the top of the form (not in the sub) now GCriteria,GCriteriaSoFar is global to the form so

    If gcriteria <>"" then 'must be 2nd pass
    GCriteria = GCriteriaSoFar & " Or " & cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
    Gcriteriasofor = GCriteriaSoFar
    Else
    GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
    Gcriteriasofor = GCriteriaSoFar
    End if

    rest of you bit
    Last edited by myle; 02-11-11 at 21:58.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Oct 2007
    Posts
    214
    Thanks for the reply! Maybe I'm all wrong here. I click a command "search" to perform the "filter", well I have the following code but it still is only allowing me to search and filter one field. When I try to select another one, it clears the first one.

    Here is what I have in the command button code.
    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
    MsgBox "You must select a field to search."

    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
    MsgBox "You must enter a search string."


    Else

    Dim GCriteria As String
    Dim GCriteriaSoFar As String


    If GCriteria <> "" Then 'must be 2nd pass
    GCriteria = GCriteriaSoFar & " Or " & cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
    Gcriteriasofor = GCriteriaSoFar
    Else
    GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
    Gcriteriasofor = GCriteriaSoFar





    Forms![Claims Tracking System].RecordSource = "select * from [Claim Tracking System] where " & GCriteria
    Forms![Claims Tracking System].Caption = "[Claim Tracking System] (" & cboSearchField.Value & "*')"






    MsgBox "Results have been filtered."

    End If
    End If

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    hay that

    Dim GCriteria As String
    Dim GCriteriaSoFar As String

    should not be in side the sub as each time its is run it will be REDIM
    and there for the GCriteria is blank each time

    So you should add is to the onopen event of the form

    GCriteria = ""
    GCriteriaSoFar = ""

    so that you know that they a emptyeach time the form is open.

    it must be at the Top of the form code page

    place it under the

    Option Compare Database
    Dim GCriteria As String
    Dim GCriteriaSoFar As String

    these variable will only be decleared once
    Last edited by myle; 02-12-11 at 00:44.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    Oct 2007
    Posts
    214
    Maybe It's me. Here's what I have and when I search one field, i get my results. But as soon as I do my other search for a different field, it just shows those results and clears my first one.

    Here is what I have in my command button. I put the other line of code in the top of the form




    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
    MsgBox "You must select a field to search."

    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
    MsgBox "You must enter a search string."


    Else


    If GCriteria <> "" Then 'must be 2nd pass
    GCriteria = GCriteriaSoFar & " Or " & cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
    GCriteriaSoFar = GCriteriaSoFar
    Else
    GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
    GCriteriaSoFar = GCriteriaSoFar
    End If


    'Generate search criteria
    GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

    'Filter frmCustomers based on search criteria

    Forms![Claims Tracking System].RecordSource = "select * from [Claim Tracking System] where " & GCriteria
    Forms![Claims Tracking System].Caption = "[Claim Tracking System] (" & cboSearchField.Value & "*')"






    MsgBox "Results have been filtered."

    End If

  6. #6
    Join Date
    Oct 2007
    Posts
    214
    Attached is a sample of what I based my search off of. I just want to be able to "drill down" to more than just one field. For instance, if I search one field, I want the database to keep that filter applied so I can then filter down to another then another.

    Attached is the sample database. This database only allows one field at a time to be searched though.
    Attached Files Attached Files

Posting Permissions

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