Yearly Attendance Calendar 1

[amarjitn]

thread702-1723375

Hi,
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.

 

[MajP]

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?

 

[amarjitn]

Thanks for getting in touch MajP, sure here is the DB,

If it helps my location is UK and I use dd/mm/yyyy.

Kind regards

 

[MajP]

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.

 

[MajP]

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.

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.

 Do While Not rs.EOF
        AbsenceDate = rs!AbsenceDate
        strSql = "Select * from qry_FillTextBoxes where EmployeeID = " & EmpId    'Query that finds the absence Year() by employeeID
        [b]strSql = strSql & " AND absenceDate = #" & Format(AbsenceDate, "mm/dd/yyyy") & "#"[/b]
       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.

 

[amarjitn]

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!

 

[amarjitn]

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