Trap Form Errors
By Richard Rost
14 months ago
Trap Form Errors for User-Friendly Notices in MS Access
In this Microsoft Access tutorial, I will show you how to trap form errors and give your users more user-friendly notifications. We will cover how to replace confusing default error messages with clear, understandable ones using the form's on error event. This approach will help your database users know exactly what went wrong without the techy jargon that can scare them off.
Elias from Hawthorne, New Jersey (a Platinum Member) asks: How can I make my Microsoft Access forms show easy-to-understand error messages instead of those confusing default ones that come up? I want my database users to know what went wrong without getting scared off by techy jargon. Do you have any tips or methods to help me handle and customize these error messages in forms?
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, trap form errors Microsoft Access, user-friendly error notifications Access, custom error messages Access forms, Access VBA error trapping, error handling Access forms, duplicate index error Access, field-specific error messages Access, error 3022 Access, active control name Access VBA, acDataErrContinue
Transcript
Today we're going to talk about how to trap form error messages so we can give our users more user-friendly notifications in our Microsoft Access databases. Something like this: "The changes you requested to the table were not successful because they would create duplicate values in the index primary key relationship." What? What does that even mean? Or some of them are just error 322. No idea what that is. So we're going to give them nice friendly ones, like "Error: someone else already has this phone number assigned to them," and then they understand what they did.
Today's question comes from Elias in Hawthorne, New Jersey, one of my platinum members. Elias says, "How can I make my Microsoft Access forms show easy-to-understand error messages instead of those confusing default ones that come up? I want my database users to know what went wrong without getting scared off by TechHelp Jerk. I love that TechHelp Jerk. Do you have any tips or methods to help me handle and customize these error messages in forms?"
Well, yes, of course. There are lots of things you can do. In fact, I've already put together a couple of other videos showing different methods you could deal with this. For example, you could use a before update event for each field and just check it and make sure. But then you have to look it up and see if it's in the table already, and this can get a little cumbersome.
In this video, I show another technique very similar to before update where you don't have to index the field, and you can still check and warn the user if it's in there but allow it anyways. So that's another option for you. But what we're going to do today is a little bit different. We're going to trap the form's on error event, and then we can look and see what error was it, and we can even check which field the user is in and then give them the error message that's appropriate.
But before we get started, this is a developer-level video. What does that mean? Well, you should know some basic VBA. If you haven't done any VBA programming before, watch this video. It'll get you started in about 20 minutes. You should know how to use if-then-else-else-if and end if blocks. Go watch this video if you don't know what that is. These are free videos. They're on my website. They're on my YouTube channel. Watch them and then come on back.
Okay, here I am in my TechHelp free template. This is a free database. You can get a copy off my website if you want to, as well as the video where I show how this is built. So in here, I have a customer form, and customers have phone numbers. Let's say you want to keep phone numbers unique to each customer. No two customers can have the same phone number. So let's go ahead and index that. Let's go to the customer table, right-click, design view, and let's find phone number. We're going to set this to indexed: Yes, no duplicates. I just double-clicked on that by accident. All right, so yes, no duplicates. That means no two phone numbers can be the same.
Let's save it. Close it. Of course, if you already have data in there that violates that rule, you'll get a warning message. Let's make sure you don't have those already. Oh, and if you don't know what indexing is, go watch this video. Basically, it's a method for preventing duplicate values, plus a lot of other stuff that I go over in this video.
All right, so now if I come in here and copy this phone number, and I'll go to the next record. Oh, look, I wanted to bring up this too. Look at this. This is something new that Windows 11 is doing. It's this clipboard thing. This isn't part of Access. This is part of Windows 11. It saw that I copied a phone number, and it wants me to do stuff: Teams or whatever. No, I'm turning this off right now. Go to this little guy here. That's going to bring up your clipboard settings. There's some good stuff in here too. For example, clipboard history. I love this. You press Windows key-V, and you can see the last bunch of stuff that you copied. But I hate this clipboard suggestions. It goes on for dates, times, phone numbers. No, go away. Goodbye.
All right, where was I? Copy that and let's go to a new record and just paste it in there. Now if I try to go to a new record again, you get this big long thing. All right, change it. Your end user is going to look at this and go, "What? What is? I have no idea." Then they're going to call you, and you have to explain it to them. So it would be easier if the database could just explain it to them by itself, right? So I'll hit escape. You cancel that change. Now, you could put a before update event in the phone number field or even in the form and check that yourself, but there's an easier way. Let's go and trap this error in the form's on error event.
Bring up the form's properties. Double-click there. Go to the events tab, and find on error. Right there. This will be the event that runs any time any error is thrown by this form. Let's dot-dot-dot, that will bring us into our code builder. All right, here it is. Form error. We get data error and a response. Basically, it's going to tell you what the error number is and ask how you want to respond to it. We'll talk about these in just a minute.
But let's come in here and just message box "Hi." That's all I want to do right now. All right, save that. Let's come back here. Let's close it. Close it. Open it. Let's do the same thing again. Copy this phone number. Paste it in here and then try to go to a new record. There's my "Hi" because the error event ran and Access is saying, "Oh, you want me to say hi if there is an error. Okay, hi." But you still get the error message. You can give the user a custom message, but you're going to see that one.
Now, I don't want to see the built-in error message. We're going to turn that off. We're going to come in here and say response equals acDataErrContinue. Type it in all lower case and press enter. You should see it camel cased like that with the capital "D" and capital "E". If you don't, then you typed it in wrong. There's no way to Intellisense for this; you just have to know what it is. That's going to be the response. It's going to go back to the form, and that's basically telling the form, "Hey, I don't want to see your built-in default error message."
If I come back here and do it again, I get my "Hi," but I don't get the system error message. Okay. All right, that's a step. Now it'd be nice if I knew exactly what error was thrown, right? What is the problem? What's going on here? Well, each error has a number, every type of error. A duplicate index value is error 3022. How do I know that? Well, we can have message box tell us what the error number is. We can say "message box," "An error has occurred," and then message box that data error. Save it. Oh, there it is. I hit save and initially refreshed this guy. There it is. 3022. There's a bunch of different errors that forms can throw. If you want to know what the number for it is, just do what I did. Just message box it. It'll tell you.
Now that we know what the error is, we can set up a special use case for it. We can come in here and say, if data error equals 3022, right, that's a duplicate index value. We know what that is. Then put a custom message in here. Now at this point, we just know that there's some kind of duplicate index in here, so we'll just give my generic message still message box "Error. You have entered a duplicate value." That's all we know for sure right now. And then we'll go else. If it's anything else, we'll just message box the error number. That'll be useful to you later because then your user is going to call you and say, "Hey, I got an error has occurred 4022. What's that?" Well, then you can look it up and figure what it is. Then you can adjust this.
If I save this now, let's go. "You have entered a duplicate value." That's the same thing. Now taking it even a step further, it'd be nice if we knew what field the user was in because we can tell them an even better error message. Let's come in here. We know it's a 3022. Now I can say if screen.activecontrol.name equals "Phone," then I could be even more specific. Message box "Error. Someone else already has this phone number." Otherwise, we don't know what field you're in, so give them that generic message. See that screen.activecontrol.name? I've got videos on this one too. You can also check the previous control.
Let's save it again. Look at that. See, "Someone else already has this phone number." It knows the field limit, and that's that. You can keep adding different field names if you want to the 3022 errors. You can add different error numbers, all kinds of stuff. That's pretty cool. That's easier than having to look up the table to see if someone else already has that. There are, I mean, a million ways to do everything in Access, but I think this is a pretty efficient way to handle errors on a form level.
If you want to learn more about this stuff, I cover it in my Access Developer 41 class. We spend a good amount of time going over the different form on error event options. This class also covers form zooming in and out, so you can zoom in and out making your forms bigger and smaller. This is really cool stuff too. I'll put a link to everything I mentioned in this video down below. That's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you next time.
A special thank you and shout-out to our diamond sponsors. First, we have Juan Soto with Access Experts software solutions, manufacturing experts, specializing in Access and SQL server. Juan is a 13-time Microsoft Access MVP. You can check them out at accessexperts.com.
Another shout-out to Sammy Shama from Shama Consultancy. Sammy is a certified Microsoft Office specialist, and he not only offers Access application development, but he also provides one-on-one tutoring services. So if you need someone to hold your hand and help you with your Access project, Sammy is your guy. Check them out at shamaconsultancy.com.
TOPICS:
Trapping form error messages in Microsoft Access
Customizing error messages for form errors
Using the form's OnError event
Setting field indexing to prevent duplicates
Suppressing default error messages
Displaying custom messages based on error numbers
Determining which field caused the error
Handling duplicate index values (error 3022)
Using screen.activecontrol for specific error messages
COMMERCIAL:
In today's video, we're going to learn how to trap form error messages in Microsoft Access to provide users with clearer, friendlier notifications. You know those cryptic errors like "The changes you requested were not successful"? We'll replace those with easy-to-understand messages, like "Someone else already has this phone number." I show you how to use the form's on error event to catch these errors and customize the messages based on the specific field and error type. This video is ideal if you know some basic VBA. For more details, visit my YouTube channel and website at the link shown. Live long and prosper, my friends.
Quiz
Q1. What is the primary goal of trapping form error messages in Microsoft Access databases?
A. To disguise errors from the user
B. To convert error messages into different languages
C. To provide user-friendly notifications instead of default, confusing messages
D. To speed up the database processing time
Q2. According to the video, what is an example of a confusing default error message in Microsoft Access?
A. "Invalid input"
B. "The changes you requested to the table were not successful because they would create duplicate values in the index primary key relationship."
C. "Unknown error occurred"
D. "Data entry error"
Q3. What event can be used to trap errors in a Microsoft Access form?
A. Before Delete
B. After Update
C. On Error
D. On Close
Q4. How can a developer prevent the built-in Microsoft Access error message from displaying after an error is trapped?
A. By setting response = acDataErrContinue
B. By setting response = acDataErrIgnore
C. By setting response = acDataErrRetry
D. By setting response = acDataErrAbort
Q5. Which VBA code structure is mentioned as a necessary knowledge requirement before implementing custom error handling?
A. For Each loop
B. Do While loop
C. If-Then-Else-ElseIf-End If blocks
D. Select Case blocks
Q6. In the context of the video, what is an error number 3022 indicative of in a Microsoft Access database?
A. Out of memory error
B. Duplicate index value
C. Syntax error
D. File not found
Q7. What does the following VBA line of code do: "response = acDataErrContinue"?
A. It retries the data operation
B. It logs the error to a file
C. It prevents the built-in error dialog from showing
D. It aborts the data operation
Q8. How can the specific field where an error occurred be identified in a form's On Error event?
A. Using Me.Fields("FieldName")
B. Using Screen.ActiveForm.Name
C. Using Screen.ActiveControl.Name
D. Using Forms("FormName").Controls("ControlName")
Q9. In the video, what is suggested as another method to ensure data uniqueness outside of handling On Error events?
A. Creating a union query
B. Using Before Update event to check data
C. Adding default values to fields
D. Applying form-based macros
Q10. What is one advantage of using custom error messages in Microsoft Access forms as explained in the video?
A. Allows database to process data faster
B. Increases database security
C. Reduces user frustration and support calls
D. Facilitates automatic error correction
Answers: 1-C; 2-B; 3-C; 4-A; 5-C; 6-B; 7-C; 8-C; 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 TechHelp tutorial from Access Learning Zone focuses on how to trap form error messages in Microsoft Access to provide more user-friendly notifications.
Imagine your users encountering a message like, "The changes you requested to the table were not successful because they would create duplicate values in the index primary key relationship." That's confusing, right? Or sometimes, they might just see an error code like "Error 322," which doesn't help them understand the problem. Instead, we can provide clearer messages, such as, "Error: someone else already has this phone number assigned to them," making it easier for users to know what went wrong.
Elias from Hawthorne, New Jersey, asked how to customize these error messages in forms to make them less intimidating. I'm happy to share that there are many ways to achieve this. For example, you could use a BeforeUpdate event to check each field, but that method can be cumbersome. Instead, we'll use the form's OnError event to identify errors and display appropriate messages.
Before getting started, note that this is a developer-level tutorial, so some basic knowledge of VBA is required. If you're new to VBA, I have introductory videos on my website and YouTube channel which can help you get up to speed.
Let's work with a customer form where each customer should have a unique phone number. First, we'll need to index the phone number field to prevent duplicates. Open the customer table in Design View, find the phone number field, and set it to "Indexed: Yes (No Duplicates)." Save and close the table. If there are already duplicate values, you'll need to resolve those first. If you're unfamiliar with indexing, I have more resources on this topic available for you to watch.
Now, we'll handle potential errors by trapping them in the form's OnError event. Open the form's properties, go to the Events tab, and select OnError. This event runs whenever an error occurs in the form. We'll use the code builder to write a simple message box that displays when an error is trapped.
Initially, let's just show a message saying "Hi" to confirm the error trapping is working. After saving and testing, you'll see the "Hi" message on error occurrence while still seeing the default error. To customize this, we'll set the response to acDataErrContinue, which tells Access to suppress its standard error message.
Next, we'll add functionality to display the specific error number using a message box. Error 3022 indicates a duplicate index value. Knowing this, we can create a nuance in our message depending on the error type. For error 3022, we'll provide a generic duplicate value message.
To make the error message more specific, we can check which control is active when the error occurs. Using screen.activecontrol.name, we can identify the field causing the error and give a tailored message like "Error: Someone else already has this phone number."
This approach makes it easier for users because they receive clear, relevant messages without seeing technical error codes. You can expand this by handling more error numbers and different fields, offering a comprehensive error-handling strategy.
If you want to dive deeper into error handling and other advanced form features, my Access Developer 41 class covers these topics extensively. The class also explores additional functionalities like zooming in and out of forms.
For those interested, the complete video tutorial with step-by-step instructions is available on my website at the link below. Live long and prosper, my friends.
Topic List
Trapping form error messages in Microsoft Access
Customizing error messages for form errors
Using the form's OnError event
Setting field indexing to prevent duplicates
Suppressing default error messages
Displaying custom messages based on error numbers
Determining which field caused the error
Handling duplicate index values (error 3022)
Using screen.activecontrol for specific error messages
|
