Update table with values from another table (1 Viewer)

[boycie]

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

 

[jdraw]

I'm not following what exactly you're trying yo do.
You show 3 records in t_A and 3 records in t_B

You mention numbers 1---20?
This just adds three more names and doesn't amend the current ones

What makes you think some current names will be amended?

The

rs2.AddNew
rs2![name] = rs1![name]
rs2.Update

Adds a new record to rs2

You may want to research rs2.Edit

but you'll have to make sure you're dealing with the proper records.

 

[vbaInet]

I want to loop through table A and insert records to table B that have the same autonumber (basically, 1 to 20)

An INNER JOIN between table A and B via the autonumber field will get you those records where the values match in both tables. You can do so by building a query with both tables and linking them via the autonumber fields.

But this doesn't make much sense if you're trying to INSERT into table A.

 

[boycie]

I used the 3 records as a quick example. There are 20 records in table A (this table is correct). However, various users of the database have incorrect values in their table (table B)
I need to correct the table with the values from table A. The autonumber field values must remain the same ie 1 to 20
I need to change the text in two fields on table B to match table A

So for example record 1 (table B) has Ian in the name field and jones in the surname field. Record 1 in table A is John. I need to change Ian to John.....but I need to loop through all of Table b and amend all records using vba

Hope I've explained it well, thanks

 

[DCrake]

If this is a one off exercise then create a query with both tables and join on the PK field

Then bring down field1 from bad table and field1 from the good table
Change view to datasheet

Highlight the good field column and select copy
Then highlight the bad column and select paste

Repeat for the other field(s)

 

[boycie]

Thanks for the reply but I would like to be able to do this with vba code as I may have to do it several times with several users. I think I am almost there as i can add the records in table B. I just need to be able to amend them now but I'm struggling with that

 

[jdraw]

I created a small mdb (attached). I don't have ACC2007, only 2003.
It has Tables
T_ABoycie simulates your T_A with about 9 records
T_BBoycie simulates your T_B also with 9 records

I have backups of each of these tables eg T_ABoycie original and
T_BBoycie original

There is a query, that will update the records in T_BBoycie with name and surname values from T_ABoycie where the Id fields (autonumbers)
T_ABoycie.Id= T_BBoycie.Id

The logic, based on your posts, is that T_ABoycie values are all correct.
Values in T_BBoycie have been incorrectly assigned. So we must update
records in T_BBoycie with the name and surname from T_ABoycie where the
id fields are equal.

I have included a form FrmMAIN with steps 1-4. You could run these steps.
If you click the buttons, you will see the data.
Step 3 runs the query, step 4 shows the final data (updated T_BBoycie)

You can look at the query and underlying SQL by looking at the query in Design view.
Hope it is useful to you.

 

[boycie]

Thank you Jdraw so much for your help on this. Before I saw your posting, I managed to work out a solution as follows;

Dim db As DAO.database
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim strSQL1 As String

Set db = CurrentDb()
Set rs2 = db.OpenRecordset("t_2")

strSQL = "UPDATE t_1 INNER JOIN t_2 ON t_1.ID=t_2.ID SET t_2.name = [t_1]![name];"
strSQL1 = "UPDATE t_1 INNER JOIN t_2 ON t_1.ID=t_2.ID SET t_2.surname = [t_1]![surname];"

'rs1.MoveFirst

Do Until rs2.EOF

db.Execute (strSQL)
db.Execute (strSQL1)

rs2.MoveNext

Loop

rs2.Close
Set rs2 = Nothing

Set db = Nothing

End Sub

It was the update sql that I was missing.I could probably combine both strSQL's with just one. Your solution seems the most straight forward but the tables that I need to update are in an access backend with passwords and so I need to Set db string for passwords etc (which I can do). If I can adapt your solution to pass the relevant security I will use it
thanks again