Firstly data entry should be undertaken via a form. For your first
requirement you can then put code in the form's Error event procedure. In
this you can call the MsgBox function to display your own message and set the
return value of the event procedure's Response argument to acDataErrContinue
to suppress the system message. To identify the specific error examine the
value of the DataErr argument in the code. To find the value for a
particular error temporarily put a line:
MsgBox DataErr
in the event procedure and then deliberately generate the error. Once you
know the number you can then use it in your final code to determine that it’s
the anticipated error and not an unanticipated one. In the case of the
latter you can either pop up a generic message with the MsgBox function or
allow the system error message to be displayed, e.g.
Const ANTICIPATEDERROR = 666 ' this is the error number you expect
Dm strMessage As String
Select Case DataErr
Case ANTICIPATEDERROR
strMessage = "Your custom error message goes here."
Case Else
srMessage = "An unknown data error has occurred."
End Select
MsgBox strMessage, vbExclamation, "Data Error"
Response = acDataErrContinue
Or to show the system error in the event of an unanticipated error:
Const ANTICIPATEDERROR = 666 ' this is the error number you expect
Const CUSTOMERRORMESSAGE = "Your custom error message goes here."
Select Case DataErr
Case ANTICIPATEDERROR
MsgBox CUSTOMERRORMESSAGE , vbExclamation, "Data Error"
Response = acDataErrContinue
Case Else
' unknown error so show system message,
' the next line is not actually necessary to do this
Response = acDataErrDisplay
End Select
For you second requirement you can use the control's ValidationRule property
to enforce a date before the current date (or you can do it in code in the
control's BeforeUpdate event procedure, which has a Cancel argument whose
return value you can set to True if the validation criteria are not met), but
if you want to control the format in which the date is entered I'd suggest
that an input mask would be the appropriate solution. The format does not
affect the underlying date/time value of course, which in Access is stored as
a floating point number as an offset from 30 December 1899 00:00:00, with the
integer part representing the day and the fractional part the time of day,
and can be shown in any format you wish.
Ken Sheridan
Stafford, England
UT said:
I have two questions--
1. I want to give a friendly error message when a integrity constraint is
violated in place of the system message in the database.
2. In a date field I have "short date" format and a validation rule with a
validation text -
Is Not Null And <=Date() And Not Like "[A-Z]"
So when I enter a date in incorrect format like, 223/2007 (2/23/2007) it
gives me the system error message instead of my validation text. What do I
need to add to validation rule so that my customised message is displayed?
Thanks
