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

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?

I assume the cost centre is being selected in a bound combo box control in the form, in which case you can avoid the error being raised by anticipating it in the form's BeforeUpdate event procedure:

    Const MESSAGE_TEXT = "You must select a cost center before continuing."

    If Nz(Me.CostCenterID,0) = 0 Then

        MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"

        Cancel = True

    End If

This will work fine if the user is attempting to save the record by moving to another record or explicitly saving the current record in some other way, but will generate another system message if the user attempts to close the form.  It's quite difficult to trap this, but you can avoid it by forcing the user to save or close a record by means of command buttons.  You'll find an example as SaveDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the buttons are enabled/disable by code in the form's module on the basis of the current state of the record.

BTW the reason you were getting the specific error which you encountered is almost certainly not because the CostCenterID control is Null, but because Access has given the column a default value of zero in the table design.  The 'required' constraint is not therefore violated, but referential integrity is.  A default value of zero in a foreign key column can be useful in the right context, as it can reference a row in the referenced table with a primary key value of zero and a non-key value of N/A or similar, thus avoiding the ambiguity of a Null foreign key.  This would be inappropriate in the current context of course, and you might therefore wish to remove the default value in the table design.