Error Handling & Debugging: How to Properly Handle Errors in Microsoft Access VBA

In this Microsoft Access tutorial, I'll teach you the basics of handling and trapping errors in your databases. The standard VB error messages aren't very user-friendly. Plus, an untrapped error can cause an ACCDE database to just crash and exit. Proper error handling can give friendly, meaningful messages to your users, prevent crashes, and control the flow and execution of your code if an error occurs.

LEARN MORE:

PRE-REQUISITE:

RECOMMENDED COURSES:

TOPICS:
On Error Resume Next
On Error Goto 0
On Error Goto MyErr
Resume Next
Resume MyLocation

BECOME A MEMBER:

ADDITIONAL RESOURCES:

KEYWORDS
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, #fasttips, error handling, on error resume next, on error goto 0, on error goto location, resume next, resume location, Elements of run-time error handling, On Error statement, Error Handling in VBA, Error Handling and Debugging Tips, ms access break on all errors, access vba error handling best practices, access vba try catch

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[dankirchner]

Your YouTube content alone has taught me so much about access. This video is no different. Thank you!

[senorimotor]

I have been really enjoying the seminar series I purchased form Richard (Imaging, Barcode Inventory and Relationships). He has a great teaching style. One of my favorite things is how you can choose fast and 2x the speed for a second watching. One request would be to add 10 second rewind and forward buttons (much like you have on Audible). Thanks for your great work Richard.

[Jojosmith]

thumbs up always before the class even starts

[HistoricMold]

Your videos have been a lifesaver for me. I had like 0 experience with access 2 weeks ago and these videos of yours have essentially been teaching me on the fly. If it's not too much to ask I am struggling with error trapping on a continuous form and would appreciate a tip:

My form kind of works like a sign-out sheet for tools, you enter information provided by combo boxes, select one of 2 radio buttons to state the reason for sign out, and click one of two buttons: one confirms the sign out, and the other marks it as returned. Is there a way to error trap so that a tool can't be signed out if it already has been / until it has been marked as returned?

Thanks for any help in advance if you manage to get around to this comment lol.

[madmaks]

GREAT VIDEO... VERY HELPFUL !! Thanks !

[rabidfollower]

You may need to manually create a run-time error with Err.Raise so that the code doesn't keep running after an error is trapped. On Error only traps errors; it doesn't stop execution. The programmer has the option to stop it or let it run even when an error occurs.

This kind of error handler is basically to add "user friendliness" to your app, so your users won't see incomprehensible error messages. If I make an Access app only I use myself, I don't bother with this. Setting up error handling for an entire app is a major undertaking because you have to code for every single procedure. I wish we had some kind of "global" error handler.

[richpertgen]

Is it possible to use this if I have a macro that is running say 40 make table and append queries on specific csv files so that if one of those files is not in the folder where expected instead of stopping the macro it would just continue on. currently there is no vba in the database. thx

[AdamPennock]

Any Idea what Reserved error (-1104); there is no message for this error and how to fix?

[alexandermedwedew]

On Error Resume Next is the better approach to handling errors. It avoids the spaghetti code of goto and gosub. Errors are handled immediately after they occur. Multiple error types can be identified by using Err.Number and Err.Description. Resume Next is not needed in the code. Err.Clear resets code to continue after the error is dealt with. In the code below the CDate function will cause an error if an invalid date value is processed.

Public Function ConvertDateYYYYMMDD(ByVal strValue As Variant) As String
On Error Resume Next

Dim result As String
result = CDate(Mid(strValue, 5, 2) & "/" & Mid(strValue, 7, 2) & "/" & Mid(strValue, 1, 4))

Select Case Err.Number
Case 13
MsgBox "Not a date" & _
vbLf & Err.Number & _
vbLf & Err.Description
result = ""
Case Else
If CDate(result) < #1/1/2022# Then
MsgBox "Date is too early" & _
vbLf & result
End If

End Select
Err.Clear

ConvertDateYYYYMMDD = result

End Function