code to match and update records

  • Thread starter Thread starter Joel
  • Start date Start date
J

Joel

TIA:

Have Table1 and Table2.
Both tables have ID and SSN fields.
I would like to Loop through records in Table1, take ID
in Table1, Find it in Table 2 and set SSN in Table2 = SSN
in Table1

Something like:
Dim rst As DAO.Recordset
Set DataBase = CurrentDb
Do Until rst1.EOF
Set rst1 = DataBase.OpenRecordset("table1")
id1 = rst1!id
ssn1=rst1!ssn
Set rst2 = DataBase.OpenRecordset("table2")
rst2.FindFirst "id1"
'Edit record in Table2 code so SSN =SSN from Table1
Loop

I know this doesn't work but I think I have the proper
concept.

Can someone help with the details!
Thanks, Joel
 
An Update query would be much more efficient than t loop:

strSQL = "UPDATE Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID SET
Table2.SSN = [Table1].[SSN];"
dbEngine(0)(0).Execute strSQL, dbFailOnError
 
Of course...Thanks Allen,

Joel
-----Original Message-----
An Update query would be much more efficient than t loop:

strSQL = "UPDATE Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID SET
Table2.SSN = [Table1].[SSN];"
dbEngine(0)(0).Execute strSQL, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

TIA:

Have Table1 and Table2.
Both tables have ID and SSN fields.
I would like to Loop through records in Table1, take ID
in Table1, Find it in Table 2 and set SSN in Table2 = SSN
in Table1

Something like:
Dim rst As DAO.Recordset
Set DataBase = CurrentDb
Do Until rst1.EOF
Set rst1 = DataBase.OpenRecordset("table1")
id1 = rst1!id
ssn1=rst1!ssn
Set rst2 = DataBase.OpenRecordset("table2")
rst2.FindFirst "id1"
'Edit record in Table2 code so SSN =SSN from Table1
Loop

I know this doesn't work but I think I have the proper
concept.

Can someone help with the details!
Thanks, Joel
Click to expand...


.
Click to expand...
 
Back
Top