Error Handling
By Richard Rost
4 years ago
Error Handling & Debugging in 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.
Pre-Requisites
Recommended Courses
Topics
- On Error Resume Next
- On Error Goto 0
- On Error Goto MyErr
- Resume Next
- Resume MyLocation
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
Subscribe to Error Handling
Get notifications when this page is updated
Intro In this video, we will talk about error handling in Microsoft Access VBA, including how to prevent runtime errors from confusing users, ways to use commands like On Error Resume Next and On Error GoTo to handle problems gracefully, and different techniques for managing the flow of your code when unexpected issues occur. We will also look at practical examples of handling errors in forms, providing friendly messages, and resuming code execution after an error.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we're going to take a look at error handling - how to properly handle errors in your Microsoft Access VBA. Yes, this video is a little more advanced for the developers out there. If you've never done any programming in VBA before, go watch this video, my intro to VBA. It's about 20 minutes long. It'll teach you everything you need to know to get started programming with VBA.
For the rest of you, let's continue on.
So what is error handling? Well, error handling is basically putting stuff in your code to avoid things like this. This runtime error 3075. Errors like this are very user-unfriendly. Your end user is going to get this and go, uh, what?
Plus, if you've compiled your database and made an ACCDE file, which you should before you give your end users your database, this won't show up. Your database will just crash. It'll just crash, exit Access, and no one will have any idea what's going on because it won't go into any kind of debug mode if you're not using the full version of Access.
Proper error handling can give friendly, meaningful messages to your users like, "Oopsie. Sorry, this button isn't working right now," prevent crashes, and control the flow and execution of your code if an error occurs.
Let's take a look at a very simple example of when something like this happens. Got a customer form. Click on either orders or contacts. It opens up another form to show their orders or their contacts.
But what happens if the user goes to a blank new record? Customer ID is null. So if I try to see their orders, there's my syntax error: query expression customer ID equals null. And if I hit debug right now, which you don't want your end users ever doing, it takes you to this line and you see it's all in yellow. If you hold your mouse over customer ID, it'll say that it's null, so there's your problem.
I'm going to stop the debugger.
Now, there are a couple of different ways to handle this. First, you could try handling it in the code itself by checking that value. If IsNull(CustomerID), then Exit Sub. Or, you can give them a message box and say, "Hey, you don't have a customer," but at least this will exit out. If you come in here now, hit the button - nothing happens.
But for the sake of argument, for the sake of class, we're not going to do that. We're going to use some actual error handling commands. Yes, usually you'll use error handling commands in stuff that's a lot more complicated than this, but this is a simple example for demonstration purposes.
So the first thing you could do is basically tell Access, if you run into any kind of errors, I don't care what it is, just ignore it and continue running the code. That command is On Error Resume Next. That says, you hit an error, I don't want to hear about it, just keep going. That's fine for simple things like this where you've got like one or two lines of code in here.
Come in here and hit the button, and you don't get an error message. That'll handle anything, not necessarily just a customer ID that's null. If you've got a couple of different lines of code in here, you don't have to check for each different circumstance. Now, it's not going to give the user an error message, but at least nothing bad will happen like your program crashing.
I will only use an "On Error Resume Next" in very, very small blocks of code where it's going to either do something very simple or exit out. I don't use it in big, long procedures if they've got lots and lots of stuff in them because it just gets crazy.
Now, if you do have a lot of other code after this, let's say you've got this and you've got a whole bunch of other stuff down here, more lines of code. You just want to get away from this one line causing a problem, but you might not be able to check to see what it is. You can do that, and then you can say after it "On Error GoTo 0." "GoTo 0" basically turns that error handling off.
I use this a lot with lookups where I don't necessarily know if it's going to return a weird value and Nz doesn't handle it. You can try your DLookup. You set your value up here, try your DLookup. But here you'd say, "Okay, ignore the next line if it causes a problem, do it," and then right here it turns that error handling back off, so you can continue normal execution.
Now, On Error Resume Next isn't super friendly. What you can do, what I prefer doing, is I like saying "On Error GoTo MyError" or whatever, "myLocation," whatever you want to call it. Then somewhere else down in your code, you can say "MyError:" put a colon after it. Now, this location in your code is a spot that any error will jump down to if it hits it.
So if this line generates an error, it's going to immediately jump down here. Here you can say MsgBox "An error was generated." That's going to say "Okay, don't cause a problem with this if an error happens, just jump down here."
What you have to be careful of, though, is if an error isn't generated, it's still going to pass this and jump down here. So, before that, you have to put in an "Exit Sub" or "Exit Function." You want to run this and this and this, whatever other lines of code you've got in here, and then if everything is okay, exit the sub. Down here you put your errors, and you can have multiple error locations.
You could say, "Okay, if this one generates an error, go to MyError," and if you get another one after that, "If this one generates an error, go to MyError2," or whatever you want to call it. But we'll just stick with the one for right now.
Save that. If I come in here and run it now, if I go over here and there is no error, it opens up nicely. But if I go to a blank record and I hit the button, oh, "An error was generated." You can give the user a more elaborate description, whatever you want to say in here, and you can use the different options and titles and critical and all that stuff in the message box.
Now, if you've got lots more stuff in here, like let's say after you run that piece of code, you want to also message box "Everything is okay." So you want to open that form and then you want to message box "Everything okay."
The way this is running right now is it'll generate the error, drop down to here, and then finish. So if I come in here now and I run this, "An error was generated" and that's it.
But if you wanted to say, just give me the error for this message, but then continue executing the rest of the subroutine, you can say down here "Resume Next." What that'll say is, okay, resume execution starting with the next line of code. So you'll get your error message, but then the rest of the sub will run. That's up to you. If everything else in here has to go too and then you just want to exit out.
Here we go. Error was generated, but everything's okay. So you prevented it from crashing, but you still got "Everything's okay."
Yes, you can actually resume at specific locations too. If you've got multiple different things in here and you want to say, if this error is generated, jump back to this location, you could say "MyLocation" here, and then make something else appear called "MyLocation," like that.
So, if an error happens with this line, it's going to jump down to MyError, give you the error message, resume with MyLocation, give you "Everything's okay," and then exit the sub. See how you can bounce around?
You guys remember the old programming languages? Like old BASIC? I started on a TRS-80 with old Tandy CoCo BASIC. It was "10 PRINT," "20 GOTO 10," and you could loop around using line numbers. These are kind of like line numbers. They're line locations, kind of.
Now if I run this, an error is generated, everything's okay, and it exits out. It goes from here to here, back to here, does this, and then exits.
This is really all just based on the flow of your program - how you want it to handle. I've got hours and hours of different lessons covering different uses for these things. I start off with basic debugging and error handling in Access Developer Level 2. Then I spend a lot more time with advanced debugging in Developer 15. We actually spend a lot of time with different error handling setups, using the Immediate Window, watches, breakpoints, all kinds of stuff.
So this right here is just scratching the surface of all kinds of stuff you can do with these things too. There are lots and lots of ways to debug your code and to set up error handling.
So there you go, there's your fast tip for today. I hope you learned something.
Check out my developer courses for lots more information on error handling and debugging.
We'll see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free Expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free Developer class each month after you finish the Expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond Sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.Quiz Q1. What is the main purpose of error handling in Microsoft Access VBA?
A. To improve the speed of database queries
B. To avoid user-unfriendly runtime errors and program crashes
C. To make forms look more visually appealing
D. To automatically save all records
Q2. What does the command "On Error Resume Next" do in VBA?
A. It stops code execution when an error occurs
B. It jumps to a specific error handling location
C. It ignores the error and continues executing the next line of code
D. It displays a default error message to the user
Q3. In what situations is it recommended to use "On Error Resume Next"?
A. Large procedures with complex logic
B. Small code blocks where an error should be ignored or the code should exit
C. When you want to jump to multiple error handlers
D. When displaying custom error messages to users
Q4. What does the VBA command "On Error GoTo MyError" accomplish?
A. It ignores all errors
B. It jumps to a label called MyError when an error occurs
C. It restarts the procedure from the beginning
D. It stops execution without any message
Q5. Why is it important to use "Exit Sub" or "Exit Function" before your error handler label?
A. To skip the remaining code and prevent unintended execution of the error handler when no error has occurred
B. To reset all variables in your code
C. To automatically save the database file
D. To go back and check the previous code for errors
Q6. What effect does the command "On Error GoTo 0" have?
A. It turns error handling off, restoring default error reporting
B. It creates a new error handler at the start of the procedure
C. It sends all errors to the Immediate Window
D. It restarts the error handler at line 0
Q7. What does the "Resume Next" statement do after encountering an error and running the error handler?
A. Ends the procedure immediately
B. Begins execution from the error handler label again
C. Continues execution with the line following the one that caused the error
D. Causes the program to crash
Q8. When would you use multiple error handling labels like "MyError" and "MyError2" in your code?
A. When you want all errors to be ignored
B. When you want to handle different errors in different ways
C. To reduce code complexity
D. To avoid writing message boxes
Q9. What is an advantage of providing custom error messages to users instead of letting the default Access runtime errors appear?
A. It makes errors harder to understand
B. It gives friendly, meaningful messages and prevents crashes
C. It increases the occurrence of errors
D. It prohibits debugging opportunities
Q10. What Access database file format should you use before deploying your application to end users to prevent exposing the code?
A. MDB
B. ACCDB
C. ACCDE
D. TXT
Answers: 1-B; 2-C; 3-B; 4-B; 5-A; 6-A; 7-C; 8-B; 9-B; 10-C
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on error handling in Microsoft Access VBA. This lesson is intended for those with some experience in VBA programming. If you are new to VBA, I recommend that you start with my introductory video where I cover all the fundamental concepts you will need to get started.
For those already familiar with VBA, let us move forward.
Error handling is a technique used in your code to prevent unfriendly runtime errors, such as error 3075, from appearing to end users. These errors are confusing and provide little useful information to the user. Furthermore, if you have compiled your database into an ACCDE file for distribution, these errors will not even present a debug window; instead, the database will simply crash and close Access. This leaves users puzzled and unable to determine what happened.
Implementing error handling allows you to present users with friendly, easy-to-understand messages like, "Sorry, this button isn't working right now." It can also prevent crashes and help you control the flow of your code when an error occurs.
Let us look at a practical example. Imagine you have a customer form with buttons to open related orders or contacts. If a user tries to perform this action when a new, blank record is selected and there is no CustomerID, an error occurs since you cannot perform a lookup with a null value. Instead of exposing your end users to a confusing debug interface, proper error handling will make your application far more robust.
One way to handle the issue is by checking for null values in your code. For instance, you could use a conditional statement to exit the subroutine if the CustomerID is missing. You might even display a simple message indicating that no customer has been selected. However, for the purposes of instruction, I want to demonstrate how to use actual error handling commands.
The simplest error handling command is On Error Resume Next. This tells Access to ignore any errors it encounters and continue executing the code. While this is effective for blocks of code that have only a line or two, it does not provide users with any message about what went wrong. Still, it prevents Access from crashing, which is a step in the right direction.
It is important to limit On Error Resume Next to small, simple procedures. In lengthy, complex routines, silently ignoring all errors can lead to unpredictable outcomes.
If you have additional code following the operation that might generate an error, you can reset error handling by issuing On Error GoTo 0 after the risky segment. This tells Access to revert to normal error handling from that point onward. This approach is especially useful when using database lookups like DLookup where unpredictable values might arise.
A more refined method is to use structured error handling with a specific error handler. For example, On Error GoTo MyError designates a labeled section in your code to handle any errors that occur. When an error is triggered, VBA immediately jumps to that label, where you can display a message box or log the error. If no error occurs, you must ensure that control does not proceed into the error handler by inserting an Exit Sub or Exit Function before the label.
You can add as many error handlers as needed by using different labels, although in most cases a single handler will suffice.
You might also want to continue executing code following an error, rather than exiting the subroutine entirely. To do this, use Resume Next within the error handler. This allows your program to show a message and then pick up execution on the line following the one that caused the error. Alternatively, you can specify another label to resume execution at a specific location. This flexibility gives you fine control over the sequence and flow of your routines.
These techniques are reminiscent of older programming practices, such as placing line numbers in code blocks and jumping between them. In modern VBA, labels and error handling statements fulfill a similar function, enabling you to manage the program's flow effectively.
Error handling is a large topic. In my more advanced courses, especially Access Developer Level 2 through Developer Level 15, I cover many aspects including using the Immediate Window, setting watches, breakpoints, and various error handling setups. The material presented today is just an introduction to a much broader set of tools and strategies that you can use to build professional, user-friendly, and stable Access applications.
If you are interested in more comprehensive training on debugging and error handling, take a look at my developer courses. They cover these topics in detail along with many other advanced techniques.
Thank you for joining me for this Fast Tips lesson. You can always find more resources and a complete video tutorial with step-by-step demonstrations of everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Definition of error handling in VBA
Consequences of unhandled errors in Access ACCDE files
Displaying user-friendly error messages
Example of error caused by null values
Checking for null values before running code
Using On Error Resume Next for simple error handling
Turning off error handling with On Error GoTo 0
Using On Error GoTo with custom error labels
Providing custom messages with MsgBox on errors
Exiting subroutines to avoid unintended code execution
Using Resume Next to continue after an error
Resuming at custom locations with Resume [label]
Controlling program flow with error handling blocks |
