Consider:
Code:
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?