Avatar of fabi2004
fabi2004
Flag for United States of America asked on

Replacing an Access Error with a Custom Message

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.
Microsoft Access

Avatar of undefined
Last Comment
Rey Obrero (Capricorn1)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
fabi2004

ASKER
Rey, that was fast!!!  Thank you so much!  I never hit on the right one.  I really appreciate your help.
Rey Obrero (Capricorn1)

you are welcome!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck