Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

eilob

Programmer
Mar 28, 2007
54
IE
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
 
Well first of all, rst2 is never set to anything in your code...

Why not just run a simple update query, like:

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


-V
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top