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
- Private Sub frmMyForm_Error (DataErr as Integer, Response as Integer)
- MsgBox "Error No.:" & DataErr
- End Sub
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
- Private Sub frmMyForm_Error (DataErr as Integer, Response as Integer)
- If DataErr = 3314 Then
- MsgBox "MyField is required. Please enter a data in this field."
- Else
- MsgBox "Error No.:" & DataErr
- End Sub
'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
- Private Sub frmMyForm_Error (DataErr as Integer, Response as Integer)
- If DataErr = 3314 Then
- MsgBox "MyField is required. Please enter a data in this field."
- Response = acDataErrContinue
- Else
- MsgBox "Error No.:" & DataErr
- End If
- End Sub
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
- 2107
- The value you entered doesn't meet the validation rule defined for the field or control.
- To see the validation rule, click Design view, click the appropriate field,
- and then, if the property sheet isn't open, click the Properties button on the toolbar.
- Then click the Data tab.
- To solve this problem, enter a value that meets validation rule, or press ESC to undo your changes
- 2113
- The value you entered isn't valid for this field.
- For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.
- 2169
- You can't save this record at this time.
- 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
- anyway?
- 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.
- Select an item from the list, or enter text that matches one of the listed items.
- 3022
- 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.
- 3200
- The record cannot be deleted or changed because table <name> includes related records.
- 3201
- You can't add or change a record because a related record is required in table <name>.
- 3314
- 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.
- 3315
- Field <name> can't be a zero-lenght string
- 3316
- <Table-level validation text>.
- 3317
- 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.
Expand|Select|Wrap|Line Numbers
- Private Sub Form_Error (DataErr as Integer, Response as Integer)
- Select Case DataErr
- 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
- End Sub
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
- Public Sub FErrorHandler (ByVal DataErr as Integer)
- Select Case DataErr
- 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
- End Sub
Expand|Select|Wrap|Line Numbers
- Private Sub Form_Error (DataErr as Integer, Response as Integer)
- MyCodes.FErrorHanlder (DataErr)
- Response = acDataErrConitnue
- End Sub
**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
- Private Sub cmdSave_Click()
- DoCmd.RunCommand acCmdSaveRecord
- MsgBox "Changes saved successfully."
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
- Private Sub cmdSave_Click()
- On Error GoTo ErrorHandler
- DoCmd.RunCommand acCmdSaveRecord
- MsgBox "Changes saved successfully."
- ExitErrorHandler:
- Exit Sub
- ErrorHandler:
- MsgBox "Error No.:" & Err.Number
- Resume ExitErrorHanlder
- End If
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
- Private Sub cmdSave_Click()
- On Error GoTo ErrorHandler
- DoCmd.RunCommand acCmdSaveRecord
- MsgBox "Changes saved successfully."
- ExitErrorHandler:
- Exit Sub
- ErrorHandler:
- 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 ExitErrorHanlder
- End If
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
- Public Sub PErrorHandler()
- 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
- End Sub
All I need now is to call these procedure from each of my form's Save button error handler:
Expand|Select|Wrap|Line Numbers
- Private Sub cmdSave_Click()
- On Error GoTo ErrorHandler
- DoCmd.RunCommand acCmdSaveRecord
- MsgBox "Changes saved successfully."
- ExitErrorHandler:
- Exit Sub
- ErrorHandler:
- MyCodes.PErrorHandler
- Response = acDataErrContinue
- Resume ExitErrorHanlder
- End If
************************
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. :)