• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1876
  • Last Modified:

Currentdb.Execute - Return Error if SQL does not process

I'm trying to run some INSERT and UPDATE queries using CurrentDb.Execute.  I've noticed a problem, however... if there is an error in the SQL and the SQL cannot be processed, there's no way that I've seen to trap the error.  If I use DoCmd.RunSQL instead, I can see the error message if SetWarnings is set to True; but not if it's set to False.  I don't want an access message popping up in my application regardless, though.

Is there any way to trap errors when CurrentDb.Execute does not execute my SQL properly?  I want to be able to notify my users that the information did not save, and I'm using a custom (unbound) form that runs multiple INSERT/UPDATE queries when the save button is clicked.
0
SeanStrickland
Asked:
SeanStrickland
  • 3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Public Function mFx()


On Error Goto mFxErrTrap
CurrentDb.Execute strSQL, dbFailOnError

'more code

mFxExit:
Err.Clear
Exit Function

mFxErrTrap:
   msgbox "Some Error"
   Goto mFxExit
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
That is the basic drill ... you need to use the dbFailOnError  ... and put in your error trapping code - to whatever degree you need ... specific errors you expect, etc.


Public Function mFx()
Dim strSQL
strSQL = "Your sql statement ........"

On Error Goto mFxErrTrap
CurrentDb.Execute strSQL, dbFailOnError

'more code

mFxExit:
Err.Clear
Exit Function

mFxErrTrap:
   msgbox "Some Error " & Err.Number & "  " & Err.Description
   Goto mFxExit

End Function   ' forgot this
0
 
SeanStricklandAuthor Commented:
I forgot all about the dbFailOnError piece.  I've used it before, but it's been a while.  Let me make sure it works before I give you the points. :)

I'll try to test tomorrow when I'm back in the office.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
It works ... I *promise* ... I use it everywhere.  I rarely use SetWarnings, etc.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now