X
XMan
How to catch database messages and replace them with my own? TIA.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gary Miller
XMan,
This is done by creating Error Handlers in VBA code. I was
going to tell you to go to Help to look that up, but Help
was totally Helpless on that.
Here is a basic primer....
Anyplace that you anticipate a possible error ( this covers
a lot of ground ) you need to include in your code something
like this at the top...
On Error GoTo xyzErrorHandler
Then at the end of your code...
xyzErrorHandler:
If err > 0 Then
msgbox err & ": " & error (This will give you the
number and message of something you didn't anticipate)
ElseIf err = (Whatever err number you may anticipate) Then
msgbox "Your special message to the user"
** Now you do whatever you need to after this error
End If
Hope this helps a bit,
Gary Miller
This is done by creating Error Handlers in VBA code. I was
going to tell you to go to Help to look that up, but Help
was totally Helpless on that.
Here is a basic primer....
Anyplace that you anticipate a possible error ( this covers
a lot of ground ) you need to include in your code something
like this at the top...
On Error GoTo xyzErrorHandler
Then at the end of your code...
xyzErrorHandler:
If err > 0 Then
msgbox err & ": " & error (This will give you the
number and message of something you didn't anticipate)
ElseIf err = (Whatever err number you may anticipate) Then
msgbox "Your special message to the user"
** Now you do whatever you need to after this error
End If
Hope this helps a bit,
Gary Miller
X
XMan
You mean at Before and After DB operations like Add, Update, and Delete? How
about inquiry?
Man that's a lot of ground.
about inquiry?
Man that's a lot of ground.
G
Gary Miller
Grin!!!!
Error handling is always a mixed bag of cost benefit. If
this DB is just for you, you probably don't need much. If it
is for commercial distribution, you need to cover a lot of
bases. The best programmers usually take the time to put
error handling into any coding routine that they do.
Generally, error handling is appropriate where you expect to
or have run into errors. Somebody goes to print a report and
then hits cancel, they may be thrown into a window that is
thrown by the canceling the print and asks them to Debug or
End. Error handling can suppress this.
A normal Add, Edit or Delete on a form doesn't take coding
on your side normally, so there is no reason to have or
place to put error handlers there. If you put a command
button on a form to open another form and then you misspell
the name or the form gets deleted, an error will occur then.
No, If you are searching for a record that can't be found,
that is kind of normal and you shouldn't need any handling.
What were the error messages that you were getting
originally that you wanted to handle a different way? This
may be a good example to pursue as sometimes there is a
difference between handling a 'system error' and a 'data
validation error'. Ex: Typing text into a date or number
field.
Gary Miller
Error handling is always a mixed bag of cost benefit. If
this DB is just for you, you probably don't need much. If it
is for commercial distribution, you need to cover a lot of
bases. The best programmers usually take the time to put
error handling into any coding routine that they do.
Generally, error handling is appropriate where you expect to
or have run into errors. Somebody goes to print a report and
then hits cancel, they may be thrown into a window that is
thrown by the canceling the print and asks them to Debug or
End. Error handling can suppress this.
A normal Add, Edit or Delete on a form doesn't take coding
on your side normally, so there is no reason to have or
place to put error handlers there. If you put a command
button on a form to open another form and then you misspell
the name or the form gets deleted, an error will occur then.
No, If you are searching for a record that can't be found,
that is kind of normal and you shouldn't need any handling.
What were the error messages that you were getting
originally that you wanted to handle a different way? This
may be a good example to pursue as sometimes there is a
difference between handling a 'system error' and a 'data
validation error'. Ex: Typing text into a date or number
field.
Gary Miller
X
XMan
I've got errors like foreign key constraints, or asking for cascade delete,
or delete a record. I would like to insert my own wordings for these errors.
This app is not a commercial app so it can be less proof. Those constraint
errors really drive me nuts. TIA.
or delete a record. I would like to insert my own wordings for these errors.
This app is not a commercial app so it can be less proof. Those constraint
errors really drive me nuts. TIA.
G
Gary Miller
Sounds like you did a good job with setting your referential
integrity on your database. Is it maybe more than you
wanted? Do you not want the cascade deletes? They are a good
safety measure, ie... not being able to delete an invoice
without deleting all of the invoice details.
This kind of falls outside error handling and more into the
field of data validation. You could do things in the
BeforeUpdate of a form that would simulate what Access is
doing inherently, such as checking to see if there are any
child records before a parent is deleted, but Access is kind
of doing that for you. Some of these things may be trappable
for you to create your own message, others may not be.
Foreign key constraint errors I haven't run into as I use
subforms for the child tables and Access seems to handle
everything there pretty efficiently.
Give me some examples of the constraint errors and maybe I
can get a better idea of what is going on. They sound a bit
unusual.
May be signing off for the evening before too long.
Gary Miller
integrity on your database. Is it maybe more than you
wanted? Do you not want the cascade deletes? They are a good
safety measure, ie... not being able to delete an invoice
without deleting all of the invoice details.
This kind of falls outside error handling and more into the
field of data validation. You could do things in the
BeforeUpdate of a form that would simulate what Access is
doing inherently, such as checking to see if there are any
child records before a parent is deleted, but Access is kind
of doing that for you. Some of these things may be trappable
for you to create your own message, others may not be.
Foreign key constraint errors I haven't run into as I use
subforms for the child tables and Access seems to handle
everything there pretty efficiently.
Give me some examples of the constraint errors and maybe I
can get a better idea of what is going on. They sound a bit
unusual.
May be signing off for the evening before too long.
Gary Miller
N
Neil
Just to add to this, forms have their own On Error events. The event passes
in 2 arguments, DataErr and Response. DataErr is the number of the error
that has occured. Response is how you wish to proceed after your code has
performed a task. What I do is declare constant error variables in the
General Declerations section (right @ the top of the form) and then use them
in my code later. This way you have a variable which gives an idea of the
error rather than just a number. The code for this would look as follows:
-------------------------------------------------------
Option Compare Database
Option Explicit
' Declare known errors
Const intErrConfirmCascadeDelete As Integer = 2861
Const intErrInputMaskIsInvalid As Integer = 1086
Const intErrDateEnteredIsInvalid As Integer = 5810
' Events and procedures will then start here....
-------------------------------------------------------
In the error event for the form your code would look something like this:
-------------------------------------------------------
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Debug.Print DataErr ' This will print the error number that has
occured into the immediate window so that you know the correct error number.
Delete this line once you know all the errors that you want to handle
yourself
' Check which error has occured
Select Case DataErr
Case intErrConfirmCascadeDelete
' Display a custom message box
MsgBox "This will delete all the records in all linked tables",
vbOKOnly
' Continue as normal
Response = acDataErrContinue
Case intErrInputMaskIsInvalid
' Do nothing - Let Access hadle this error
' This is pointless put just showing you that if you dont set the
response then Access handles the error as normal
Case intErrDateEnteredIsInvalid
' Ignore that this is an incorrect date and continue without a
warning being displayed (The record will not save though!)
Response = acDataErrContinue
End Select
End Sub
-------------------------------------------------------
The code above is just an example and can be modified to suit your needs.
Also, the error codes above are not correct.
HTH,
Neil.
in 2 arguments, DataErr and Response. DataErr is the number of the error
that has occured. Response is how you wish to proceed after your code has
performed a task. What I do is declare constant error variables in the
General Declerations section (right @ the top of the form) and then use them
in my code later. This way you have a variable which gives an idea of the
error rather than just a number. The code for this would look as follows:
-------------------------------------------------------
Option Compare Database
Option Explicit
' Declare known errors
Const intErrConfirmCascadeDelete As Integer = 2861
Const intErrInputMaskIsInvalid As Integer = 1086
Const intErrDateEnteredIsInvalid As Integer = 5810
' Events and procedures will then start here....
-------------------------------------------------------
In the error event for the form your code would look something like this:
-------------------------------------------------------
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Debug.Print DataErr ' This will print the error number that has
occured into the immediate window so that you know the correct error number.
Delete this line once you know all the errors that you want to handle
yourself
' Check which error has occured
Select Case DataErr
Case intErrConfirmCascadeDelete
' Display a custom message box
MsgBox "This will delete all the records in all linked tables",
vbOKOnly
' Continue as normal
Response = acDataErrContinue
Case intErrInputMaskIsInvalid
' Do nothing - Let Access hadle this error
' This is pointless put just showing you that if you dont set the
response then Access handles the error as normal
Case intErrDateEnteredIsInvalid
' Ignore that this is an incorrect date and continue without a
warning being displayed (The record will not save though!)
Response = acDataErrContinue
End Select
End Sub
-------------------------------------------------------
The code above is just an example and can be modified to suit your needs.
Also, the error codes above are not correct.
HTH,
Neil.
J
jfp
"Help" is indeed helpless -- in general, it is totally lacking in
overviews of topics. For error handling specifically (and other topics
as well) i would recommend "VB and VBA in a NutShell" (O'Reilly Press)
-=-=
overviews of topics. For error handling specifically (and other topics
as well) i would recommend "VB and VBA in a NutShell" (O'Reilly Press)
-=-=
X
XMan
Excellent! Thanks.
Neil said:Just to add to this, forms have their own On Error events. The event passes
in 2 arguments, DataErr and Response. DataErr is the number of the error
that has occured. Response is how you wish to proceed after your code has
performed a task. What I do is declare constant error variables in the
General Declerations section (right @ the top of the form) and then use them
in my code later. This way you have a variable which gives an idea of the
error rather than just a number. The code for this would look as follows:
-------------------------------------------------------
Option Compare Database
Option Explicit
' Declare known errors
Const intErrConfirmCascadeDelete As Integer = 2861
Const intErrInputMaskIsInvalid As Integer = 1086
Const intErrDateEnteredIsInvalid As Integer = 5810
' Events and procedures will then start here....
-------------------------------------------------------
In the error event for the form your code would look something like this:
-------------------------------------------------------
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Debug.Print DataErr ' This will print the error number that has
occured into the immediate window so that you know the correct error number.
Delete this line once you know all the errors that you want to handle
yourself
' Check which error has occured
Select Case DataErr
Case intErrConfirmCascadeDelete
' Display a custom message box
MsgBox "This will delete all the records in all linked tables",
vbOKOnly
' Continue as normal
Response = acDataErrContinue
Case intErrInputMaskIsInvalid
' Do nothing - Let Access hadle this error
' This is pointless put just showing you that if you dont set the
response then Access handles the error as normal
Case intErrDateEnteredIsInvalid
' Ignore that this is an incorrect date and continue without a
warning being displayed (The record will not save though!)
Response = acDataErrContinue
End Select
End Sub
-------------------------------------------------------
The code above is just an example and can be modified to suit your needs.
Also, the error codes above are not correct.
HTH,
Neil.
J
jfp
To extend this approach:
Create a separate code module and put the various "Const intErrXxx ..."
in there. (They need to be Public). Then, you do not have to repeat
the code inside every form. Also, as you add new codes, all forms are
affected.
Next -- if it makes sense, take the guts of the error handler (the Case
statement) and turn it into a function, again placed in this new module.
In the form error handler, call this function; test its return. If
true, the error has been handled; if not, proceed to your own
specialized Case statement.
-=-=-=
Create a separate code module and put the various "Const intErrXxx ..."
in there. (They need to be Public). Then, you do not have to repeat
the code inside every form. Also, as you add new codes, all forms are
affected.
Next -- if it makes sense, take the guts of the error handler (the Case
statement) and turn it into a function, again placed in this new module.
In the form error handler, call this function; test its return. If
true, the error has been handled; if not, proceed to your own
specialized Case statement.
-=-=-=