Yearly Attendance Tracker/Calendar (1 Viewer)

[McSwifty]

Thought I would add my 2 cents here, I have used this for a number of years now and have modified it numerous times to record different info. Really good job on the layout and the reports are superb.
Unfortunately head office liked it too as it made it clear when the time came for people to lose jobs over poor attendance.

I have added a field to record the number of hours away, this also shows on the reports.
I also added the fields for the color coding to the table tblCalInput so I didn't have to do the color in the VBA everytime a new type of leave was added. It's still a bit buggy and can cause issues, but its not too bad.

I will try to remove what I can from the rest of the DB i am using and put up my most recent version for reference.
I even added a shift report so at a glance you can see how each person is doing without having to go through 50-60 people on a regular basis.

I will leave with this warning - mm/dd/yy is standard for SQL dates in VBA when searching and filtering - if you are using dd/mm/yyyy (like me - UK, Australia, NZ and others) you will need to reformat before putting into the VBA and SQL - I simply run

format([datefield],"mm/dd/yyyy")

and then use that inline or add a variable to change it over at the start of the VBA code.

I think I mentioned in another poast a while back:

Again Thanks Oxicottin, superb job and I now understand 95% of how and why you did it that way. Great (learning) tool.

 

[saurabhwise]

Hi Oxycottin

Great database...

Im working on a Absence reporting system where employees can login and enter their attendance for the past 15 days. I run a small company around 40 employees

Right now I use excel sheets for each dept. See attached

can you give me some insight.Thanks in advance.

Cheers!

 

[McSwifty]

Hi Saurabhwise,

This is the best bet for you for that kind of thing and make the venture into Access.
You need a table for staff names and employee numbers (included in above example), one for the department and number (included in above example) and the others for recording the actual absence reason (included) and the data for absence (included). Just download the example and I think at default it will work for you quite well.

 

[Ramesh_vm]

Hi oxicottin, Its really nice and helpful. But i need to make some more changes... Like the Leaves details and Departments name.. which are there(Vacation Days-V,Personal Holiday-P,Unworked Holiday-H) those i want to change to Annual Vacation,Sick Leave,etc... Can u pls help me how can i edit those....

 

[McSwifty]

Hi oxicottin, Its really nice and helpful. But i need to make some more changes... Like the Leaves details and Departments name.. which are there(Vacation Days-V,Personal Holiday-P,Unworked Holiday-H) those i want to change to Annual Vacation,Sick Leave,etc... Can u pls help me how can i edit those....

There should be a table with the name of input (calinput i think) that you can change the names there.
Not at work so can't check

 

[Rach!]

Hi Everyone,

I have been reading through your posts and I dont suppose anyone knows how to convert the Employee Vacation tracker to be able to insert hours? into a date rather than it count as 1 day? Really struggling to find a way to do it!!

Anyone have a database which is simular (shift workers rather that day workers)?

Really appreciate any help

Rach

 

[McSwifty]

Hi Rach, I have done this at work. I will separate out and hope to put it up in a day or two when its sorted.

 

[Rach!]

Hi McSwifty!

That would be fantastic! Been struggling for a while, at least I've tried to have a go I suppose.

Really Appreciate it!

Rach

 

[McSwifty]

Hi Rach,

Sorry for any delays, I found I have embedded my calendar so far into other parts of my database I have to start from scratch with the example posted above. (I tried but it would have taken days)

Links removed - old data

I have NOT updated the reports, but they all need the query and the end report edited to add the new field "TimeTaken" that is now in the "tblInput" table.

Let me know if you have any issues understanding what has been done.

 

[Rach!]

Thanks McSwifty,

Will take a look and get back to you



Rach

 

[Rach!]

Hi I've had a look and i get the following problem when selecting a date box

Set rst = CurrentDb.OpenRecordset(StrgSQL, dbOpenDynaset)

Says Type Mismatch?

---------------------------

Also How do i get the Vacation Days Left & Vacation Days Taken to state hours taken and not the individual 1 day...if that makes sence?

So if i have 160 hours vacation and i book 10 hours, how do i get the Vacation days to say 150 and the Vacation days taken to 10?

Rach

 

[Rach!]

Can anyone help?

 

[McSwifty]

please post the StrgSQL line.

how is it made up, it should be either a query name or SQL
there must be something wrong with it.

 

[McSwifty]

is this error in the example I have given or somewhere else?

 

[Rach!]

Hi Kev,

it errors at see red.


Public Sub SetCalendar()
Dim rst As Recordset, StrgSQL As String
Dim OnDay As Integer, CurDay As Integer, Bclr As Long, Fclr As Long
Dim iRed As Integer, iGreen As Integer, iBlue As Integer
Dim i As Integer, AbsentReason As String

Dim YelYear As Integer

'Hold what Day it is (the day number)
CurDay = Format(Now(), "d")
YelYear = Format(Now(), "yyyy")
'Clear the Day Boxes and set the Current Day Box Color
'but only if the current day is visible.
For i = 1 To 37
Me.Controls("Day" & i).BackColor = vbWhite
Me.Controls("Day" & i).ForeColor = vbBlue
Me.Controls("DBox" & i).BackColor = -2147483633 ' system back color
Me.Controls("time" & i) = Null
Me.Controls("Text" & i) = Null
If Me.Controls("Day" & i) = CurDay And _
Me.Controls("Day" & i).Visible = True Then
'Color in the Box to indicate "Today" but only
'if the calendar is on todays month and current year
If Me.CalMonth.Column(1) = Format(Now, "mmmm") And Me.CalYear = YelYear Then
Me.Controls("DBox" & i).BackColor = 8454143 'light yellow
End If
End If
Next i

StrgSQL = "SELECT * FROM tblInput WHERE UserID=" & CLng(Me.cboUser.Column(0)) & _
" AND (Format([InputDate], 'mm')=" & Me.CalMonth & _
" AND Format([InputDate],'yyyy')=" & Me.CalYear & ");"
Set rst = CurrentDb.OpenRecordset(StrgSQL, dbOpenDynaset)

If rst.RecordCount = 0 Then GoTo Exit_SetCalendar

With rst
.MoveLast: .MoveFirst
Do Until .EOF
OnDay = Format(!InputDate, "dd")
AbsentReason = Nz(!InputText, "")
Select Case !InputText
Case "Vacation"
Bclr = 438366: Fclr = 0
Case "Personal Holiday"
Bclr = 16711680: Fclr = 16777215
Case "Unworked Holiday"
Bclr = 16633344: Fclr = 0
Case "Excused Tardy"
Bclr = 8421504: Fclr = 16777215
Case "Excused Absence"
Bclr = 65535: Fclr = 0
Case "Unexcused Absence"
Bclr = 255: Fclr = 16777215
Case "Unexcused Tardy"
Bclr = 16711935: Fclr = 16777215
Case "Excused Leave Early"
Bclr = 65535: Fclr = 0
Case "Plant Closed"
Bclr = 26367: Fclr = 16777215
Case "Disciplinary Lay-Off"
Bclr = 16776960: Fclr = 0
Case "Medical Leave"
Bclr = 128: Fclr = 16777215
Case "Family Leave"
Bclr = 65280: Fclr = 0
Case "Personal Leave"
Bclr = 10092543: Fclr = 0
Case "Jury Duty"
Bclr = 52479: Fclr = 0
Case "Funeral Leave"
Bclr = 13408767: Fclr = 0
End Select
For i = 1 To 37
If Me.Controls("Day" & i).Value = OnDay Then
Me.Controls("Day" & i).BackColor = Bclr
Me.Controls("Day" & i).ForeColor = Fclr
Me.Controls("Text" & i) = AbsentReason
Me.Controls("time" & i) = !TimeTaken
End If
Next i
Bclr = vbWhite: Fclr = vbBlue: AbsentReason = ""
.MoveNext
Loop
End With
Exit_SetCalendar:
rst.Close
Set rst = Nothing
End Sub

 

[McSwifty]

I honestly have no idea why it's failing. Never had an issue with that part of the code. I don't have access at home any more so will have to wait until I can check when back at work about 14 hours. Also was looking into the vacation time as I realized I hadn't changed to hours before uploading the test files.

 

[Rach!]

Kev,

That was going to be my next question about the hours, im having a go at trying to change it. But if you could help that would be great.


The error happens when ive clicked the Employee dropdown. It comes up with Run-time error '13' Type mismatch. Then it highlights the code i put above.

Rach

 

[Poppa Smurf]

The problem is not the line in red, the problem is in your StrgSQL statement.

I have not used the following code format as in
" AND (Format([InputDate], 'mm')=" & Me.CalMonth & _
" AND Format([InputDate],'yyyy')=" & Me.CalYear & ");"

When using SQL the date must be formatted in the US format e.g.mm/dd/yyyy

Assuming strText is a date 21/08/2013 then the format for SQL should be as follows

"#" & Format(strText, "mm/dd/yyyy") & "#"

 

[Rach!]

Another question Kev,

Hidden is a text box showing VacDays with control source =[cboUser].[Column](1) this brings up the employee vacation days (on the calendar).

I can see that if i make this [cboUser].[Column](2) it brings up the persons name. Where abouts is this looking so that i can add additional columns of information to add to the calendar?

For example I want to add or take off bought and sold vacation which is in also in the the tblUser as well. Is this the best way to add this to the vacation total days on the calendar?

Any ideas?

 

[McSwifty]

@ poppa smurf - this code it Oxicottin's the only thing I changed was having hours on the calender instead of days. I just forgot to adjust the vacation to match.
I have had no problems, but you are right with the date formatting. I have to change all dates to US as I use UK formatting and it created a huge headache most of the time while testing.

@Rach - I think the column(1) is from the employee table, just adjust the query on the cboUser and it will have as many or as few columns of data as you like.
I can check when I get in to work.