Solved

If an insert statement does not run correctly

Posted on 2011-09-19
4
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 36561007
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
ID: 36561496
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
ID: 37097855
thank you!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 37097872
Glad to help :)
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

705 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