copy data from table to another with matching field

[gangel]

Hey everyone Ive got 2 tables,



and im running this code.
It looks at a reference and date on a table, saves that tables info,
then looks at another table, finds the same refence and date and copys data over.

Table 1 may have Multiples of table 2

Im trying to copy the data over with this vba code, but it took about 10 minutes to go through 10,000 records. (270,000 to go...)

im obviously doing this wrong!!!

Private Sub Command0_Click()

Dim Pay1 As DAO.Recordset
Dim paymeth As DAO.Recordset
Dim curst As DAO.Recordset


Dim payrefc As Variant
Dim insco As Variant
Dim Chq As Variant
Dim paydatec As Variant

Set paymeth = CurrentDb.OpenRecordset("import_Paymthrdr", dbOpenDynaset)

paymeth.MoveFirst

Do Until paymeth.EOF

payrefc = paymeth(1)
insco = paymeth(2)
Chq = paymeth(4)
paydatec = Nz(paymeth(3), Null)

If IsNull(payrefc) Then
    Set Pay1 = CurrentDb.OpenRecordset("SELECT * FROM Import_Pay1 WHERE PayDate = #" & paydatec & "# and payref IS NULL", dbOpenDynaset)
End If

If IsNull(paymeth(3)) Then
    Set Pay1 = CurrentDb.OpenRecordset("SELECT * FROM Import_Pay1 WHERE PayDate IS NULL and payref = '" & Replace(payrefc, "'", "''") & "'", dbOpenDynaset)
Else
    Set Pay1 = CurrentDb.OpenRecordset("SELECT * FROM Import_Pay1 WHERE PayDate = #" & paydatec & "# and payref = '" & Replace(payrefc, "'", "''") & "'", dbOpenDynaset)
End If

If Pay1.RecordCount > 0 Then

Pay1.MoveFirst
Do Until Pay1.EOF
Pay1.Edit
Pay1(11) = Chq
Pay1(12) = insco
Pay1.Update
Pay1.MoveNext
Loop
paymeth.Delete

End If

paymeth.MoveNext

Loop

End Sub

Any ideas/help?

[June7]

Consider:

While Not paymeth.EOF
    payrefc = paymeth(1)
    insco = paymeth(2)
    Chq = paymeth(4)
    paydatec = paymeth(3)
    
    If IsNull(payrefc) Then
        CurrentDb.Execute "UPDATE Import_Pay1 SET Pay11=" & Chg & ", Pay12=" & insco & " WHERE PayDate=#" & paydatec & "# AND payref Is Null"
        'Set Pay1 = CurrentDb.OpenRecordset("SELECT * FROM Import_Pay1 WHERE PayDate = #" & paydatec & "# and payref IS NULL", dbOpenDynaset)
    End If
    
    If IsNull(paymeth(3)) Then
        CurrentDb.Execute "UPDATE Import_Pay1 SET Pay11=" & Chg & ", Pay12=" & insco & " WHERE PayDate=Is Null AND payref='" & Replace(payrefc, "'", "''") & "'"
        'Set Pay1 = CurrentDb.OpenRecordset("SELECT * FROM Import_Pay1 WHERE PayDate IS NULL and payref = '" & Replace(payrefc, "'", "''") & "'", dbOpenDynaset)
    Else
        CurrentDb.Execute "UPDATE Import_Pay1 SET Pay11=" & Chg & ", Pay12=" & insco & " WHERE PayDate=#" & paydatec & "# AND payref = '" & Replace(payrefc, "'", "''") & "'"
        'Set Pay1 = CurrentDb.OpenRecordset("SELECT * FROM Import_Pay1 WHERE PayDate = #" & paydatec & "# and payref = '" & Replace(payrefc, "'", "''") & "'", dbOpenDynaset)
    End If
    
    'If Pay1.RecordCount > 0 Then
    '    Pay1.MoveFirst
    '    Do until Pay1.EOF
    '        Pay1.Edit
    '        Pay1(11) = Chq
    '        Pay1(12) = insco
    '        Pay1.Update
    '        Pay1.MoveNext
    '    Loop
    '    paymeth.Delete
    'End If
    paymeth.MoveNext
Wend
paymeth.Close
CurrentDb.Execute "DELETE FROM import_Paymthrdr"

I would reference recordset field names instead field index. Of course, this assumes the field names never change. However, your code assumes the field position is always the same.
Use your actual field names for Pay11 and Pay12. What data type are these fields?

Really should be able to run UPDATE action without having to open and loop recordset. Something like:

CurrentDb.Execute "UPDATE Import_Pay1, import_Paymthrdr SET Pay11 = import_Paymthrdr.fieldname11, Pay12=import_Paymthrdr.fieldname12 WHERE Import_Pay1.PayDate=import_Paymthrdr.PayDate AND Import_Pay1.payref Is Null AND import_Paymthrdr.payref Is Null"

Use the query designer to help build the correct sql syntax.

Why would there be a pay date and no pay reference?