Vilolation of Primary Key Constraint

[pjscott]

I'm using Access 2003 and Sql2000.

I have a form that users enter work titles.

How can I generate a custom message when they enter a duplicate title?
Instead of the standard "Violdation of PRIMARY KEY constraint...

Thanks for the help,

Paul

 

[Roger Carlson]

I'm not sure if Access throws the same error number when SQL Server has a
duplicate key violation, but with Access tables, you can put something like
this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
'MsgBox DataErr
If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "Your friendly message."
End If
End Sub

In the OnFormError Event of your form. You can un-comment the MsgBox
DataErr line in there temporarily to see what error number is raised. Once
you know, you can delete it.

Another option is to check for duplicates on the AfterUpdate event of your
control to catch the duplicate before trying to save the record.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[pjscott]

Thanks Roger. I'll give this a shot.

Paul

I'm not sure if Access throws the same error number when SQL Server has a
duplicate key violation, but with Access tables, you can put something like
this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
'MsgBox DataErr
If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "Your friendly message."
End If
End Sub

In the OnFormError Event of your form. You can un-comment the MsgBox
DataErr line in there temporarily to see what error number is raised. Once
you know, you can delete it.

Another option is to check for duplicates on the AfterUpdate event of your
control to catch the duplicate before trying to save the record.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Tony Toews [MVP]]

Another option is to check for duplicates on the AfterUpdate event of your
control to catch the duplicate before trying to save the record.

I prefer that approach because, if there are several, or many, more
fields of data to be entered the user may spend considerable time
entering the extra data. Only to then be told that they've wasted
their time.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/