Update Table from Matched Records of 2 recordsets 1

[Fattire]

I'm trying to update a table's records when it finds a match from a recordset or add a new record when it can't find a match. When it can't find a match it just errors on the !AMOUNT_APPLIED = rec1!AMOUNT_APPLIED line. Error is : "Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

This has got to be easy, but I'm getting no where - thanks in advance.

    i_update = 0
    i_add = 0
    
    Do While Not rec1.EOF
    rec4.Find "[PK] = '" & rec1!PK & "'", , adSearchForward
        If Not NoMatch Then
            With rec4
                !AMOUNT_APPLIED = rec1!AMOUNT_APPLIED
                !ON_ACCOUNT_AMT = rec1!ON_ACCOUNT_AMT
                !UNAPPLIED_AMT = rec1!UNAPPLIED_AMT
                .Update
                i_update = i_update + 1
            End With
        Else
            If NoMatch Then
                With rec4
                .AddNew
                !PK = rec1!PK
                !AMOUNT_APPLIED = rec1!AMOUNT_APPLIED
                !ON_ACCOUNT_AMT = rec1!ON_ACCOUNT_AMT
                !UNAPPLIED_AMT = rec1!UNAPPLIED_AMT
                End With
                i_add = i_add + 1
            End If
        End If
    rec1.MoveNext
    Loop

 

[PHV]

What about this ?

Do While Not rec1.EOF
    With rec4
        .MoveFirst
        .Find "[PK] = '" & rec1!PK & "'", , adSearchForward
        If .BOF Or .EOF Then
            .AddNew
            !PK = rec1!PK
            !AMOUNT_APPLIED = rec1!AMOUNT_APPLIED
            !ON_ACCOUNT_AMT = rec1!ON_ACCOUNT_AMT
            !UNAPPLIED_AMT = rec1!UNAPPLIED_AMT
            i_add = i_add + 1
        Else
            !AMOUNT_APPLIED = rec1!AMOUNT_APPLIED
            !ON_ACCOUNT_AMT = rec1!ON_ACCOUNT_AMT
            !UNAPPLIED_AMT = rec1!UNAPPLIED_AMT
            .Update
            i_update = i_update + 1
        End If
    End With
    rec1.MoveNext
Loop

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Fattire]

That did it, perfect - didn't change any of your code. As always PHV - learning a lot, ty!

 

[fneily]

And why do you have two tables with the same data?