Replace system error message with a custom message

Question

Hi!

I have field in my form called CostCenterID - since the field is part of a PK/FK relationship it is a required field.

Currently if the user tries to leave the field blank access returns an error "You cannot add or change a record because a related record is in table 'CostCenters'"

I would like to change the message to a custom one "You must select a Cost Centre" but am not sure how I can achieve this.  In googling around it appears that I need to place some code into the On Error property of the form but I do not know the error number.  I think the code should be something like:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = ???? Then

      MsgBox ("You must select a cost center before continuing.")

      Response = 0

   End If

End Sub

I found the "Microsoft JET Database Engine error '80040e21' and tried to use it in the "If DataErr" = statement but it does not work.

Answer 1

Hi,

i found it,  try with 3201.

Mimmo

Answer 2

I think that the event that you use is correct .

Set a breakpiont on your code after setting a number on the If DataErr statement so as:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 1234 Then

      MsgBox ("You must select a cost center before continuing.")

      Response = 0

   End If

End Sub

then start the form. Create the situation that cause the  error , the execution will stop on the statement If DataErr move mouse on DataErr variable to see the value. Set the number in your code.

Ciao Mimmo

Answer 3

Another thought - is my vba code in the wrong place?  I have the code in the On Error property of the form.  Should it be placed somewhere else?

Answer 4

Nope that didn't work but thanks.

Is there a site somewhere that lists common access error numbers?

Answer 5

Hi,

try with:

If DataErr = 2107 Then

Ciao Mimmo

P.s. Add a breakpoint on your code pressing a F9 on the row

If DataErr = 2107 Then

then run the form to obtain the error , move the mouse on the DataErr word to see the error number.