Reset filter on search form

[NeilT123]

I have two different search criteria on one of my forms. Search 1 is via a list box which lists all current entries and clicking on the list box invokes a filter

Private Sub QuickSearch_Click()

Me.frmIfFertAppln.Form.Filter = "[FieldCode] = " & Me.QuickSearch.Column(0)
Me.frmIfFertAppln.Form.FilterOn = True

End Sub

Search 2 is a "like" or "starts with" search and uses a query behind the subform

SELECT tblTEMPIfFertAppln.FieldCode, tblTEMPIfFertAppln.AccountName, tblTEMPIfFertAppln.FarmAccountNumber, tblTEMPIfFertAppln.FieldName, tblTEMPIfFertAppln.FieldComments, tblTEMPIfFertAppln.[SubFarm/FieldGroup], tblTEMPIfFertAppln.SoilTexture, tblTEMPIfFertAppln.NVZStatement, tblTEMPIfFertAppln.NoLongerCropped, tblTEMPIfFertAppln.DateOfAnalysis, tblTEMPIfFertAppln.PIndex, tblTEMPIfFertAppln.KIndex, tblTEMPIfFertAppln.MgIndex, tblTEMPIfFertAppln.pH, RemovePunc([tblTEMPIfFertAppln].[FieldName]) AS Expr1
FROM tblTEMPIfFertAppln
WHERE (((RemovePunc([tblTEMPIfFertAppln].[FieldName])) Like "*" & [Forms]![frmIfTFertAppln]![Search2] & "*" And (RemovePunc([tblTEMPIfFertAppln].[FieldName])) Like [Forms]![frmIfTFertAppln]![Search3] & "*"))
ORDER BY tblTEMPIfFertAppln.FieldName;

If I open the form, then on 1st use either search works perfectly. The problem is when I go to Search 1 and then switch to Search 2 search 2 doesn't work. I am guessing that it is to do with the filter being in place and have tried all sorts of code in the search box that drives Search 2 but to no avail. My latest code is

Private Sub Search_Change()
' clear any filter on frmIfFertAppln
Me.frmIfFertAppln.Form.FilterOn = False
Me.frmIfFertAppln.Form.Filter = ""

' to clear the QuickSearch list box
Dim varSelected As Variant
For Each varSelected In QuickSearch.ItemsSelected
QuickSearch.Selected(varSelected) = False
Next varSelected

'error trapping to deal with no matches
On Error GoTo SearchBoxErrorHandler

' save the variable vSearchString
Dim vSearchString As String
    vSearchString = Search.Text
    Search2.Value = vSearchString
Me.frmIfFertAppln.Requery

SearchBoxErrorHandlerExit:
    Exit Sub
SearchBoxErrorHandler:
    If Err = 3021 Then
    MsgBox "There are no matches for your selection."
        Resume Next
    Else
        MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
        Resume SearchBoxErrorHandlerExit
    End If

End Sub

The query behind form frmIfFertAppln is updating correctly the form just goes to and stays on the 1st record. Any suggestions please?

Thanks

 

[NeilT123]

I resolved this by doing away with the filter and utilising the query based search to include a value from the list box

Private Sub QuickSearch_Click()

' clear any search data
Me.Search = ""
Me.Search2 = ""
Me.Search3 = ""

' column numbering starts from 0
Me.Search3 = Me.QuickSearch.Column(0)

Me.frmIfFertAppln.Requery

End Sub