Resetting a filter...

[waldo7474]

Hi All!!!

I am having issues with a filtered form. Here is the deal: I have a form that has five searchable controls. Once I hit the search button it filters all the records so that I get only the ones desired. I am having problems is when I try another search. At first, I had it so that it would try to search through the filtered records for the new search. I have tried to correct this problem two ways with the same result. I have changed the recordsource back to the original query, and I have turned the filter off, once I hit the New Search button. The result is that the first search works fine, the second search starts with the original amount of records, but it comes up with nothing for a recordsource.

Here is my code for the Search button:

Private Sub SearchBtn_Click()

    Dim multVar As Integer
    Dim FilterString As String
    
    multVar = 0     'This variable is to let the function know if there's more than one criteria
    
    If Not IsNull(PartNumberLookup) Then
        FilterString = "[PartNumber] Like '*" & PartNumberLookup & "*'"
        multVar = multVar + 1
    End If
    
    If Not IsNull(DescriptionLookUp) Then
        If multVar = 0 Then
            FilterString = "[Description] Like '*" & DescriptionLookUp & "*'"
        Else
            FilterString = FilterString & "AND [Description] Like '*" & DescriptionLookUp & "*'"
        End If
        multVar = multVar + 1
    End If
    
    If Not IsNull(FirstUsedOnLookup) Then
        If multVar = 0 Then
            FilterString = "[FirstUsedOn] Like '*" & FirstUsedOnLookup & "*'"
        Else
            FilterString = FilterString & "AND [FirstUsedOn] Like '*" & FirstUsedOnLookup & "*'"
        End If
        multVar = multVar + 1
    End If
    
    If Not IsNull(OldPartNumberLookup) Then
        If multVar = 0 Then
            FilterString = "[OldPartNumber] Like '*" & OldPartNumberLookup & "*'"
        Else
            FilterString = FilterString & "AND [OldPartNumber] Like '*" & OldPartNumberLookup & "*'"
        End If
        multVar = multVar + 1
    End If
    
    If Not IsNull(WrongPartNumberLookup) Then
        If multVar = 0 Then
            FilterString = "[WrongPartNumber] Like '*" & WrongPartNumberLookup & "*'"
        Else
            FilterString = FilterString & "AND [WrongPartNumber] Like '*" & WrongPartNumberLookup & "*'"
        End If
        multVar = multVar + 1
    End If
    
    'Set the form's recordsource
    Forms!FrmDwgSearch.Filter = FilterString
    Forms!FrmDwgSearch.FilterOn = True

Here is my code for the New Search button:

Private Sub NewSearchBtn_Click()

    Dim QryDwgSearch1 As String
    
    Forms!FrmDwgSearch.FilterOn = False
    DoCmd.GoToRecord , , acNewRec
    Me!PartNumberLookup = ""
    Me!DescriptionLookUp = ""
    Me!FirstUsedOnLookup = ""
    Me!OldPartNumberLookup = ""
    Me!WrongPartNumberLookup = ""
    Me!PartNumberLookup.SetFocus

I also had it where I set the forms recordsource:

Forms!FrmDwgSearch.RecordSource = "Select * FROM [QryDwgSearch1] WHERE " & FilterString

And switched it back with:

Forms!FrmDwgSearch.RecordSource = "QryDwgSearch1"

Again both of the ways (either through recordsource or filtering) came back with the same results. No results after I clicked the New Search button, put in criteria, and clicked the Search button.

I would rather not have the user close the form and reopen it. (It shouldn't be this complicated.)

Any help would be greatly appreciated. If any of this doesn't make sense I would be happy to explain further. Thanks again for all the help in advance!!
Wally

 

[Rick39]

Dim multVar As Integer
Dim FilterString As String

Me.FilterOn = False

multVar = 0 'This variable is to let the function know if there's more than one criteria

'The rest of your code..........

 

[waldo7474]

That didn't seem to work. It still doesn't find any records. At best it finds one. Is this a normal problem??

Any more help would be greatly appreciated.

Thanks!!
Wally