CODEWITHSUNDEEP
vbams-accessfor-looperror-handlingresume
user9027935
user9027935

Reputation:

access vba: go to next iteration on error

Listbox2 is populated from items in a table, which itself is populated from listbox1. An error will be thrown if an attempted addition to the table contains duplicate keys. I want my code to handle the error by skipping the problem iteration in question, instead of stopping halfway through the loop.

My code looks something like this:

Public Sub CopySelected(ByRef frm As Form)

    Dim ctlSource As Control
    Dim intCurrentRow As Integer

    Set ctlSource = Me!listbox1
On Error GoTo nonrelation
    Dim rst As dao.Recordset
    Set rst = CurrentDb.OpenRecordset("Select * from [tempTable]")

    For intCurrentRow = 0 To ctlSource.ListCount - 1
        If ctlSource.Selected(intCurrentRow) Then
            rst.AddNew
            rst![field1] = Forms![myForm]![listbox1].Column(1, intCurrentRow)
            rst![field2] = Forms![myForm]![listbox1].Column(0, intCurrentRow)
            rst.Update
            Forms![myForm]!listbox2.Requery
        End If
    Next intCurrentRow
    Forms![myForm]!listbox2.Requery
done:
    Exit Sub
nonrelation:
    MsgBox Err.Description
End Sub

I know I have to use a 'resume' command somehow in place of my MsgBox Err.Description, but I've never used it. I would like to know how to properly implement that into my code. Thanks!

Upvotes: 0

Views: 225

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

You could check if the record exists with a helper function and only add if not.

Public Function Exists(ByVal Value As String) As Boolean
    Exists = DCount("*","tempTable","[field1]='" & Value & "'") > 0
End Function

Then inside your loop check each record before attempting to insert.

For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
        If Not Exists(Forms![myForm]![listbox1].Column(1, intCurrentRow)) Then
            With rst
                .AddNew
                ![field1] = Forms![myForm]![listbox1].Column(1, intCurrentRow)
                ![field2] = Forms![myForm]![listbox1].Column(0, intCurrentRow)
                .Update
            End With
            Forms![myForm]!listbox2.Requery
        End If
    End If
Next intCurrentRow

Note the above example expects a String. In case of a numeric, you will need to remove the ' ' quotes.

Upvotes: 1

Related Questions