Two search boxes that work fine on there own but when used together they override eachothers results 1

[Kekins1]

I have two filters one called "Search_Gauge" and the other called " "Search_Date", both of these are on a form.
When i tpe in a Gauge number it displays all of the records relating to that gauge, then I want to use the search_date filter to further filter the results. However when I use the search_date filter it searches for all records with that date in but I only want records of that gauge number on that date.
here is the code for the filters

Private Sub Search_Gauge_AFTERUPDATE()
Dim strfilter As String
On Error GoTo ErrHandler
If Me.Search_Gauge.Text <> "" Then
strfilter = "[Gauge Number] = '" & Me.Search_Gauge.Text & "'"
Me.Filter = strfilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
With Me.Search_Gauge
.SetFocus
.SelStart = Len(Me.Search_Gauge.Text)
End With
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
End Sub

Private Sub Search_Date_AFTERUPDATE()
Dim strfilter As String
On Error GoTo ErrHandler
If Me.Search_Date.Text <> "" Then
strfilter = "[Date Calibrated] Like '*" & Me.Search_Date.Text & "*'"
Me.Filter = strfilter
Me.FilterOn = True



Else
Me.Filter = ""
Me.FilterOn = False

End If
If strfilter1 <> "" Then
strfilter = strfilter & " AND [Gauge Number]='" & Me.Search_Gauge.Text & "'"
Me.Filter = strfilter1
Me.FilterOn = True
End If

With Me.Search_Date
.SetFocus
.SelStart = Len(Me.Search_Date.Text)
End With

Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation

End Sub
Thanks
Kris

 

[dhookom]

You shouldn't have to use the Text property since it is available only when a control has the focus. The "value" property is more appropriate and is the default property so you can omit it.

I would place the filtering in a single sub and call it from the after update of the other controls.

Public Sub AddFilter()
    Dim strFilter as Variant
    If Not IsNull(Me.Search_Guage) Then
        strFilter = strFilter & "[Gauge Number] = '" & Me.Search_Gauge & "'"
    End If
    If Not IsNull(Me.Search_Date) Then
        strFilter = strFilter + " AND " & "[Date Calibrated] Like '*" & Me.Search_Date & "*'"
    End If
    If Not IsNull strFilter then
        Me.Filter = strFilter
        Me.FilterOn = True
     Else
        Me.Filter = ""
        Me.FilterOn = False
    End If
End Sub

Private Sub Search_Gauge_AFTERUPDATE()
    AddFilter
End Sub
 
Private Sub Search_Date_AFTERUPDATE()
    AddFilter
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Kekins1]

Thanks for the response, the line " If Not IsNull strFilter then" is a syntax error, is there another way of putting it??

 

[dhookom]

Sorry, I was typing outside of VBA. Try:

If Not IsNull(strFilter) then

Duane
Hook'D on Access
MS Access MVP

 

[Kekins1]

Cheers, that got rid of that error but now it is coming up with a message on the public sub saying "identifier under cursor is not recognized

 

[Kekins1]

Ignore my last post I had to add an underscore so now its "Public sub Add_Filter()"
and it works brilliantly, thanks alot for your help mate.