View Single Post
  #2 (permalink)  
Old September 16th, 2003, 10:47 PM
Bob Bedell Bob Bedell is offline
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Hi elleetan,

Here's some info I posted a while back on how to use a form's (or subform's) Error event to trap Jet's duplicate record error message. See if you can get it going in your situation.

Build a multi-field, unique index on the table that includes the fields (as many as you like) which define a duplicate record in your subform's record source. This is done through the Indexes dialog in table design view. Just give the index a name, assign your "duplicate-record-defining" fields to it, and set its 'Unique' property to yes.

Second, add this code behind your subform:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Const errDuplicateIndexValue = 3022
    Dim strMsg As String

    If DataErr = errDuplicateIndexValue Then
        strMsg = "This record cannot be added to the database." & vbCrLf
        strMsg = strMsg & "It would create a duplicate record." & vbCrLf & vbCrLf
        strMsg = strMsg & "Changes were uncuccessful."

        MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Record."


        Response = acDataErrContinue
        Response = acDataErrDisplay
    End If

End Sub

This will display a more user friendly error message than the Jet default error string which reads:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

Hope this is in the vacinity of what you're looking for.