Capture Run-time error 3022 (PK duplicate values).

[Darren Bartrup]

Hi all,

Is there any way of capturing a Run-time error 3022?

The code I'm using is:

    Set rst = dbs.OpenRecordset("Contact_List_" & sType, dbOpenDynaset, dbAppendOnly)    
    For x = 3 To lLastRow
        rst.AddNew
        rst.Fields(0).Value = Forms("frmCalendar").clndr.Value
        For lngColumn = 0 To UBound(y)
            rst.Fields(lngColumn + 1).Value = xlc.Offset(0, y(lngColumn) - 1).Value
        Next lngColumn
        rst.Update
        Set xlc = xlc.Offset(1, 0)
    Next x

and it's falling over on the rst.Update part. I've added a On Error GoTo ERR_HANDLE at the top of the procedure, and this code at the end:

EXIT_PROC:
    On Error GoTo 0
    Exit Sub


ERR_HANDLE:
    Select Case Err.Number
        Case 3022
            DebugError "Duplicate record: " & x, "ImportFromExcel"
            Err.Clear
            Resume Next
        Case Else
            'DisplayError Err.Description, "mdlFunctions_Range.FindCell", True
            Resume EXIT_PROC
    End Select
    
End Sub

but it still stops on the update event.

Do I need to check first if the new record will be a duplicate, or is there any way of capturing it when it occurs?

Thanks for any help offered as usual.

 

[Darren Bartrup]

I've figured it out and it was one of those real DOH! moments.

Earlier in the code I have this:

'Establish an EXCEL application object
    On Error Resume Next
    Set xlx = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
          Set xlx = CreateObject("Excel.Application")
          blnEXCEL = True
    End If
    Err.Clear
    On Error GoTo 0

May help if I reset the On Error GoTo correctly.