I think you may be making this harder than is required.

How do the record for the current week get inserted into the table ?

This can all be done with just the use of sql.
Here are a couple of examples, untested.

If you want to select the values from the previous week and insert then into
the table with this weeks end date then,
Insert Into Productivity(WeekEndingDate, Earned, PercentComplete)
Select #CreateODBCDate(TodaysDate)#, Earned, PercentComplete
From Productivity
Where WeekEndingDate = #CreateODBCDate(dateadd("d",-7,todaysDate))#

But if the record is already inserted and you just need to update it then

UPDATE Productivity
SET Earned = R.Earned
, PercentComplete = R.PercentComplete
FROM Productivity, Productivity R
WHERE R.WeekEndingDate = #CreateODBCDate(dateadd("d",-7,todaysDate))#
AND Productivity.WeekEndingDate = #CreateODBCDate(TodaysDate)#

Ken