ADP - Prevent SQL Errors

[Guest]

I have an ADP with a unique field that allows users to enter in new values.
Each value needs to be unique, but they also must enter these values by hand.
I set-up a Unique Index on this field and it prevents the users from
entering duplicate values.

But when this does happen, the users get a "tough to understand SQL
generated error message". I have created my own error message that explains
better to the users how to resolve the error.

Is there anyway I can prevent the SQL error message from being displayed in
the ADP, so my error message is displayed only?

Thank you for your time,

T.J. Bernard

 

[Guest]

This might be a slightly different situation than what you're using. I use
code to write to the DB using SQL. If you're using a bound form, then I think
it should work the way you want if you subsitute the "CurrentDb.Execute..."
line with "DoCmd.GoToRecord acNew" or however you add a new record.

On Error Resume Next
CurrentDb.Execute strSQL, dbFailOnError

If Err.Number <> 0 Then

Select Case Err.Number

Case 3022 ' Duplicate entry

MsgBox "Your error message here."
Err.Clear
Exit Sub

Case Else ' Unexpected error

' Optional

End Select

End If