Yearly Attendance Tracker/Calendar

oxicottin said:
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?
Click to expand...

That is correct. I want to make it as foolproof as possible and only allow users to "select" a few reasons.
 
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

oxicottin said:
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!
Click to expand...


Wow.... thank you!!
 
MajP said:
that does not make any sense. There is already only a combobox.
Click to expand...

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?
 
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 said:
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
Click to expand...

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")"
 
Code: 
AbsenceCode =("PTO","FMLA", "VAC")
not correct sql. Try
Code: 
AbsenceCode IN ('PTO','FMLA', 'VAC')
 
Mr. Southern said:
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?
Click to expand...

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

IMAGE: https://imgur.com/yHqmIh1
 

Attachments

MajP said:
Code: 
AbsenceCode =("PTO","FMLA", "VAC")
not correct sql. Try
Code: 
AbsenceCode IN ('PTO','FMLA', 'VAC')
Click to expand...

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 said:
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:
Click to expand...

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)
 
MajP said:
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.
Click to expand...

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.
 
Department/Branch vs Supervisor selection. Is there a way to set this up using department/branches instead of Supervisor?
Click to expand...
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.
 
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
 
luva said:
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
Click to expand...
Look at the starting day of the week option.
 
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.
 
I found the solution, thanks for your precious help
 
You must log in or register to reply here.

Similar threads

Steve R.
Drone Hysteria
2 3
Replies
46
Views
1,064
The_Doc_Man
The_Doc_Man
MajP
Attendance tracker
Replies
2
Views
909
Gasman
Gasman
P
LEAVE MANAGEMENT
Replies
13
Views
764
Solo712
Solo712
4
Is this possible?
Replies
5
Views
325
mike60smart
mike60smart
D
How to select most current date by month, day and year
Replies
15
Views
854
Design by Sue
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom