By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,583 Members | 3,481 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Custom Error Messages in Access

hyperpau
Expert 100+
P: 184
Before anything else, I am not a very technical expert when it comes to VBA coding.
I learned most of what I know by the excellent Access/VBA forum from bytes.com (formerly thescripts.com).
Ergo, I will be writing this article intended for those who are in the same level, or maybe lower, of my technical knowledge.
I would be using layman's words, or maybe, my own words as how I understand them, hoping, you will understand it the same way that I do.


TYPES OF ERRORS
We need to understand that there are different types of errors in MS-Access and I am not fully aware of the real technical names of these errors, but from my experience, I only, most of the time, encounter two types of errors that I always trap and make customer error messages for.
I would like to refer to these errors as 1) Form Errors, 2 ) Sub Procedure Errors.

* Form Errors
I call this Form Errors because these errors normally occur during form validation. Meaning, there is no actual Sub Procedure or VBA code executed for the error to be triggered.

This errors are triggered by either a form or a control losing focus, or a form or control being updated.
There are a lot of possible causes but to name a few, here are the common examples.

* A field set as Indexed (No Duplicates) but a duplicate value has been supplied.
* The field is set to Required but no data has been entered for that field
* The field has been set with an Input Mask but the data entered does not match the Input Mask requirements

There are other examples but these are the most common ones I normally trap errors for.
This classifies under Form Errors because there is no specific button clicked, or procedure called for the error to be triggered.
Most of the time, this can be triggered by moving to the next, or a new, record, or by moving focus from a Main Form to its subform.
Both actions causes the record to be updated or saved, thus, if there are validation requirements, such errors are triggered.

* Sub Procedure Errors
I call this Sub Procedure Errors bec from most projects I make, these errors are triggered by executing a code in vba, whether I programmed the codes myself, or used the Wizard to create it for me. This is the reason why I also call this Button errors because most of the time (not all), I put sub procedure vba codes on the On Click event of a button.

The errors are triggered almost exactly by the same validation reasons as the mentioned above with the Form Errors, only that it is not necessarily that the record is updated by leaving the form, or moving to another, or new, record, or moving focus from a main form to a subform.

Say for example, I have set a field property to Required. If I do not put any data in that field, then a validation error is triggered when the record is saved. In this case, if i move to a different record, then that's the time the record is updated or saved, then the Form Errors from above will be triggered.
But what if I want the users to click a Save Button which should actually check or validate data input?

In this example, if I leave the required field with no data, then click the Save button, then the Form Errors are not triggered, but instead, the sub procedure
error that I have coded on the On Click event of my Save button. So If i dont' click that button (then sub procedure will not be called), then the error will not be triggered because data validation will not occur. Not unless I close the form, or may save it using the Menu Bar, or use the Navigation button or Mouse Scroll to move to the next record - which will trigger the Form Error and not the Sub Procedure Error (button error).
Are we getting some light here?


HOW TO TRAP THE ERRORS
Since in this article, I cited two types of errors, then you should already know that trapping this Errors are done differently.
The method is similar but where to place them and how to identify them differs.

To trap Form Errors, you can use the On Error Event of the form.
To trap Sub Procedure Errors, you need to place that error handler on a sub procedure level.

The Form Error are trapped using the DataErr variable.
The Sub Procedure Error are trapped using the Err.Number Constant and propety.

My method to trap the errors is to use the MsgBox to tell me what the error number is.
After Identifying the error number, then I can use the If..Then..Else or Select Case statements to customize customer error messages
depending on the error number triggered.

** TRAPPING FORM ERRORS
On the On Error event of the form, enter these codes.

Expand|Select|Wrap|Line Numbers
  1. Private Sub frmMyForm_Error (DataErr as Integer, Response as Integer)
  2. MsgBox "Error No.:" & DataErr
  3. End Sub
The MsgBox will tell you the Error Number, and after clicking OK on that Message Box, the Default Error Message will appear.

Now, do a trial entry on the form and trigger the error. In this example, I will intentionally leave the Required field blank and then move on to a new record. The Error will be triggered and I would receive my Message Box with the message: 'Error No.: 3314'.
I click OK on that and then I will see the default MS Access Error Message: "The field MyField can't contain a Null value because the Required property for this field is set to True. Enter a value in this field."

Not that I know the error number and the description of the error, I will then trap it and assign it a customer error message.
To do this, I will use this codes.

Expand|Select|Wrap|Line Numbers
  1. Private Sub frmMyForm_Error (DataErr as Integer, Response as Integer)
  2. If DataErr = 3314 Then
  3.     MsgBox "MyField is required.  Please enter a data in this field."
  4. Else
  5.     MsgBox "Error No.:" & DataErr
  6. End Sub
Now, I will try it again. This time, when I move to a new record leaving the requried field blank, I would see an error message:
'MyField is requried. Please enter a data in this field.' which is my custom error message.
After I click OK on that, then the default access error message appears again. Now that's a problem! I don't want users to see this anymore, I just want them to see my custom error message. So I would need to supress that default error message. To do that, I will use the Response variable and the constanct acDataErrContinue right after my custom error message.

Expand|Select|Wrap|Line Numbers
  1. Private Sub frmMyForm_Error (DataErr as Integer, Response as Integer)
  2. If DataErr = 3314 Then
  3.     MsgBox "MyField is required.  Please enter a data in this field."
  4.     Response = acDataErrContinue
  5. Else
  6.     MsgBox "Error No.:" & DataErr
  7. End If
  8. End Sub
The Response = acDataErrContinue simply tells access not to show the defaul error message anymore.
If the Error triggered is not 3314, then I would see the Error Number, and the default error message again.
So it's really up to you now how many errors you think can be triggered that needs to be assigned with a custom error message.

If you ask me, I find these errors as the most common ones that are triggered in my projects:
Expand|Select|Wrap|Line Numbers
  1. 2107
  2. The value you entered doesn't meet the validation rule defined for the field or control.
  3. To see the validation rule, click Design view, click the appropriate field,
  4. and then, if the property sheet isn't open, click the Properties button on the toolbar.
  5. Then click the Data tab.
  6. To solve this problem, enter a value that meets validation rule, or press ESC to undo your changes
  7. 2113
  8. The value you entered isn't valid for this field.
  9. For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.
  10. 2169
  11. You can't save this record at this time.
  12. Microsoft Access may have encountered an error while trying to save a record.If you close this object now, the data changes you made will be lost.Do you want to close the database object
  13. anyway?
  14. 2237  - (can be replaced by the On Not In List event of a combobox/listbox)The text you entered isn't an item in the list.
  15. Select an item from the list, or enter text that matches one of the listed items.
  16. 3022
  17. The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate values and try again.
  18. 3200
  19. The record cannot be deleted or changed because table <name> includes related records.
  20. 3201
  21. You can't add or change a record because a related record is required in table <name>.
  22. 3314
  23. The field <name> can't contain a Null value because the Required property for this field is set to True. Enter a value in this field.
  24. 3315
  25. Field <name> can't be a zero-lenght string
  26. 3316
  27. <Table-level validation text>.
  28. 3317
  29. One or more values are prohibited by the validation rule <rule> set for <field name>. Enter a value that the expression for this field can accept.
Since, I would like to make sure that in any case these errors are triggered, my Database would show my custom error message, then I would use the Select Case statement in my Form_Error Event.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Error (DataErr as Integer, Response as Integer)
  2. Select Case DataErr
  3.     Case 2107
  4.         MsgBox "This is my custom error message for Error No 2107"
  5.     Case 2113
  6.         MsgBox "This is my custom error message for Error No 2113"
  7.     Case 2169
  8.         MsgBox "This is my custom error message for Error No 2169"
  9.     Case 2237
  10.         MsgBox "This is my custom error message for Error No 2237"
  11.     Case 3022
  12.         MsgBox "This is my custom error message for Error No 3022"
  13.     Case 3200
  14.         MsgBox "This is my custom error message for Error No 3200"
  15.     Case 3201
  16.         MsgBox "This is my customer error message for Error No 3201"
  17.     Case 3314
  18.         MsgBox "This is my customer error message for Error No 3314"
  19.     Case 3315
  20.         MsgBox "This is my customer error message for Error No 3315"
  21.     Case 3316
  22.         MsgBox "This is my customer error message for Error No 3316"
  23.     Case 3317
  24.         MsgBox "This is my customer error message for Error No 3317"    
  25.     Case Else
  26.         MsgBox "This is an unexpected error.  Please report this to the administrator."
  27. End Select
  28. Response = acDataErrContinue
  29. End Sub
HANDLING FORM ERRORS GLOBALLY
I am not sure if I used the term globally right, but what I meant by this is I want to make sure all errors in all my forms are handled by my cutsomer error messages.

One way to do this is to copy and paste thist error codes to each of my form's On Error event.
The problem is I am very particular in the way my VBA codes appear in each of my form's module and I dislike seeing the same codes over and again in each form's module. I want my form modules to be as neat as possible so I ventured my way into learning standard modules.
With the help of FishVal, I finally learned it.

What I want is to just type this customer error messages once and just call it from each of my form's module.
So i will create a new module and named it MyCodes.

I then write a Public Procedure having this custome error handling so that I can call it from any form in my project. So on MyCodes module, I have this Sub procedure.

Expand|Select|Wrap|Line Numbers
  1. Public Sub FErrorHandler (ByVal DataErr as Integer)
  2.  
  3. Select Case DataErr
  4.     Case 2107
  5.         MsgBox "This is my custom error message for Error No 2107"
  6.     Case 2113
  7.         MsgBox "This is my custom error message for Error No 2113"
  8.     Case 2169
  9.         MsgBox "This is my custom error message for Error No 2169"
  10.     Case 2237
  11.         MsgBox "This is my custom error message for Error No 2237"
  12.     Case 3022
  13.         MsgBox "This is my custom error message for Error No 3022"
  14.     Case 3200
  15.         MsgBox "This is my custom error message for Error No 3200"
  16.     Case 3201
  17.         MsgBox "This is my customer error message for Error No 3201"
  18.     Case 3314
  19.         MsgBox "This is my customer error message for Error No 3314"
  20.     Case 3315
  21.         MsgBox "This is my customer error message for Error No 3315"
  22.     Case 3316
  23.         MsgBox "This is my customer error message for Error No 3316"
  24.     Case 3317
  25.         MsgBox "This is my customer error message for Error No 3317"    
  26.     Case Else
  27.         MsgBox "This is an unexpected error.  Please report this to the administrator."
  28. End Select
  29.  
  30. End Sub
After this, I would still need to code each form's On Error event. To call it from my Form, I use this codes:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Error (DataErr as Integer, Response as Integer)
  2. MyCodes.FErrorHanlder (DataErr)
  3. Response = acDataErrConitnue
  4. End Sub
Yes! I would still need to do this on every form module, but it would be better this way. First, because it looks more neat, and more importanly, If I would be changing an error message, or maybe adding a new DataErr, I would just have to edit the Public Procedure in my MyCodes module than do it on each and every form I have in my project.


**TRAPPING SUB PROCEDURE ERRORS
The only way, for me, to trap these errors I normally want to call Button Errors is by using the Sub Level On Error Property.
In this example, I have a command button named cmdSave that saves the record and validates the data and tells the user if validation is successful.
The coding should be something like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2. DoCmd.RunCommand acCmdSaveRecord
  3. MsgBox "Changes saved successfully."
However, if I have missed any required fields, or maybe trigger other errors, my Form Error handler will not handle it.
So I need to tell VBA that if errors are encountered, I would want a custom error message.
As I have mentioned before, on Sub Procedure Errors, we use Err.Number instead of DataErr to trap procedure level errors:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2. On Error GoTo ErrorHandler
  3.  
  4. DoCmd.RunCommand acCmdSaveRecord
  5. MsgBox "Changes saved successfully."
  6.  
  7. ExitErrorHandler:
  8. Exit Sub
  9.  
  10. ErrorHandler:
  11.     MsgBox "Error No.:" & Err.Number
  12.     Resume ExitErrorHanlder
  13.  
  14. End If
Now I would leave my required field blank again and this time, instead of moving to a new record, I will click my Save button. I then see the message 'Error No.: 3314' then the default access error message.

For customer error messages again, I would use the If..Then..Else or Select Case Statement, Then the acDataErrContinue constant
to suppress the default error messages.
I will go straight to Select Case example because I want all possible errors handled when I click my button.
So my coding would be like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2. On Error GoTo ErrorHandler
  3.  
  4. DoCmd.RunCommand acCmdSaveRecord
  5. MsgBox "Changes saved successfully."
  6.  
  7. ExitErrorHandler:
  8. Exit Sub
  9.  
  10. ErrorHandler:
  11. Select Case Err.Number
  12.     Case 2107
  13.         MsgBox "This is my custom error message for Error No 2107"
  14.     Case 2113
  15.         MsgBox "This is my custom error message for Error No 2113"
  16.     Case 2169
  17.         MsgBox "This is my custom error message for Error No 2169"
  18.     Case 2237
  19.         MsgBox "This is my custom error message for Error No 2237"
  20.     Case 3022
  21.         MsgBox "This is my custom error message for Error No 3022"
  22.     Case 3200
  23.         MsgBox "This is my custom error message for Error No 3200"
  24.     Case 3201
  25.         MsgBox "This is my customer error message for Error No 3201"
  26.     Case 3314
  27.         MsgBox "This is my customer error message for Error No 3314"
  28.     Case 3315
  29.         MsgBox "This is my customer error message for Error No 3315"
  30.     Case 3316
  31.         MsgBox "This is my customer error message for Error No 3316"
  32.     Case 3317
  33.         MsgBox "This is my customer error message for Error No 3317"    
  34.     Case Else
  35.         MsgBox "This is an unexpected error.  Please report this to the administrator."
  36. End Select
  37. Response = acDataErrContinue
  38. Resume ExitErrorHanlder
  39.  
  40. End If
HANDLING SUB PROCEDURE ERRORS GLOBALLY
Now I don't have to worry about my errors. The only problem I deal now is, again, I would like my codes to be as neat as possible.
More Importantly, I have like 20 forms that has a save button that does the same command and I do not want to copy paste the lengthy code to each of them.
So I go back to my module MyCodes and create another public procedure.

Expand|Select|Wrap|Line Numbers
  1. Public Sub PErrorHandler()
  2. Select Case Err.Number
  3.     Case 2107
  4.         MsgBox "This is my custom error message for Error No 2107"
  5.     Case 2113
  6.         MsgBox "This is my custom error message for Error No 2113"
  7.     Case 2169
  8.         MsgBox "This is my custom error message for Error No 2169"
  9.     Case 2237
  10.         MsgBox "This is my custom error message for Error No 2237"
  11.     Case 3022
  12.         MsgBox "This is my custom error message for Error No 3022"
  13.     Case 3200
  14.         MsgBox "This is my custom error message for Error No 3200"
  15.     Case 3201
  16.         MsgBox "This is my customer error message for Error No 3201"
  17.     Case 3314
  18.         MsgBox "This is my customer error message for Error No 3314"
  19.     Case 3315
  20.         MsgBox "This is my customer error message for Error No 3315"
  21.     Case 3316
  22.         MsgBox "This is my customer error message for Error No 3316"
  23.     Case 3317
  24.         MsgBox "This is my customer error message for Error No 3317"    
  25.     Case Else
  26.         MsgBox "This is an unexpected error.  Please report this to the administrator."
  27. End Select
  28. End Sub
If you can see, I didn't have to declare a variable, as compared to the Form Error's "(DataErr as Integer)"
All I need now is to call these procedure from each of my form's Save button error handler:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSave_Click()
  2. On Error GoTo ErrorHandler
  3.  
  4. DoCmd.RunCommand acCmdSaveRecord
  5. MsgBox "Changes saved successfully."
  6.  
  7. ExitErrorHandler:
  8. Exit Sub
  9.  
  10. ErrorHandler:
  11. MyCodes.PErrorHandler
  12. Response = acDataErrContinue
  13. Resume ExitErrorHanlder
  14.  
  15. End If
Now, If I need to edit or add a custom error message, I just edit it from the MyCodes module and it shall apply to all Save buttons to all forms.

************************

I hope this article helps a little for those who are seeking this same kind of solution that I have seeked and found answer for before.
For any suggestions, please tell me so that we can improve this for future viewers, specially members to use. :)
Aug 18 '08 #1
Share this Article
Share on Google+
2 Comments


P: 6
I have created a form that captures Capital Project Request data for review. After the reviewer has made their decision as to approve or deny the project request, they click an approval button or a denial button. When the button is clicked, an append query runs and writes the data to the appropriate table. I have the project ID set as the primary key and the project ID field is set to not allow duplicates. This process is working correctly, but I want to replace the standard access message with a custom message telling the reviewer the project has already been saved and that duplicates are not allowed. I thought I found the code on this thread that would enable me to add the custom message but I have not been able to get a custom message of any type to appear. The code I have written is as follows:

Private Sub Approve_Click()
On Error GoTo Err_Approve_Click
DoCmd.GoToRecord , , acNewRec

Dim stDocName As String

stDocName = "Qry_Operations Approval Append"
DoCmd.OpenQuery stDocName, acNormal, acEdit


Exit_Approve_Click:
Exit Sub


Err_Approve_Click:
Select Case Err.Number
Case 2107
MsgBox "This is my custom error message for Error No 2107"
Case 2113
MsgBox "This is my custom error message for Error No 2113"
Case 2169
MsgBox "This is my custom error message for Error No 2169"
Case 2237
MsgBox "This is my custom error message for Error No 2237"
Case 3022
MsgBox "This is my custom error message for Error No 3022"
Case 3200
MsgBox "This is my custom error message for Error No 3200"
Case 3201
MsgBox "This is my customer error message for Error No 3201"
Case 3314
MsgBox "This is my customer error message for Error No 3314"
Case 3315
MsgBox "This is my customer error message for Error No 3315"
Case 3316
MsgBox "This is my customer error message for Error No 3316"
Case 3317
MsgBox "This is my customer error message for Error No 3317"
Case Else
MsgBox "This is an unexpected error. Please report this to the administrator."
End Select
Response = acDataErrContinue

Resume Exit_Approve_Click
End Sub

Any help would be appreciated as my knowledge of VB is very limited and I have taken most of my code from samples and online fourms.
Jan 16 '09 #2

P: 3
hyperpau thanks for simple global error customizing technique.

but plz correct typing error in HANDLING FORM ERRORS GLOBALLY which I figured out after spending some time.

FErrorHandler is written as FErrorHanlder in form's On Error event calling.


the correct calling code is

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Error (DataErr as Integer, Response as Integer)
  2.  MyCodes.FErrorHandler (DataErr)
  3.  Response = acDataErrConitnue
  4.  End Sub
  5.  
Jun 23 '14 #3