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

[@dankirchner4460]

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

[@599CD]

Happy to hear that!

[@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.

[@599CD]

Thanks for the kind words. Yeah, I'll be adding more functionality to that video player as soon as I can.

[@HistoricMold440]

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.

[@Jojosmith342]

thumbs up always before the class even starts

[@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.

[@599CD]

Indeed!

[@Souhila-yi8qk]

Thank you for the interesting Video

[@madmaks152]

GREAT VIDEO... VERY HELPFUL !! Thanks !

[@599CD]

Welcome

[@richpertgen3005]

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

[@599CD]

Macro? Meh. Convert that to VBA, then yes.

[@AdamPennock]

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

[@599CD]

That could be a lot of things.

[@AdamPennock]

@@599CD I figured out the problem a network drive had stopped working causing the error to come up

[@dawoodacademy]

So nice Sir!

[@599CD]

Thanks

[@alexandermedwedew7219]

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

[@599CD]

I usually do stick to On Error Resume Next, but sometimes you may want different error handlers for different issues. It's good to have options.