Solved

If an insert statement does not run correctly

Posted on 2011-09-19
4
246 Views
Last Modified: 2012-05-12
I have written some VBA code that inserts information into a table.  I was curious if anyone could help me figure out how to catch the case that the insert fails, for any reason, and end the Sub so that everything else does not blow up.  Thanks for any help!
0
Comment
Question by:uconnfb13
  • 3
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
Comment Utility
Run your insert using CurrentDB.execute, and use error handling:

On Error goto EH
CurrentDb.Execute strSQLYourInsertQuery, dbFailOnError

' Other code

' Thse should be at the very end of your function
Exit Sub ' (or function) <--- the point is to exit gracefully if there is no error

EH:
  msgbox "ERROR " & err.number & ": " & err.descriptiom
End Sub

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Typo in my code above...
<<  msgbox "ERROR " & err.number & ": " & err.descriptiom  >>

Should be:
msgbox "ERROR " & err.number & ": " & err.description

Also, If you don't want to see the actual error message, you could also do something along these lines for the error handler:

EH:
  msgbox "Insert failed"
End Sub
0
 

Author Closing Comment

by:uconnfb13
Comment Utility
thank you!
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Glad to help :)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now