Yearly Attendance Tracker/Calendar (2 Viewers)

Rach! · Aug 21, 2013

Thanks Kev!

Really appreciate your help. The code (date format) worked fine before as it was. Must be something to do with adding the time taken, or what ever it was you added in new to the calendar i suppose.

Rach

McSwifty · Aug 26, 2013

I think this sorts the issues - I hope.

I have Vacation as hours - I have also added the hours into the reports and underlying queries.

See attached file.

I have added a line of code to debug the issue you were having with StrgSQL.

If it errors out again with the mismatch error please post the debug from the VBA debug window.
If you have no issues you can comment out the debug line.

cheers.

Rach! · Aug 27, 2013

Thanks Kev,

Will take a look.

Rach! · Aug 28, 2013

McSwifty said:
I think this sorts the issues - I hope.

I have Vacation as hours - I have also added the hours into the reports and underlying queries.

See attached file.

I have added a line of code to debug the issue you were having with StrgSQL.

If it errors out again with the mismatch error please post the debug from the VBA debug window.
If you have no issues you can comment out the debug line.

cheers.

Kev,

Just brilliat! Works an absolute treat! Thank you so much for you help.

McSwifty · Aug 28, 2013

Rach! said:
Thank you so much for you help.

glad I could help. Just realised you are in the UK, no issues with the date selection when clicking on a day in the calendar?
I used to get issues with Feb as the 1st Feb would come out at 2nd Jan.
2 Feb = 2 feb
3 feb = 2 Mar
4 feb = 2 Apr

etc.

Hope you got this sorted.

Rach! · Aug 28, 2013

Hi Kev,

Your right feb has come out as January any idea why this is? do i need to change the date format in the code then?

Rach

Also
Have you had to set the vacation up to account for employees who are calculated over a pro rata vacation?

McSwifty · Aug 28, 2013

pro rata? Do you mean leave taken before the next roll over of a year allocation? I did not account for that. The balance will go negative.

The date issue is due to access wanting to use US date format of mm/ dd/yyyy
UK is dd/mm/yyyy so you need to reformat the date input to the pop up date selection box.

I am away from work until Monday but I can look at what I had to do in my company one as I have had this running for over 5 years. And *bring on the boast* it was rolled out to the whole business at the end of last year.

McSwifty · Aug 29, 2013

Hopefully oxicottin can help with that. It would take a bit of a rewrite to make that work I think

Edit:

is anyone else having email notifications that someone has posted and then found there is no new post in the topic?

I got this from Don33ald -
I am starting with 12 calendar controls on the form but would prefer having 2 6-month blocks.

Rach! · Sep 3, 2013

Thanks Kev,

How do i contact oxicottin?

McSwifty · Sep 3, 2013

Try to pm.

I think my response advice was aimed at a message I had in my email but not showing in here. The date fix is Eau but after a long weekend and a few days missed at work I didn't get a chance to look. Will try to do that today.

JPRENG · Sep 20, 2013

How do I view this database so I can edit for example the departments, so they suit my needs better?

dyfink · Oct 13, 2013

Firstly, thank you so much oxicottin!!! I was about to make this database from scratch which would have taken me weeks of complete dedication. Now, I only need to customize it.

Rach! said:
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?

Rach

I just started getting that error today and found the solution. I don't know why but for some reason you need to specify what type of RecordSet rst is. So go to the first line of the SetCalendar() sub and change it to

Code:

Dim rst As DAO.Recordset, StrgSQL As String

JPRENG said:
How do I view this database so I can edit for example the departments, so they suit my needs better?

If you just want to edit that data, use the feature at the bottom right of the form. Check the selection that you want and click the lock (The password is oxicottin.) If you need to edit the actual database structure, hold down the shift key as you open the database.

dyfink · Oct 14, 2013

McSwifty said:
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.

McSwifty said:
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)

Here is an Access 2007 version (Google Drive) and Here is the Access 2003 version (Google Drive)

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.

Hi McSwifty,

I tried the links and it looks like the files are no longer there. I really like your idea of adding the colors to the tblCalInput table. Can you (re)share a version of the database which has that implemented?

Thanks

osmosisgg · Dec 3, 2013

Year 2013: found this post and have seen the output. I think this is wonderful. I am wanting to work with the db but would like to know if there are folks out there who are still around to help in case I get stuck.

Please let me know.

Thanks!

McSwifty · Dec 3, 2013

Still here and using this at work on a regular basis. Give it a go and let me know if you have any problems

Dyfink. Missed your post. can't find the files. Must have cleaned it my Google drive and deleted them. I will see if I can find what u did to put the color in the table.

osmosisgg · Dec 3, 2013

wow wow wow! This is awesome! Well, being that I am one of those self learning and just starting out, I have tried to enter the common names we use here into tblCalInput. I did not change the AttendanceCode thinking the data is pulled based on the code, but it appears it is also based on the naming convention. From the little that I know, I have searched and found the modules, but feel unsafe changing it on my own.

If you can point me in the right direction, I would greatly appreciate it. I like to try to figure things out on my own, but this is way above me.

Home leave is the only selection that uses by days, as the rest are all done by hours.

I like the way the form subtracts hours taken from the hours available (vacation)- would be using annual leave in place of this. This would be nice to also have for sick leave, FMLA and home leave(days).

Here is what we use:

CalInputAttendanceCodeAdministrative LeaveLNAnnual LeaveLAComp Time EarnedCEComp Time TakenCTFMLAFMLAFurloughKEHolidayLHHome LeaveXXSick LeaveLSTime Off AwardLY

osmosisgg · Dec 3, 2013

Sorry about the last post- didn't review before I sent

Administrative Leave LN
Annual Leave LA
Comp Time Earned CE
Comp Time Taken CT
FMLA FMLA
Furlough KE
Holiday LH
Home Leave XX (will have to change this one when I get the right code)
Sick Leave LS
Time Off Award LY

McSwifty · Dec 3, 2013

ok, if you change the attendancecode in tblcalinput you also need to change the color details in module basYearView under loadReportCalendar to match those new codes you have in tblcalInput or the calendar view does not display any color markers for the days in question.

the limitation I have found is having hours for one type of leave and days for the other, it would mean two types of tracking data for each staff member. not a big deal but a little more work. I have found hours works pretty well but as Rach wanted days I changed it all over.

The version below is just the standard download by oxicottin and i added the time tracking to it.

Just be aware of the date issue like I put in post #45 if you have system setting to US date format then you won't have any issues, if you do it will take a little bit of work to sort. mostly for the inputbox that comes up when you click on a date for entering data.

osmosisgg · Dec 3, 2013

Thank you.
I went into basYearView under loadReportCalendar and updated it. I also went in basCalendar and changed frm!txtV = 0 to frm!txtLA = 0 as I got the debug popup. Colors work great, but now on the form the "hrs taken" do not calculate. The "hrs left" are correct.

Any ideas on what I can do?

Thanks again,
~osmosisgg

McSwifty · Dec 3, 2013

I think its due tot eh change from txtV to txtLA - txtV was used to calculate the hours taken

the name of the field after hours taken is currently txtV - change to txtLA and change the hours left field to =([txtTotalVacDays])-([txtLA])

Yearly Attendance Tracker/Calendar (2 Viewers)

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

New member

New member

New member

Registered User.

New member

New member

Registered User.

Attachments

New member

Registered User.

Similar threads

Users who are viewing this thread