How to generate weeknumber and begin of week date in access vba?

Hi guys . I got a table in my db called week. It has the following
feilds on it . Year, weekno and monday. I created a button and a text box in the form.
when i type a year name and press a button i want the the follwing happes:

it check if the year exist in the db. if the year does not exist it goes
and inserts week number from 1 -52 along with start of date and year in to the db. I be happy if some one show me how to do this in access.Thanks

example :

year --- weekno --- monday
2005 1 1/3/2005
2005 2 1/10/2005
2005 3 1/17/2005
. . .
. . .
. . .
. . .
. . .
2005 50 12/26/2005

Suppose your year is in the textbox Me.txtYear. Then...

    Dim dtm1stMonday As Date, dtmDate As Date, bytWeekNo As Byte

    'Assume first Monday is January 1.
    dtm1stMonday = DateSerial(Me.txtYear, 1, 1)

    'Correct date if it's NOT Monday.
    dtm1stMonday = DateAdd("d", 2 - Weekday(dtm1stMonday), dtm1stMonday)

    'If correction places you in last Monday of previous year, correct THAT.
    If Year(dtm1stMonday) < Me.txtYear Then
        dtm1stMonday = DateAdd("d", 7, dtm1stMonday)
    End If

    dtmDate = dtm1stMonday
    For bytWeekNo = 1 To 52
        MsgBox Me.txtYear & " " & bytWeekNo & " " & dtmDate, vbInformation, "Mondays in Year"
        dtmDate = DateAdd("d", 7, dtmDate)
    Next bytWeekNo

Change the MsgBox statement in this code to actually assign the bytWeekNo and dtmDate to other textboxes if you wish.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Many thanks to u reply. If i want to insert year,weekno and monday date to week table how shold i do that(just like example below) i be happy if u show me that part.Thanks

year --- weekno --- monday
2005 1 1/3/2005
2005 2 1/10/2005
2005 3 1/17/2005
. . .
. . .
. . .
. . .
. . .
2005 50 12/26/2005