Trapping Primary Key Violation Error

[nerdcore]

I would like to trap the primary key violation error when a user enters a duplicate name on a form. My table is set up with a FranchieNumber field that is the primary key. This field is bound on a Form.

If a user enters a duplicate FranchiseNumber they will get a key violation error when moving to the next record. Currently the error is not very user friendly, says "you have created duplicate entries in the index, primary key, or relationship.....remove the index...". This will freak out my users . I'd like to trap this error and put up a MsgBox saying "You entered a duplicate Franchise Name. Please enter a unique number". How do I trap this error and where in the code would I do this? Is this even possible? Thanks!

 

[nabiS2K]

Look up the OnError event for an access form in Help. If you still have trouble let me know.


You basically only need to know the error number to be able to trap the error. God Bless,
Mike

Yes, I believe in Jesus. ;-)
"Neither is there salvation in any other: for there is none other name under heaven given among men, whereby we must be saved." (Acts 4:12, KJV)

 

[nerdcore]

The problem is there is no apparent Error number for the primary key violation. At least it doesn't come up in the MessageBox. The OnError event seems to belong inside a sub however this error is not happening inside a sub. It is happening when moving to the next record. Any help?

 

[HomeALone]

I assume you know VBA, put the following sub to the BeforeUpdate event of the Pimarykey field on the form. Modify the sub'f field/table names to match your database.

Good luck.
________________________________________
'This Sub will check if the acount number is unique. If it is not. It gives you an option to do nothing or goto that record for update.

Dim x As Variant
Dim DocName As String
Dim LinkCriteria As String

x = DLookup("[PrimaryKeyName]", "PrimaryKeyTable", "[PrimaryKeyName] = " & Forms![FormName]![PrimaryFieldName])
On Error GoTo 0

If Not IsNull(x) Then
Beep
'Ask whether user wants to go to the record for update.
If msgbox("Oops, You did it again! " & " This account already exists!" _
& vbCrLf & "Do you want to go to that record to update?", vbYesNo) = vbYes Then
Cancel = True
DocName = "FromName"
LinkCriteria = "[PrimaryKeyName] =" & x
Me.Undo 'undo form entry
DoCmd.OpenForm DocName, , , LinkCriteria, acNormal
Exit Sub
Else
Cancel = True
Me![CCRNumber].SetFocus
End If
End If

End Sub

 

[nerdcore]

Thanks HomeAlone! That solved my problem (in 4 different forms/tables)

 

[danvlas]

3022 is the name (number) of the error...

and the message has something to do with violating index or primary key

Just 2 cents...


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html