hi,
I am trying to update values in a table with the correct values from another table. The first column is an autonumber field and is linked to another table and so I cannot just delete the records and add new ones.
I want to loop through table A and insert records to table B that have the same autonumber (basically, 1 to 20)
eg.
table A
1,ian,smith
2,paul,jones
3,kevin,thomas
I want to loop through and amend the last two fields to;
table B
1,john,evans
2,paul,davis
3,ringo,starr
This is my code;
Dim db As DAO.database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("t_A")
Set rs2 = db.OpenRecordset("t_B")
'
rs1.MoveFirst
Do Until rs1.EOF
rs2.AddNew
rs2![name] = rs1![name]
rs2.Update
rs1.MoveNext
Loop
rs2.Close
Set rs2 = Nothing
Set db = Nothing
End Sub
This just adds three more names and doesn't amend the current ones
Thanks for any help
I am trying to update values in a table with the correct values from another table. The first column is an autonumber field and is linked to another table and so I cannot just delete the records and add new ones.
I want to loop through table A and insert records to table B that have the same autonumber (basically, 1 to 20)
eg.
table A
1,ian,smith
2,paul,jones
3,kevin,thomas
I want to loop through and amend the last two fields to;
table B
1,john,evans
2,paul,davis
3,ringo,starr
This is my code;
Dim db As DAO.database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("t_A")
Set rs2 = db.OpenRecordset("t_B")
'
rs1.MoveFirst
Do Until rs1.EOF
rs2.AddNew
rs2![name] = rs1![name]
rs2.Update
rs1.MoveNext
Loop
rs2.Close
Set rs2 = Nothing
Set db = Nothing
End Sub
This just adds three more names and doesn't amend the current ones
Thanks for any help