Here is how I would do this (assuming your ID is autonumber):
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim dtStart As Date
Dim dtEnd As Date
Dim dtWeek As Date
Dim i As Integer 'check for runaway loop
i = 0
dtStart = (input start date here like "01/01/2009")
dtEnd = (input end date here, plus one day, like "01/01/2010")
sSQL = "SELECT * FROM tblYourTableName"
Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Do Until dtStart = dtEnd Or i = 400
If DatePart("w", dtStart) = 1 Then
dtWeek = dtStart
rs("Date") = dtStart
rs("Week Commencing") = dtWeek
dtStart = DateAdd("d", 1, dtStart)
i = i + 1
This will add all but the first 6 or less Week Commencing dates unless 2009 starts on a Sunday. You will only have to fill those in by hand.
Also, "Date" is a reserved name, and should not be used for a field name.
Did that help?
Look it up at: http://wrox.books24x7.com