Filter error handling?

[KateB]

Hi everyone,

I have a search box on a form that works great provided the number entered
exists in the database. However, if it is incorrect (i.e. not found) it
gives me a blank page. I can go back to my form by clicking the filter
button in the menu but the people who will be using the form won't know to do
that (and won't remember even if I tell them!) If I post the code can anyone
suggest what I can add so that it gives a message telling them its invalid
and to try again? (and where to add it! I am self-taught and everything I
use has been found and adapted from this forum or some of the excellent web
sites suggested).

Many thanks in advance, Kate


Private Sub NHSNoSearch_Click()

On Error GoTo Err_NHSNoSearch_Click

Dim FilterStr As String

If IsNumeric(txtNHSnumber) = True Then

FilterStr = "QryAllPtInfo.NHSnumber = " & txtNHSnumber
Me.Filter = FilterStr

End If

Debug.Print FilterStr

Me.FilterOn = True

Exit_NHSNoSearch:
Exit Sub

Err_NHSNoSearch_Click:
MsgBox Err.Description
Resume Exit_NHSNoSearch

End Sub

 

[Dorian]

Hi Kate,
I don't usually use filters like that, however, if you insert a DLOOKUP()
call before the filter, you can trap the not found condition.
Refer to DLOOKUP function in Access Help
e.g.
If DLookup("NHSNumber","QryAllPtInfo","NHSnumber = " & txtNHSnumber) then
FilterStr = "QryAllPtInfo.NHSnumber = " & txtNHSnumber
Me.Filter = FilterStr
else
msgbox "not found"
end if

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[KateB]

Brilliant Dorian - you solved it!

Just in case its useful to anyone else, the only changes I had to make were
to remove the Dim FilterStr As String line (don't know why but it works!),
and to put the else msgbox clause after the Me.FilterOn line (thanks to a
colleague for solving that part for me!)

Thanks for your help,

Kate