Pass one date per record to another subform

[gregc]

OK first i have been at this for a few days so i read thru so much code i am
now lost. Here is what i need to do and i hope someone can help me.
I have an access 2003 database, there are tables with the feilds needed for
the query as follows
Table Name
tblProjectList: ProjectNum, DueDate
tblDeliverables: ProjectNum, DeliverableID, DueDate
tblworkpackage: ProjectNum, DeliverableID, WPID, DueDate
tbltasks: ProjectNum, DeliverableID, WPID, taskID, DueDate
these tables are attached via subforms on a mainform called frm Project Data
Entry Form and are linked by project number then the subforms are linked via
their ID feilds. what i want to do is when a duedate is change on the Task
subform (tbltasks subform) it will look at all the tasks duedates for that
WPID (the next level up) and select the MAX DueDate, then pass it to the
Workpackage DueDate in an update query using code run in VB. and so on for
the remaining tables using the same critiria.
Here is the code i have tried so far:
1st attempt

DoCmd.RunSQL "UPDATE tblProjectList INNER JOIN (tblDeliverables INNER JOIN
(tblworkpackage INNER JOIN tbltasks ON tblworkpackage.WPID = tbltasks.WPID)
ON tblDeliverables.DeliverableID = tblworkpackage.DeliverableID) ON
tblProjectList.ProjectNum = tblDeliverables.ProjectNum SET tbltasks.DueDate =
[tblworkpackage]![DueDate]& WHERE (([tbltasks]![ProjectNum] =
[tblworkpackage]![ProjectNum] & [tbltasks]![DeliverableID] =
[tblworkpackage]![DeliverableID] & [tbltasks]![WPID] = [tblworkpackage]![WPID]

2nd attempt

UPDATE ((tblProjectList
INNER JOIN tblDeliverables ON tblProjectList.ProjectNum =
tblDeliverables.ProjectNum)
INNER JOIN tblworkpackage ON (tblDeliverables.ProjectNum =
tblworkpackage.ProjectNum) AND (tblDeliverables.DeliverableID =
tblworkpackage.DeliverableID))
INNER JOIN tbltasks ON (tblworkpackage.ProjectNum = tbltasks.ProjectNum) AND
(tblworkpackage.DeliverableID = tbltasks.DeliverableID) AND
(tblworkpackage.WPID = tbltasks.WPID)
SET tbltasks.DueDate = tblworkpackage!DueDate
WHERE (((tbltasks.DueDate)= Max([Forms]![tbltasks subform]![DueDate])) AND
(([Forms]![tbltasks
subform]![ProjectNum])=[Forms]![tblworkpacksubform]![ProjectNum]) AND
(([Forms]![tbltasks
subform]![DeliverableID])=[Forms]![tblworkpacksubform]![DeliverableID]) AND
(([Forms]![tbltasks subform]![WPID])=[Forms]![tblworkpacksubform]![WPID]));

3rd attempt

DoCmd.RunSQL "UPDATE Copyoftblworkpackage
((tblProjectList INNER JOIN tblDeliverables ON tblProjectList.ProjectNum =
tblDeliverables.ProjectNum)INNER JOIN tblworkpackage ON
tblworkpackage.ProjectNum = tblDeliverables.ProjectNum) AND
(tblDeliverables.DeliverableID = tblworkpackage.DeliverableID)) INNER JOIN
tbltasks ON (tbltasks.ProjectNum = tblworkpackage.ProjectNum) AND
(tbltasks.DeliverableID = tblworkpackage.DeliverableID) AND
(tblworkpackage.WPID = tbltasks.WPID) SET DueDate =(SELECT MAX([DueDate])
FROM [tbltasks] WHERE ((me.ProjectNum=[tblworkpackage]![ProjectNum] And
me.DeliverableID =[tblworkpackage]![DeliverableID] And me.WPID =
[tblworkpackage]![WPID]));"

4th attempt

DoCmd.RunSQL "UPDATE ((tblProjectList INNER JOIN tblDeliverables ON
tblProjectList.ProjectNum = tblDeliverables.ProjectNum) INNER JOIN
tblworkpackage ON (tblDeliverables.ProjectNum = tblworkpackage.ProjectNum)
AND (tblDeliverables.DeliverableID = tblworkpackage.DeliverableID)) INNER
JOIN tbltasks ON (tblworkpackage.ProjectNum = tbltasks.ProjectNum) AND
(tblworkpackage.DeliverableID = tbltasks.DeliverableID) AND
(tblworkpackage.WPID = tbltasks.WPID) SET tbltasks.DueDate =
copyoftblworkpackage!DueDate WHERE
(([tbltasks]![ProjectNum]=[tblworkpackage]![ProjectNum]) AND
([tbltasks]![DeliverableID]=[tblworkpackage]![DeliverableID]) AND
([tbltasks]![WPID]=[tblworkpackage]![WPID]) AND ((tbltasks.DueDate)=
Max([DueDate])));"


all have failed due to aggregate errors or missing object errors and so forth.

I could REALLY use some help as this has been the only thing holding up my
small Project application.
Thanks in advance
Greg