By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 775 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

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

P: 2
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 cmdUpdatePayments_Click()
Dim DB As DAO.Database
Dim rs As DAO.recordset
Dim rs1 As DAO.recordset

Set DB = CurrentDb
Set rs = DB.OpenRecordset("PBPurchases", dbOpenDynaset)
Set rs1 = DB.OpenRecordset("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
Mar 1 '11 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.