Recordset Update from one table to another using main form/Subform

[farid121]

I am trying to update the main form with each record in the subform.
I have two tables Invoice and Payment table.
The main form has the payment details which I need to update for each invoice selectedon the subform.
EG
Invoice Table (SubForm)
Ref no , Inv No , Amt
1 , 101 , 10.00
2 , 102 , 20.00 etc

Payment Table Feilds (Main Form)
RefNo , Inv No , chq no amt

The code should update as follows
Payment Table Should be
RefNo , Inv No , chq no, amt
1 , 101 , 111 , 10.00
2 , 102 , 111 , 20.00

the code updates the Invoice table fine but only updates the last record in the payment table. What I need is a additional loop which adds to the payment table.

Payment Table Results
RefNo , Inv No , chq no, amt
2 , 102 , 111 , 20.00

Thanks for any help.


code
Private Sub cmdUpdatePaymen ts_Click()
Dim DB As DAO.Database
Dim rs As DAO.recordset
Dim rs1 As DAO.recordset

Set DB = CurrentDb
Set rs = DB.OpenRecordse t("PBPurchases" , dbOpenDynaset)
Set rs1 = DB.OpenRecordse t("PBPayAmt", dbOpenDynaset)
rs.MoveFirst
'-----------------------------------------------------------
Do Until rs.EOF

If Me!txtYesNo = -1 Then
Me!PBSelectPay = -1
Me!PBSelPayDes = "Paid"
Me!Recon = "R"
Requery
Else
If Me!txtYesNo = 0 Then
Me!PBSelPayDes = "Un Paid"
Me!PBSelectPay = 0
End If
End If

rs.MoveNext
Loop
End Sub