Replacing an Access Error with a Custom Message

fabi2004
fabi2004 used Ask the Experts™
on
Could someone help me write an error handler please?  I feel like I've tried every which way and I'm just not getting the code right.

I have a button on a form that calls a Sub

Sub cmdSearch_Click
     Call Search
End Sub

Sub Search ()
    Dim strCriteria, ... ... ...
'''---lotsa code---
'''...
task = "select * from [qryFinancialsByMonth2] where (" & strCriteria & ")"
    Me.FilterOn = True
    DoCmd.ApplyFilter task
End Sub

If the strCriteria is blank when the task filter is applied, then Access says "Run-time error 3075, Syntax error missing operator in query expression..."

I'd like to replace that MsgBox with one saying "Please select at least one criteria for the search." with a simple OK button to click.


As I said, I've tried several different things.  I'm not sure any of them are worth posting.  But here's one of them:
On Error GoTo Error_Search

If IsNull strCriteria Then
MsgBox "Please select at least one criteria to search for.", vbOKOnly
End If
Exit Sub
'''...
Error_Search:
If Err.Number = 3075 Then
Resume Exit_Sub
End If 
End Sub

Open in new window


Much appreciate any help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
do this

if  strCriteria  & ""="" then  'nothing was selected
   msgbox "Please select at least one criteria for the search.", vbOKOnly
   exit sub
else
   task = "select * from [qryFinancialsByMonth2] where (" & strCriteria & ")"
    Me.FilterOn = True
     DoCmd.ApplyFilter task

end if

end sub

Open in new window

Author

Commented:
Rey, that was fast!!!  Thank you so much!  I never hit on the right one.  I really appreciate your help.
Top Expert 2016

Commented:
you are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial