CODEWITHSUNDEEP
excelvbams-access
sharkantipav
sharkantipav

Reputation: 139

error handling while Updating Access Table from Excel

Hey I am updating an Access table from an Excel file with this code.

On Error GoTo Boucle2
row = 1 ' the start row in the worksheet
Do While Not IsEmpty(Worksheets("Temp").Range("A" & row))
    With adoRec
       If Worksheets("Temp").Range("A" & row).Value = "Processed" Then
        .AddNew
        .Fields("TRADE_ID") = Worksheets("Temp").Range("B" & row).Value
        .Fields("Tkt") = Worksheets("Temp").Range("C" & row).Value
        .Update
       ElseIf Worksheets("Temp").Range("A" & row).Value = "AmendValid" Then
        adoRec.Filter = "TRADE_ID='" & Worksheets("Temp").Range("B" & row).Value & "'"
        .Fields("Tkt") = Worksheets("Temp").Range("C" & row).Value
        .Update
      End If
    End With

Boucle2 :
    row = row + 1
    Loop

I want to handle a common error .... If the TRADE_ID already exist... I get a normal Runtime Error '-2147217887 (80040e21)' that says that this TRADE_ID already exists and it doesnot support duplicates. I want to ignore this error by going to next row in the loop and maybe add a msgbox saying This TRADE_ID already exists

Thank you much

Upvotes: 1

Views: 539

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Try this one:

Sub test()        
    'some code        
    On Error GoTo errHandler

    Do While Not IsEmpty(Worksheets("Temp").Range("A" & Row))
        With adoRec
            'add record
        End With            
Boucle2:
        Row = Row + 1
    Loop        

    'some code        
    Exit Sub

errHandler:
    If Err.Number = -2147217887 Then
        MsgBox "This TRADE_ID already exists"
    Else
        MsgBox "Unknown error: " & Err.Description
    End If
    Resume Boucle2        
End Sub

Upvotes: 1

Wayne G. Dunn
Wayne G. Dunn

Reputation: 4312

This will handle that error - and you can customize to handle others.

 On Error GoTo Error_Trap
Row = 1 ' the start row in the worksheet
Do While Not IsEmpty(Worksheets("Temp").Range("A" & Row))
    With adoRec
       If Worksheets("Temp").Range("A" & Row).Value = "Processed" Then
        .AddNew
        .Fields("TRADE_ID") = Worksheets("Temp").Range("B" & Row).Value
        .Fields("Tkt") = Worksheets("Temp").Range("C" & Row).Value
        .Update
       ElseIf Worksheets("Temp").Range("A" & Row).Value = "AmendValid" Then
        adoRec.Filter = "TRADE_ID='" & Worksheets("Temp").Range("B" & Row).Value & "'"
        .Fields("Tkt") = Worksheets("Temp").Range("C" & Row).Value
        .Update
      End If
    End With
    Row = Row + 1
Loop
Exit Function
Error_Trap:
If Err.Number = -2147217887 Then
    MsgBox "When processing Row: " & Row & " - Record already exists for Ticket: " & Worksheets("Temp").Range("C" & Row).Value & vbCrLf & vbCrLf & _
            "Will skip this record.", vbOKOnly, "Duplicate Record"
    Resume Next
Else
    MsgBox "Error Encountered - what do you want to do? Add Code..." & vbCrLf & vbCrLf & _
            "Err: " & Err.Number & vbTab & Err.Description, vbOKOnly, "Error"
    Resume Next
End If

Upvotes: 0

Related Questions