Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Yearly Attendance Calendar

Yearly Attendance Calendar

Yearly Attendance Calendar

thread702-1723375: Yearly calendar for attendance

I have just come across the amazing database developed by OXICOTTIN & MAJP, version 5.00 from the link in thread 702-1723375 in 2014.

I have a problem that maybe OXICOTTIN or MAJP or others can help with;

After entering activities in any of the months Jan-Jun the cells do not get shaded in, the activity correctly shows in the weekly absence grid.

After entering activities in any of the months Jul-Dec everything appears to work as expected.

Thank you so much for any help and advise that can be given.

RE: Yearly Attendance Calendar

I can take a guess where I would think the problem is, but would have to see your code for what you have modified. Can you post your DB?

RE: Yearly Attendance Calendar


If it helps my location is UK and I use dd/mm/yyyy
That is helpful, I can see that being a possible issue. I may need to add some code to handle that.

RE: Yearly Attendance Calendar

in the module mod_CalendarInputBox there is a public function called GridClick. This function is what fires whenever you click on a day "cell" in the calendar subform. You changed the date format to dd/mm/yyyy. I changed it back.
DoCmd.OpenForm "frm_CalendarInputBox", , , , , acDialog, Format(selectedDate, "mm/dd/yyyy") & "

It passes that value in openargs as a string to the input form to add a new absence. I modified the code to use a date serial function instead of a cdate function. That way it should work regardless of regional settings.

CODE -->

Private Sub Form_Load()
    Dim selectedDate As String
    Dim EmpId As Long
    Dim strWhere As String
    Dim rs As DAO.Recordset
    Dim mnth As Integer
    Dim dy As Integer
    Dim yr As Integer
    If Me.OpenArgs & "" <> "" Then
        selectedDate = Split(Me.OpenArgs, ";")(0)
        EmpId = Split(Me.OpenArgs, ";")(1)
        Me.txtEmployeeName = DLookup("EmpLName & ', ' & EmpFName", "tbluEmployees", "EmployeeID = " & EmpId)
        Me.txtEmployeeID = EmpId
        'added to support international date format
        'Me.txtAbsenceDate = CDate(selectedDate)
        mnth = CInt(Left(selectedDate, 2))
        dy = CInt(Mid(selectedDate, 4, 2))
        yr = CInt(Right(selectedDate, 4))
        Me.txtAbsenceDate = DateSerial(yr, mnth, dy)
        'MsgBox Me.txtAbsenceDate
    End If
End Sub 

Also in the fillsubformTextBoxes format the absence date for SQL. Always in SQL use the mm/dd/yyyy format regardless of your regional settings.

CODE -->

Do While Not rs.EOF
        AbsenceDate = rs!AbsenceDate
        strSql = "Select * from qry_FillTextBoxes where EmployeeID = " & EmpId    'Query that finds the absence Year() by employeeID
        strSql = strSql & " AND absenceDate = #" & Format(AbsenceDate, "mm/dd/yyyy") & "#"
       Set rsDay = CurrentDb.OpenRecordset(strSql, dbOpenDynaset) 

Once I did this is worked for me. However, there may be other places you have to do a similar trick.

RE: Yearly Attendance Calendar

Hi Majp,

Thank you for looking at the issue I had, I'll download a fresh copy of the database and implement your fixes and let you know how I get on.

Thank you!

RE: Yearly Attendance Calendar

Majp, just implemented the change and it appears to work, thank you

I can't immediately see which other places I need to look at, but I'll run through it till I get to it.

Many thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close