LoadCalendar procedure in Access 2013

[funwithaccess]

Hi everyone!

I wanted to know if anyone has implemented the LoadCalendar procedure in Access 2013. I need to create a table for each employee and these tables must include all calendar dates for the year 2014. The LoadCalendar procedure will do this for me, unfortunately I do not know how to run it.

EDIT: Also, I do not need to use LoadCalendar if anyone has a better suggestion. What I am looking to do is create each employee table with every calendar year and a point system based on their attendance. If they are late, they get a point, call-off they get a point, they cover for a shift or work a holiday they get points taken off. Each point added is bad. Hopefully this info is helpful. Any suggestions would be much appreciated!

Public Sub LoadCalendar(Optional ByVal pYear As Integer, _
        Optional ByVal pTable As String = "tblCalendar", _
        Optional ByVal pField As String = "the_date")
    Dim db As DAO.Database
    Dim dte As Date
    Dim dteLast As Date
    Dim intYear As Integer
    Dim rs As DAO.Recordset
    Dim strMsg As String

On Error GoTo ErrorHandler

    intYear = IIf(pYear = 0, Year(Date), pYear)
    dte = DateSerial(intYear, 1, 1)
    dteLast = DateSerial(intYear, 12, 31)

    Set db = CurrentDb
    Set rs = db.OpenRecordset(pTable, dbOpenTable, dbAppendOnly)

    Do While dte <= dteLast
        rs.AddNew
        rs.Fields(pField).value = dte
        rs.Update
        dte = dte + 1
    Loop
    rs.Close

ExitHere:
    On Error GoTo 0
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    strMsg = "Error " & Err.Number & " (" & Err.Description _
        & ") in procedure LoadCalendar"
    MsgBox strMsg
    GoTo ExitHere
End Sub

Thank you in advance for your help!

Nate

 

[funwithaccess]

I decided to create the calendar in a spreadsheet within Excel and then import it as a table into Access. It was easier to drag the autofill down and allow Excel to do the work.

 

[David R]

I believe you are going about this the wrong way because you're thinking of Access like a spreadsheet, not a relational database.

http://www.access-programmers.co.uk/forums/showthread.php?p=1315659#post1315659