Updating a column on a table depending on data on another table

[eilob]

Hi,
I have two tables in a database in Access, the first one is a table with a week column and a date column. The second one has a colum called dates and if that date matches the date on the first table the week column has to be updated with the corresponding week from the first table.

I tried some VBA code but is not working:

Sub Calendar()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_Mail", dbOpenDynaset) 'Open table tblMail
Set rst1 = db.OpenRecordset("Calendar", dbOpenDynaset) 'Open Calendar table


If (rst1!Dates = rst!Date) Then
rst.AddNew
rst!Week = rst1!Week
rst2.Update
End If

Set rst = Nothing
Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing

End Sub

 

[VRoscioli]

Well first of all, rst2 is never set to anything in your code...

Why not just run a simple update query, like:

UPDATE tblFirst INNER JOIN tblSecond ON tblFirst.DateField = tblSecond.DateField SET tblSecond.WeekField = tblFirst.WeekField;

-V

 

[eilob]

Thanks, I just tried this query below but there is something wrong, this is basically what I am trying to do..

UPDATE tbl_Mail SET tbl_Mail.Week=Calendar.Week WHERE tbl_Mail.Date = Calendar.Dates

 

[PHV]

UPDATE tbl_Mail INNER JOIN Calendar ON tbl_Mail.Date = Calendar.Dates SET tbl_Mail.Week=Calendar.Week

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886