Yearly Attendance Tracker/Calendar (1 Viewer)

Mr. Southern

Registered User.
Local time
Today, 17:53
Joined
Aug 29, 2019
Messages
90
So what I'm getting is your trying to do is create a list of "reasons" like:

took a poop
went to lunch
Got abducted by aliens
Truck is stuck in four-wheel drive
Bla bla bla

That you want to populate in a combo box instead of you typing reasons. Am I correct?

That is correct. I want to make it as foolproof as possible and only allow users to "select" a few reasons.
 

oxicottin

Learning by pecking away....
Local time
Today, 18:53
Joined
Jun 26, 2007
Messages
851
Ok, I had to add a new table (tbluAbsentReasons) and change the (tbl_YearCalendar) to accept the ID of the new table. I changed the form (subFormCalendarInputBox) text box and created a combo box and deleted some spelling code ect. I fixed all the querys to include the table and AbsentReason so they show up in the various reports that use it. I created a form (frm_UpdateAbsentReasons) so you can add/update or disable the absent reasons and if you disable the reason then it wont show in the combo box (subFormCalendarInputBox).

Now I only added a few examples for Employee 1

On a side note the password is "toor" if you couldn't figure out how to find it.... and on the admin form, the last option was added to open the new form.

Hope this is what was needed... Enjoy!
 

Attachments

  • Mr. Southern_Attendance DB v6.xx.zip
    328.2 KB · Views: 179

Mr. Southern

Registered User.
Local time
Today, 17:53
Joined
Aug 29, 2019
Messages
90
Ok, I had to add a new table (tbluAbsentReasons) and change the (tbl_YearCalendar) to accept the ID of the new table. I changed the form (subFormCalendarInputBox) text box and created a combo box and deleted some spelling code ect. I fixed all the querys to include the table and AbsentReason so they show up in the various reports that use it. I created a form (frm_UpdateAbsentReasons) so you can add/update or disable the absent reasons and if you disable the reason then it wont show in the combo box (subFormCalendarInputBox).

Now I only added a few examples for Employee 1

On a side note the password is "toor" if you couldn't figure out how to find it.... and on the admin form, the last option was added to open the new form.

Hope this is what was needed... Enjoy!


Wow.... thank you!!
 

Mr. Southern

Registered User.
Local time
Today, 17:53
Joined
Aug 29, 2019
Messages
90
that does not make any sense. There is already only a combobox.

MajP,

oxicottin was able to help me with the combobox.

What I need to look into now is I am only trying to show certain absence codes in the subFormWeekViewGrid. I want to be able to print and post the weekly grid to show specific absences such as PTO, Vacation and FMLA.

If I wanted to do that would I just be able those codes to the Validation Rule in each days Property Sheet(Is Null Or In ("PTO","VAC","FMLA")) or would I have to change something in the VBA?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:53
Joined
May 21, 2018
Messages
8,463
This is the code that loads the table for the week view
Code:
Private Sub LoadGrid(SupervisorID As Long)
    Dim RsRead As DAO.Recordset
    Dim RSGrid As DAO.Recordset
    Dim strSql As String
    Dim i As Integer
    Dim FieldDate As Date
    Dim InputText As String
    CurrentDb.Execute "Delete * from tbl_WeekViewGrid"
    Set RSGrid = CurrentDb.OpenRecordset("tbl_WeekViewGrid", dbOpenDynaset)
    For i = 0 To 6
        FieldDate = Me.FirstDayOfWeek
        FieldDate = FieldDate + i
        strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & " AND SupervisorID = " & SupervisorID
        Set RsRead = CurrentDb.OpenRecordset(strSql)
        If Not (RSGrid.EOF And RSGrid.BOF) Then RSGrid.MoveFirst
        Do While Not RsRead.EOF
            InputText = RsRead!AbsenceCode & ": " & RsRead!EmployeeName
            InputText = WrapText(InputText, RsRead!AbsenceColorTag, 25) 'Num of spaces in text box
            If RSGrid.EOF Then
                RSGrid.AddNew
                RSGrid.Fields("Day" & i + 1) = InputText
                RSGrid.Update
            Else
                RSGrid.Edit
                RSGrid.Fields("Day" & i + 1) = InputText
                RSGrid.Update
            End If
            If Not RSGrid.EOF Then RSGrid.MoveNext
            RsRead.MoveNext
        Loop
    Next i
    RSGrid.Close
End Sub

You would have to add an additional AND to this line to include only certain choices
Code:
 strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & " AND SupervisorID = " & SupervisorID
 

Mr. Southern

Registered User.
Local time
Today, 17:53
Joined
Aug 29, 2019
Messages
90
This is the code that loads the table for the week view
Code:
Private Sub LoadGrid(SupervisorID As Long)
    Dim RsRead As DAO.Recordset
    Dim RSGrid As DAO.Recordset
    Dim strSql As String
    Dim i As Integer
    Dim FieldDate As Date
    Dim InputText As String
    CurrentDb.Execute "Delete * from tbl_WeekViewGrid"
    Set RSGrid = CurrentDb.OpenRecordset("tbl_WeekViewGrid", dbOpenDynaset)
    For i = 0 To 6
        FieldDate = Me.FirstDayOfWeek
        FieldDate = FieldDate + i
        strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & " AND SupervisorID = " & SupervisorID
        Set RsRead = CurrentDb.OpenRecordset(strSql)
        If Not (RSGrid.EOF And RSGrid.BOF) Then RSGrid.MoveFirst
        Do While Not RsRead.EOF
            InputText = RsRead!AbsenceCode & ": " & RsRead!EmployeeName
            InputText = WrapText(InputText, RsRead!AbsenceColorTag, 25) 'Num of spaces in text box
            If RSGrid.EOF Then
                RSGrid.AddNew
                RSGrid.Fields("Day" & i + 1) = InputText
                RSGrid.Update
            Else
                RSGrid.Edit
                RSGrid.Fields("Day" & i + 1) = InputText
                RSGrid.Update
            End If
            If Not RSGrid.EOF Then RSGrid.MoveNext
            RsRead.MoveNext
        Loop
    Next i
    RSGrid.Close
End Sub

You would have to add an additional AND to this line to include only certain choices
Code:
 strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & " AND SupervisorID = " & SupervisorID

MajP,

I think im missing something because I am getting a syntax error.

Code:strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & " AND SupervisorID = " & SupervisorID & "AND AbsenceCode =("PTO","FMLA", "VAC")"
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:53
Joined
May 21, 2018
Messages
8,463
Code:
AbsenceCode =("PTO","FMLA", "VAC")
not correct sql. Try
Code:
AbsenceCode IN ('PTO','FMLA', 'VAC')
 

oxicottin

Learning by pecking away....
Local time
Today, 18:53
Joined
Jun 26, 2007
Messages
851
Hi oxicottin,

I am trying to add one thing to the yearly attendance tracker. I am trying to add a total section in the Absences_For_Year Report. For example, when the report displays each type of absence Description/Code it will also show the total next to it. What would be the best way to do that?

Is this what you wanted? I made the changes to rpt_AbsencesForYear

IMAGE: https://imgur.com/yHqmIh1
 

Attachments

  • V2-Mr. Southern_Attendance DB v6.xx.zip
    342.8 KB · Views: 197

Mr. Southern

Registered User.
Local time
Today, 17:53
Joined
Aug 29, 2019
Messages
90
Code:
AbsenceCode =("PTO","FMLA", "VAC")
not correct sql. Try
Code:
AbsenceCode IN ('PTO','FMLA', 'VAC')

MajP,

What would I need to add if I wanted to only show specific AbsentReasonID(s)? Oxicottin added a combobox to the AbsentReasons and now I have 1,2,3,4 as AbsentReasonsID(s) that I want to display in the WeeklyGrid.

Would it be something like

Code:
strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & "AND SupervisorID = " & SupervisorID & "AND AbsenceCode IN('PTO','FMLA', 'V') AND AbsentReasonID IN ('1','2','3','4')
Set RsRead = CurrentDb.OpenRecordset(strSql)
Code:
 
Last edited:

Mr. Southern

Registered User.
Local time
Today, 17:53
Joined
Aug 29, 2019
Messages
90
MajP,

What would I need to add if I wanted to only show specific AbsentReasonID(s)? Oxicottin added a combobox to the AbsentReasons and now I have 1,2,3,4 as AbsentReasonsID(s) that I want to display in the WeeklyGrid.

Would it be something like

Code:
strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & "AND SupervisorID = " & SupervisorID & "AND AbsenceCode IN('PTO','FMLA', 'V') AND AbsentReasonID IN ('1','2','3','4')
Set RsRead = CurrentDb.OpenRecordset(strSql)
Code:

The correct code is
Code:
strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND SupervisorID = " & SupervisorID & " AND AbsenceCode IN ('PTO','FMLA','V') AND AbsentReasonID IN (1,2,3,4)"
        Set RsRead = CurrentDb.OpenRecordset(strSql)
 

HollyB

New member
Local time
Today, 15:53
Joined
Nov 13, 2019
Messages
1
If Oxicottin does not reply you can ask me most questions. I built the calendar controls for him and wrote most of the code modules.

I love the database! I'm really new to this and I need to make a few changes to fit my company's attendance program to move out of Excel trackers.

1. We use an accrual for available paid time off and wouldn't be using the "bought vacation" option. What's the best choice to remove that and replace it with the accrual?

2. Department/Branch vs Supervisor selection. Is there a way to set this up using department/branches instead of Supervisor?

I love the ability to list more than 1 attendance item per day since we allow our employees to make up time to remove the attendance points for unscheduled absences.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:53
Joined
May 21, 2018
Messages
8,463
Department/Branch vs Supervisor selection. Is there a way to set this up using department/branches instead of Supervisor?
That would be easy. Change the supervisor table to the Department_Branch table. Now you will have to update the supervisor combo to use the new table and new fields. You will have to check throughout the DB for any place that previously used the supervisor ID and replace with DepartmentID.Once you replace the old table with the new table, the code should break in a few places identifying what needs to be updated.

I will have to look at the accrual for changes.
 

luva

New member
Local time
Today, 23:53
Joined
Mar 3, 2024
Messages
2
Hi, I've tested your YearView_Attendance_with_Oxicotin V10 and it's great for what I need. I would like to start the calendar on Monday, can you give me some suggestions? Thank you
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:53
Joined
Sep 21, 2011
Messages
14,048
Hi, I've tested your YearView_Attendance_with_Oxicotin V10 and it's great for what I need. I would like to start the calendar on Monday, can you give me some suggestions? Thank you
Look at the starting day of the week option.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:53
Joined
May 21, 2018
Messages
8,463
There are two calls to the function getOffset. Read that function and it explains how to change the start day. Need to change in each call. Then you have to change the labels for the day of the week at the top of the calendar.
 

luva

New member
Local time
Today, 23:53
Joined
Mar 3, 2024
Messages
2
I found the solution, thanks for your precious help
 

Users who are viewing this thread

Top Bottom