Getting rid of standard Access error messages

[Rusty]

Hey guys,

Does anyone know the code to get rid of the standard Access error message on attempted duplicate entries on a form?

The field name is "txtPersonProjectID" the control source is "PersonProjectID" from the table "tblPersonProject". The field is indexed as "Yes (No Duplicates)" so when a duplicate value is entered the user gets the standard msg of :

“The changes you requested to the table were not successful because they would create duplicate values…”

I want to display something more useful like "This person is already working on this project"

The form is called "frmPersonProject" and I've tried placing the following code on the Before Update event of the field "txtPersonProjectID", but clearly it does not work, any ideas??

Rusty

Private Sub txtPersonProjectID_BeforeUpdate(Cancel As Integer)

If txtPersonProjectID <> txtPersonProjectID Then
Cancel = True
Msgbox “Person exists in project etc..”
End If

End Sub

 

[CBragg]

Find out what the Err.Number is then Try this:

Private Sub txtPersonProjectID_AFTER_Update(Cancel As Integer)
On Error GoTo Err_Handler

Exit Sub

Err_Handler:
If Err.Number = " NUMBER HERE " Then
Msgbox "This person is already working on this project", vbExclamation
Exit Sub
End Sub

Haven't tested it, but hope this is of help!!

 

[Mile-O]

Use the form's OnError event.

 

[Rusty]

Use the form's OnError event.

I used that and placed the following code on the form's OnError event and it works but I get my msgbox and then the default Access error message.

How do I stop that from happening?

Rusty

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Cancel = True
MsgBox "This person is already on this project"
End Sub

 

[CBragg]

Try using the

If Err.Number = NUMBER HERE then
msgbox ".......
End if

 

[Rusty]

Try using the

If Err.Number = NUMBER HERE then
msgbox ".......
End if

How do I find out what the error number is? There's nothing displayed on the default Access error message...

Rusty

 

[CBragg]

Put a msgbox in the Err_handler: bit that says
msgbox Err.Number

 

[Rusty]

Put a msgbox in the Err_handler: bit that says
msgbox Err.Number

Thanks for the help, I cracked it using the code below in the AfterUpdate event:

If Err.Number = 0 Then
    DoCmd.RunCommand acCmdUndo
MsgBox "This person is already working on this project", vbExclamation
End If

Cheers,

Rusty

 

[maxmangion]

you can use the
Response = acdataerradded and
Response = acdataerrcontinue
to get rid of the standard Access error messages.

In the FAQs there are some examples which make use of this method!