Intercepting Duplicate Error

[Kevin Sprinkel]

Can anyone tell me how to display a more user-friendly
message when a duplicate value is entered in a record on
which a unique multi-field index is maintained?

Thanks for any assistance.

 

[Gerald Stanley]

Kevin

Put validation into the AfterUpdate() method of the last
element of the multi-field index to test for uniqueness.

Hope That Helps

Gerald Stanley MCSD

 

[Kevin Sprinkel]

Thanks for your response.

I was hoping, though, to let Access test for uniqueness,
by virtue of a multi-field index (No Duplicates), and
simply cancel its message and display my own. Is it not
possible, then, to suppress this message?

 

[John Spencer (MVP)]

Use the OnError Event of the FORM and trap the error there.

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

Select Case DataErr
Case 1 '<<<--- whatever the error number is you are trapping
'first time through just trap it with the message box
'below and then you will know what it is and
'can change the case statement
MsgBox "Your Message here"
'corrective action here is possible
Response = acDataErrContinue '
Case Else
msgbox dataErr
Response = acDataErrDisplay 'let Access display its error msg

End Select

End Sub

 

[Kevin Sprinkel]

Thanks.