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.
[CODE=VB]Private Sub frmMyForm_Error (DataErr as Integer, Response as Integer)
MsgBox "Error No.:" & DataErr
End Sub[/CODE]
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.
[CODE=VB]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[/CODE]
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 acDataErrContin ue right after my custom error message.
[CODE=VB]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 = acDataErrContin ue
Else
MsgBox "Error No.:" & DataErr
End If
End Sub[/CODE]
The Response = acDataErrContin ue 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:
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.
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.
After this, I would still need to code each form's On Error event. To call it from my Form, I use this codes:
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:
[CODE=VB]Private Sub cmdSave_Click()
DoCmd.RunComman d acCmdSaveRecord
MsgBox "Changes saved successfully."[/CODE]
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:
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 acDataErrContin ue 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:
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.
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:
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. :)
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.
[CODE=VB]Private Sub frmMyForm_Error (DataErr as Integer, Response as Integer)
MsgBox "Error No.:" & DataErr
End Sub[/CODE]
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.
[CODE=VB]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[/CODE]
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 acDataErrContin ue right after my custom error message.
[CODE=VB]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 = acDataErrContin ue
Else
MsgBox "Error No.:" & DataErr
End If
End Sub[/CODE]
The Response = acDataErrContin ue 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:
Code:
[B]2107[/B] 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 [B]2113[/B] 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. [B]2169[/B] 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? [B]2237 [/B] - [I](can be replaced by the On Not In List event of a combobox/listbox)[/I]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. [B]3022[/B] 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. [B]3200[/B] The record cannot be deleted or changed because table <name> includes related records. [B]3201[/B] You can't add or change a record because a related record is required in table <name>. [B]3314[/B] 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. [B]3315[/B] Field <name> can't be a zero-lenght string 3316 <Table-level validation text>. [B]3317[/B] 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.
Code:
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.
Code:
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
Code:
Private Sub Form_Error (DataErr as Integer, Response as Integer) [I]MyCodes[/I].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:
[CODE=VB]Private Sub cmdSave_Click()
DoCmd.RunComman d acCmdSaveRecord
MsgBox "Changes saved successfully."[/CODE]
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:
Code:
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 acDataErrContin ue 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:
Code:
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.
Code:
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:
Code:
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. :)
Comment