Handling the no duplicates index error

Enoch · January 18th, 2006, 02:53 PM

I know how to handle errors and provide custom error messages for errors generated within a sub or function. However, if the error is thrown by a no duplicates index, I don't know where or how to catch the error in order to provide a more user friendly error message.

Thanks in advance for your help

Enoch

Bob Bedell · January 18th, 2006, 03:42 PM

Don't know exactly how you're doing the insert, but the Jet error you're interested in is 3022.

Code:

Sub DuplicateValueError(strNewValue As String)

   On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblRecords", dbOpenDynaset)

   With rst
      .AddNew
      !Field1 = strNewValue
      .Update  '<--- Error 3022 thrown here.
   End With

   rst.Close
   db.Close
   Set rst = Nothing
   Set db = Nothing
   Exit Sub

Exit_Here:
   rst.Close
   db.Close
   Set rst = Nothing
   Set db = Nothing
   Exit Sub

Err_Handler:

   If Err.Number = 3022 Then
      strMsg = "Error occurred when you called rst.Update." & vbCrLf & vbCrLf
      strMsg = strMsg & "Please enter a unique value for this field."

      MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Value Error"
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   End If

   Resume Exit_Here

End Sub

HTH,

Bob

Bob Bedell · January 18th, 2006, 04:02 PM

On a stylistic note, if I know the error code I'm trapping for, I like to use a constant just to make the code more self-documenting:

Private Const ERR_DUPLICATE_VALUE = 3022

Sub DuplicateValueError(strNewValue As String)

   On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblRecords", dbOpenDynaset)

   With rst
      .AddNew
      !Field1 = strNewValue
      .Update '<--- Error 3022 thrown here.
   End With

   rst.Close
   db.Close
   Set rst = Nothing
   Set db = Nothing
   Exit Sub

Exit_Here:
   rst.Close
   db.Close
   Set rst = Nothing
   Set db = Nothing
   Exit Sub

Err_Handler:

   If Err.Number = ERR_DUPLICATE_VALUE Then
      strMsg = "Error occurred when you called rst.Update." & vbCrLf & vbCrLf
      strMsg = strMsg & "Please enter a unique value for this field."

      MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Value Error"
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   End If

   Resume Exit_Here

End Sub

Bob Bedell · January 18th, 2006, 04:12 PM

Then again it occurs to me that maybe your talking about trapping the error at the Form level, in which case you would use the Form's error event:

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

   Select Case DataErr
      Case 3022
           MsgBox "You added a record which duplicates an existing value."
           Response = acDataErrContinue
      Case Else
           Response = acDataErrDisplay
   End Select

End Sub

Bob

Bob Bedell · January 18th, 2006, 04:29 PM

Then set focus back to the offending control:

Code:

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

    Const ERR_DUPLICATE_INDEX_VALUE = 3022
    Dim strMsg As String

    If DataErr = ERR_DUPLICATE_INDEX_VALUE 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."

        'Set focus to the offending control
        Me.txtText1.SetFocus

        Response = acDataErrContinue 'prevent Access from displaying its own error message
    Else
        Response = acDataErrDisplay 'cause Access to display its own error message
    End If

End Sub

Bob

Enoch · January 18th, 2006, 04:41 PM

Thanks, that's just what I needed.

Enoch

Enoch · January 18th, 2006, 04:57 PM

It was within a form, sorry I wasn't more specific, Thanks for writing out both answers though.
I didn't realize there was a built in event to just handle errors in forms.

Enoch

Bob Bedell · January 18th, 2006, 06:25 PM

No problem, Enoch. You did mention you already knew procedure error handling, so I figured you must have meant the form level stuff. Glad you found what you needed.

Best,

Bob

Penn White · March 22nd, 2006, 10:14 PM

Could someone please explain why this error (3022) is being generated at all. I am getting the same error but see no reason for it. Isn't .addnew supposed to create a new, unique record with a new, unique primary key (indexed, no duplicates, autonumber)? If so, then where is the error coming from?

I have been battling this for days and making no neadway. I'm afraid I may have a corrupt table or something. There are over 1000 records in the table (more or less in sequential numeric order) but the new PK ID is in the 500's.

Any help greatly appreciated.

Penn

Bob Bedell · March 22nd, 2006, 11:28 PM

Quote:

quote:
Isn't .addnew supposed to create a new, unique record with a new, unique primary key (indexed, no duplicates, autonumber)?

Yes it is. If you have two fields in a table named ID(autonumber) and Field1 and run:

rst.AddNew
rst!Field1 = "NewData"
rst.Update

The ID field will be automatically incremented with a new unique ID. You can retrive the new ID by immediately running (after the update):

Dim rst As ADODB.Recordset
Dim intNewAutoNumber As Integer
Set rst = New ADODB.Recordset
rst.Open "SELECT @@IDENTITY", CurrentProject.Connection
intNewAutoNumber = rst.Fields(0)

Sounds to me like your table is toast. Can you import the records you have into a new table:

Code:

INSERT INTO Table2 ( ID, Field1 )
SELECT Table1.ID, Table1.Field1
FROM Table1;

That sort of thing...

HTH,

Bob