Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Code Archive _ Holiday Planner Demo

Posted by: Peter Hibbs Mar 8 2013, 12:12 PM

This demo database shows a method for programming holiday dates for a company but it could, of course, be used for any sort of time scheduling.
The form is set up in a similar way to a standard Gantt chart with a list of employees and a four week period which shows lines against the relevant dates (see screen shot).
The basic facilities are :-
Add new employees to the chart or delete employees.
Add a holiday period by clicking the start date cell and dragging the mouse cursor across to the end date to draw the line.
Add a holiday period by double clicking a cell and entering dates in pop up form.
Edit a holiday period by clicking on a line to show a pop up editor form.
Edit an employee record by clicking on an employee name to show a pop up employee editor form.
Add Public Holiday dates manually or automatically and show them in different colors.
There is also a .pdf file which shows how to use the database and how to change some facilities.
The basic method described in the document can also be used to show data in a format which is not easy to do using the standard Access forms.
The demo database works with A2002 or later.
Peter Hibbs.

 

 Planner.zip ( 198.19K ): 1383
 

Posted by: Peter Hibbs Mar 9 2013, 12:14 PM

Hi Daniel,
Thanks for the kind comments.
OChuck,
:welcome:
Color coding individual 'cells' could probably be done (although I haven't tried it on this project) but it would be quite complicated. You could probably do it with Conditional Formatting, depending on how many colors you want and which version of Office you are using. If you need lots of colors and you are using a version earlier than A2010 then this http://www.UtterAccess.com/forum/Continuous-Form-Multiple-t1968354.html might help.
As far as showing the Notes in the Tool Tip flag, that is going to be tricky. I had already thought about that myself and the problem is that with the MouseMove event of the hidden button control, you only have the X and Y mouse co-ordinates available to determine where the mouse pointer is at any time. The X co-ordinate is no problem because it easy to calculate which column the mouse is on, the problem is determining which row the mouse is on so that you can fetch the data for the correct employee. The Y co-ordinate only gives the vertical position of the current record and NOT all the records so you can't use that to find the row number. If you, or anyone else, knows of any way to find the row number from the Y parameter of the MouseMove event then it could probably be done quite easily. The only method I can think of (and this is really complicated) is to use an API call to determine the mouse position on screen and then try and relate that to the form-subform position to calculate the offset from the first row. Also, if you happen to have more than 29 employees and the subform has scrolled down a bit, then this is going to get really really complicated. If you have any thoughts on this I would be interested to hear them, sorry I can't give you a more positive suggestion.
Anyway, let us know how you get on if you should make those changes, it could be useful to other developers.
Peter.

Posted by: jleach Mar 17 2013, 03:24 PM

Hi Peter, et al;
A few months back there was a good discussion about how to get the row position using MouseMove, and we never did come up with a good way. The best thing that we were able to figure on was, as you said, using the WinAPI and the form/sub positions and try to backfigure. Likewise, we figured that'd be ok - not too terribly bad - but throwing a scrollbar in the mix was the real dealbreaker. Stephen Lebans has some code at his site that attempts this, which may get us, generally speaking, within a few pixels perhaps in most cases. However, the because we're working with an result that's subject to accumulated error in the record calculation, the more records there are, the greater that window of errors become, so if there's 20 extra records things might be ok, but if there's 2000 extra scrollable records, a 2 pixel miscalculation because of a border width could put us 2 inches off our actual location.
All in all, not a reliable solution. Very difficult.
Cheers,

Posted by: jleach Mar 17 2013, 03:32 PM

Although, now, it occurs to me that if we know the size of the displayed record in height and the size of the container, we can limit the underlying set to the number of controls that'd fit. This would cut back on problems with accumulated errors, though it'd require a somewhat extensive custom implementation of scrolling. Not ideal, but if one really wanted to, maybe that'd at least be feasible.
Cheers,

Posted by: Peter Hibbs Mar 17 2013, 05:55 PM

Hi Jack,
Yes, coincidentally I came across the A2KConditionalFormattingVer27.mdb demo from Stephen today while researching another project but as you say, it would require a lot of programming to implement the ToolTips facility using his code (which looks quite complicated). Another possible problem is that the response times in the demo seem quite slow and combined with the ToolTip delay which can also be quite slow I really wonder whether it would be practicable and whether it is worth all the effort. Maybe someone will have a go and let us know (but I'm not holding my breath!).
Cheers,
Peter.

Posted by: Peter Hibbs Mar 28 2014, 01:49 PM

The attached A2003 database demo is version 2 of the Holiday Planner above. This version has the option to use multiple holiday types with a different color for each type (see screen shot below). A pdf file includes instructions on how to use it and modify it for your own projects.
eter Hibbs.

 

 Planner_V2.zip ( 305.08K ): 1130
 

Posted by: pctechdr Sep 12 2018, 07:15 AM

Hallo m8,

i'm trying to intergrate your perfect program in SQL Server with 500+ persons (not only for Holidays) but with OLE OBJECTS the speed is very very very slow.
I have a question!!!

How can i copy only the Color from Holiday and not the image and then how can i show it in to program!

Thanks in advance!!!

Posted by: Peter Hibbs Sep 12 2018, 10:10 AM

Hi pctechdr,

I am afraid I don't know anything about SQL Server so I can't help with that. Since the colors are part of the image I don't think there is any way to show the colors without the image.

How do you know that it is the OLE OBJECT fields that are slowing things down?

Have you tried Version 1 above (which does not use OLE OBJECTS) to see if that is any quicker?

If it is the OLE OBJECT fields that are the problem I can only suggest that you investigate this in relation to SQL SERVER to see if there is any way to speed things up or if there is any other alternative.

Sorry I can't be of more help but this is not something I have come across before.

Good luck with the program.

Peter.

Posted by: pctechdr Sep 12 2018, 11:14 AM

Thank you for the answer,

Ι will try the first version though I can not choose a different color for different type of holiday to see if is this faster!
thumbup.gif

Posted by: mrcunknown Sep 22 2018, 07:42 AM

Hi all,

I'm using my planner in excel at this moment. This works good for me, but the plan is that multiple teamleads also goining to make plans .... and that [censored] is more people work in 1 excel file.I like this access planner alot and im thinking to use this planner. Is it possible to build it that i can see how many fte are in one shift. shift 1, shift 2, shift 3

if put a example here.

 

Posted by: Peter Hibbs Sep 22 2018, 09:38 AM

Hi mrcunknown,

welcome2UA.gif

It is not clear to me what you want to display on the form, can you explain in more detail.

I am guessing that you have a number of people that do shifts at work and you are displaying coloured lines for each shift but what exactly are you trying to count (what is fte) and what do the characters a, b, c and ziek, etc mean.

More information needed please.

Peter Hibbs.

Posted by: mrcunknown Sep 22 2018, 01:27 PM

Hi,

Sorry for my poor explanation :-).

A, B and C represents shift 1, 2 and 3. ziek = sick. What i would like is indeed that under we can see how many employees there is in one of those shifts (sick, free, training must not be counted).Purpes would be that we easily can see if the shifts are evenly divided.

The most ideal is that we can see the amount of employees by department.

Posted by: Peter Hibbs Sep 22 2018, 04:49 PM

Hi,

So what you are saying is that for each day you want to show a count of the number of people that are working on each shift for that day and display the count at the bottom of the chart. If so, I guess it could be done, it may need some VBA code to do the calculations or maybe a query but it should be possible although it may get a bit complicated. You could add some more rows to the table tblWeekData to show that data on the chart but you would need to add some code to the UpdateChart sub-routine in modGeneral to implement that facility.

Sorry that I don't have the time to help you myself but good luck with the project anyway.

Peter Hibbs.

Posted by: pctechdr Oct 2 2018, 02:29 AM

Hi,
that is exactly the project that i'm working on.
As Peter said, you have to import the fields that you want in table "tblWeekData"
and then in the "modGeneral", "Public Sub Initialise()" you have to write the code to update this fields.
After that you can make filters in the main form for swifts, persons, sick etc. and you can also count how many are sick, holiday etc.








Posted by: Peter Hibbs Oct 2 2018, 03:50 AM

Hi pctechdr,

OK, very impressive.

I see you had a problem earlier with the slow speed of this demo using SQL Server, did you manage to fix that problem and if so, what did you do to correct it?

Peter.

Posted by: pctechdr Oct 2 2018, 04:01 AM

Oh yes,
i forgot to reply and i'm sorry about that!!

The solution!!

I have only the tblHolidayDates & tblPublicHols in SQL (BE)
and the tblWeekData & tblHolidayTypes in the (FE)

So the speed is optimal with the pictures.

Regards!!

Chris

Posted by: mrcunknown Oct 21 2018, 02:31 AM

Thnx for youre informations and explenations. I will try if i can manag that ... but i'm not so experiansed in writing code.

@pctechdr maybe i can see a demo how you fixed it? confused.gif

Posted by: pctechdr Oct 22 2018, 12:38 AM

 Demo_Planner.zip ( 137.45K ): 228

Posted by: mrcunknown Oct 23 2018, 04:24 PM

Thnx! I'm gonna check it out ..... :-)

Posted by: mrcunknown Oct 27 2018, 03:32 PM

Maybe is this a dumb question, but if i make a report from the qryWeekData i get a planning for the people so i can print it.......Everything is good except the date. in top i only get Day1 till Day28, no Date (exp 27-10-2018). What do i do wrong?

Posted by: pctechdr Oct 29 2018, 12:44 AM

Why are you not creating your report from tblHolidayDates ?? You have all information there.
Just a thought!

Chris.

Posted by: mrcunknown Nov 2 2018, 03:12 AM

Thnx :-) ..... I did not check that, but indeed all information is there.

Is it also possible to get a dropdown that i can select which department i want to dislpay in frmHolidays. exp; i have 7 diffrent departments but i only want to see dept #2. I have tryed something to change in qryHoliday but i dont know how to make it in form Holidays.



 

Posted by: mrcunknown Nov 2 2018, 10:10 AM

Enclosed my file ... i hope there somebody who can help me :-)

 test.zip ( 1.35MB ): 49
 

Posted by: Peter Hibbs Nov 2 2018, 11:59 AM

Hi,

Does this do what you want?

Peter Hibbs.

 ROOSTER_Planner_V2_Werkbestand.zip ( 244.05K ): 91
 

Posted by: mrcunknown Nov 2 2018, 12:20 PM

Hi Peter, Not completely...... I think its beter wen i select a department the other departments disappears. and it wil also be nice to have a overview of al departments went there is no selection. i really appreciate youre help

Posted by: Peter Hibbs Nov 2 2018, 02:06 PM

OK, try this then :-

Copy the text below into the Row Source property of the combo box cmbPName and also change the Default Value property of the same combo box to "*"

SELECT tblDepartments.DeptRef, tblDepartments.DeptName FROM tblDepartments UNION SELECT "*", "<< ALL DEPTS >>" FROM tblDepartments
ORDER BY tblDepartments.DeptName;


What happens now is that when the form opens the chart will show all departments and the Combo box will display '<< ALL DEPTS >>'. You can then select one of the departments in the Combo box to show just the records for that department.

Is this what you wanted?

By the way, you have a few 'Compile' errors which you should fix first because they may mask other more important errors. Also, I remmed out some code in the UpdateChart routine while I was testing, if you are using the Public Holidays facility you will need to un-rem those few lines. If you are not using this facility then I would suggest you leave them out as it will speed up the display a little bit.

Peter Hibbs.

Posted by: mrcunknown Nov 2 2018, 02:33 PM

Cool .... thats partally what i need :-) I also mean when i select a department that also the names from different departments wil disappear.

What do you mean with 'Compile' errors? My plan is to make use of the Public Holidays facility .... so i have to fix some lines?

Sorry for the dumb questions :-) hahah

Posted by: Peter Hibbs Nov 2 2018, 04:13 PM

Hi,

I also mean when i select a department that also the names from different departments wil disappear.

Sorry, I do not understand what you mean, what names from what departments should disappear? Please explain in more detail.

What do you mean with 'Compile' errors?
You should compile the VBA code to locate any errors in the code. To do this, open any VBA code window and click on the Debug -> Compile Holiday Planner V2 on the menu bar at the top of the window. I don't have time to explain what this does in detail but you can probably search the Internet for more information.

My plan is to make use of the Public Holidays facility .... so i have to fix some lines?
OK, just open the VBA window for module modGeneral , scroll down to the UpdateChart routine and remove the single quotes from the beginning of the six lines after the comment 'Highlight public holidays in selected colors

As a matter of interest what language are you using and in which country?

It is late here so I will have a look again tomorrow.

Peter.

Posted by: mrcunknown Nov 3 2018, 02:13 AM


I also mean when i select a department that also the names from different departments wil disappear.
Sorry, I do not understand what you mean, what names from what departments should disappear? Please explain in more detail.
I have uploaded 2 screenshots (made it with photoshop to illustrate what i mean)

What do you mean with 'Compile' errors?
You should compile the VBA code to locate any errors in the code. To do this, open any VBA code window and click on the Debug -> Compile Holiday Planner V2 on the menu bar at the top of the window. I don't have time to explain what this does in detail but you can probably search the Internet for more information.
I will try to figure that out

My plan is to make use of the Public Holidays facility .... so i have to fix some lines?
OK, just open the VBA window for module modGeneral , scroll down to the UpdateChart routine and remove the single quotes from the beginning of the six lines after the comment 'Highlight public holidays in selected colors
Did it ... thnx

As a matter of interest what language are you using and in which country?
It is in dutch


 

Posted by: Peter Hibbs Nov 3 2018, 07:01 AM

Hi mrcunknown,

OK, try this one.

The changes I have made are that I have changed the criteria for the Dept field in query qryWeekdata to :- Like [Forms]![frmHolidays]![frmHolidaySub]![cmbPName]

I also added the Me.Painting = False and Me.Painting = True to the CmbPName AfterUpdate event to speed up the display a bit.

I think this does do what you want but I have not tested the other facilities like adding new holiday events, new departments, etc so I don't know if you might have any problems with that so I will leave that up to you.

Good luck with the project,

Peter.

 ROOSTER_Planner_V2_Werkbestand__2_.zip ( 206.71K ): 102
 

Posted by: mrcunknown Nov 6 2018, 06:03 AM

thnx! that does the trick :-). This is very helpfull

Posted by: mrcunknown Nov 11 2018, 03:54 AM

@pctechdr

Hi Chris, i was bussy with the report from tblHolidayDates. Only i can not get get it like i need. I mean that when i choose a department i get a calendar like the picture. Do i have to make a new query or something?



 

Posted by: mrcunknown Nov 22 2018, 04:27 PM

Is there possibly somebody who can help me in this? pullhair.gif

Posted by: Peter Hibbs Nov 23 2018, 05:55 PM

Hi mrcunknown,

Perhaps you can upload a copy of your database with a detailed description of what you are trying to do and what problems you are having. Just showing us a screen shot on its own does not really help much.

Peetr Hibbs.

Posted by: mrcunknown Nov 25 2018, 01:40 AM

Hi, Ofcourse.

What i mean is; I have a record tblWeekData that displays all employees for the first 28 days. Problem is that it doesn't show the date in top. I would like that in form frmHolidays there is a button that wen i select a department i only get a report (print) for that department.

 Planner_V2_Werkbestand__3_.zip ( 215.81K ): 57
 

Posted by: Peter Hibbs Nov 25 2018, 07:44 AM

Hi,

OK, try this one.

I have added a button to the Calendar form called Print Report which opens the report in Preview mode (but you can change that to Print mode if you want to send the report to the printer immediately).

I am not entirely sure how you wanted the dates to be displayed at the top of the report so you will just have to check it out and make any changes that you need. Hopefully this will be something like your requirements. The dates and week numbers on the report are inserted using some VBA code in the report itself so you can make any changes to the code there, if necessary.

Good luck and let us know how you get on.

Peter.

 ROOSTER_Planner_V2_Werkbestand__PH_.zip ( 199.28K ): 82
 

Posted by: payfast8898 Dec 22 2018, 12:19 PM

Hey Peter this is great but I have two questions maybe you can help with.

1. I inherited an very old access employee database and all the employees from 1 to 116 are gone and deleted.
so the issue is your planner don't work starting at 116 in the employee id. at this point I can't change their id's because they are imbedded in hundreds of thousands of records. I can just import them into your table and re auto number them but I was wondering if there was an easy way so I don' have to do all that?

2. when I click on a employee date it requery's back to the top of the list, is there anything we can do about that?

Thanks for your time
Bob

Posted by: Peter Hibbs Dec 23 2018, 09:58 AM

Hi Bob,

I am not entirely sure what you are trying to do but if you want to populate the Holiday Planner Employee table with your own employee names and you want to keep the EmployeeID numbers the same then there are two methods you could use. It is possible to import numbers into an AutoNumber field using VBA code and then set the next number in the sequence to the last number plus 1 and I can give you more details on that if you want to go that route.

The second option is to convert the AutoNumber field in the table to a normal Long Integer type, keeping the Index property as Yes (No Duplicates) and then you would have to add some code to your form so that the next highest number was used as the EmployeeID number each time you add a new employee record. I would suggest that this is a better option because it give you more control of the EmployeeID field values if you should ever need to make any more changes in the future.

Regarding the second question, it seems I was a bit too liberal with the Me.Requery command in the code and I think you can actually dispense with most of them. Assuming you are using Version 2 of the demo, I suggest is that you modify the code (after you make a back-up copy of the database first, of course) like this -

In the form frmHolidaySub wherever you have the bit of code like :-

CODE
    UpdateChart Parent!txtDate                                      're-display chart
    Me.Requery                                                      'and requery subform

you can replace that with :-
CODE
    Me.Painting = False
    UpdateChart Parent!txtDate                          're-display chart
    Me.Painting = True

so you can do that on th button events Private Sub cmdAddOneYear_Click() to Private Sub cmdSubOneYear_Click(). This will prevent the form scrolling back up to the top of the form when you use the buttons to move through the weeks.

You can do the same in the Private Sub Form_KeyDown and Private Sub cmdDates_MouseDown events.

For the Private Sub cmdDates_MouseUp event you need to do something similar like this :-
CODE
        Me.Painting = False
        Me.Recalc                                                                                        'and requery subform
        UpdateChart Parent!txtDate                                                                          're-display chart
        Me.Painting = True

Here we need the Me.Recalc command to refresh the data but this does not reset the form like the Requery command does.

You could probably use the same techniques for the Requery commands in the main form but I have not had time to check those out so I will leave that to you.

Hope this helps but let me know how you get on and if you need any further assistance.

Peter Hibbs.

Posted by: payfast8898 Dec 23 2018, 12:36 PM

Hi Peter thanks for getting back to me

I used refresh to replace the requery's and worked great not sure if it was supposed to. I will switch it to your code.

As for the first part:
Playing around I did convert numbers to normal long integer just to see if that was the issue but no. my numbers are still 116-252 and it will not let me save a record.
I thought it was something to do with the starting position or gap between auto number field Employee ID in my system. As I Said I played around and deleted the Employee ID in my system and put it back in to re number my employees 1-78 and works great! when I import them it goes from 116-252. my would not save the record. However when I ran the same data in your system/download it worked fine. it was really weird if I keep the numbers starting at 1 it would work in my system but starting at 116 it would not and both work in your download so it is something to do with my old system so what I will do for now is use it as an external stand alone application if they want to use it.

No worries about that we are working on upgrading all our older software's to the new offices.

If you take suggestions or request this would be perfect if it automatically added the work day hours to a hours field based on the employees work day hours field when doing the date or date range so if it was one day would automatically put 8 hours for example. then on edit if it was half day we could change it to 4 or 2 etc. if they did a range would be like 5x8 for 40 hours a week. I know to add a display in the graph chart would be crazy to tell how many hr it was at a glance if not a full day but what about use one end of the image < instead of the <> so people could see it is not a full work day.

Any how you do great work thank you for sharing and happy holidays!


Posted by: Peter Hibbs Dec 23 2018, 05:09 PM

Hi Bob,

I used refresh to replace the requery's and worked great not sure if it was supposed to. I will switch it to your code.
I thought that Refresh would not work in this situation but if it works for you, then fine.

Playing around I did convert numbers to normal long integer just to see if that was the issue but no. my numbers are still 116-252 and it will not let me save a record. I am not quite sure what you are doing exactly but I sounds like you are not resetting the AutoNumber field to 0 after deleting the records. Try deleting all the Employee records in the new database and then Compacting & Repairing the database which will reset the AutoNumber field (EmployeeID). Then import the new records again.

What about use one end of the image < instead of the <> so people could see it is not a full work day. OK, the attached Version 3 has the option to save and display the holiday period that use a half-day at the start or end of the period. I guess it would not be too difficult to calculate the hours used for each holiday period but I do not have the time to look at that at the moment, maybe after Christmas, if I get some time.

Anyway, have a play with this version and let me know what you think.

Peter.

 Holiday_Planner_V3.zip ( 224.85K ): 81
 

Posted by: payfast8898 Dec 23 2018, 09:26 PM

Awesome definitely heading in the right direction I can see this being used for scheduling shifts and work days for nursing especially. they are always looking for something graphic.

I do think an hourly or numerical amount to go by would be awesome too, and the different graphic that represents a none full day is perfect. A lot of people have normal work weeks from Tuesday to Saturday or Sunday to Thursday for example or work 4 10 hour shifts, some nurses work 3 12 hour shifts for example so I think all we would have to do is in the employee table establish a normal work week and work day (hours). Help with the week end calculation I'm thinking.

I don't think the painting is working very well. sometimes I click and it don't show up until I click on another spot. I replaced with refresh for now and works good have not had any issues.

You didn't tell me about clicking on one side of the time slot gives you half a day and the other full day. drove me nuts for a second but brilliant thinking.

I wish access would fix that flickering on reformatting stuff on the new accesses. I'm running your systems side by side with new access and the old and you can barely tell any flickering on the old the new is terrible, try not to use red in anything so not to send someone into a seizure lol.

did you ever make a report to edit/add to the tblholidaytypes table?

thanks for playing around today changes are awesome hope you have a Merry Christmas.
Bob

Posted by: Peter Hibbs Dec 24 2018, 11:25 AM

Hi Bob,

I do think an hourly or numerical amount to go by would be awesome too, and the different graphic that represents a none full day is perfect. A lot of people have normal work weeks from Tuesday to Saturday or Sunday to Thursday for example or work 4 10 hour shifts, some nurses work 3 12 hour shifts for example so I think all we would have to do is in the employee table establish a normal work week and work day (hours). Help with the week end calculation I'm thinking.
It sounds like you would need the time scale along the top of the chart to be in 'hours' rather than 'days' if you are trying to display shifts of 10 hours per person. Perhaps you could elaborate on what this form is being used for. I am guessing you are trying to display the shift patterns (rather than holiday dates) for a bunch of nurses and also you want to calculate the total hours used for each person, presumably to calculate salary or something similar. Rather than try and modify this demo database to do what you want, perhaps it would be better to start from scratch and design a form that does exactly what you want. So would it be possible for you to show us what the ideal chart would look like, you could knock up a demo form using Excel or Word or something similar so that we can see how this chart should look and then it may be possible to do something like it using Access.

I don't think the Painting is working very well. Sometimes I click and it don't show up until I click on another spot. I replaced with refresh for now and works good have not had any issues.
Hmmm, not sure why that happens but this sort of code can be very 'flaky' at times. I guess it could be timing issues with different computers or whatever.

You didn't tell me about clicking on one side of the time slot gives you half a day and the other full day. drove me nuts for a second but brilliant thinking.
OK, sorry about that, I was sending that post very quickly with someone else in this house moaning that we had to get down to the shops before they got too busy!!!

I wish access would fix that flickering on reformatting stuff on the new accesses. I'm running your systems side by side with new access and the old and you can barely tell any flickering on the old, the new is terrible.
I quite agree, the A2003 version that I originally used for this project was very good with negligible flicker but the A2013 version that I use now is a lot worse. However, I very much doubt whether Microsoft will do anything about it now.

Did you ever make a report to edit/add to the tblHolidayTypes table?
As a matter of fact I did, for another project which you can download from http://www.UtterAccess.com/forum/Continuous-Form-Multiple-t1968354.html You could probably incorporate the relevant form into this demo, if you needed to. Also, if you need more information about the Public Holidays facility you can find that http://www.UtterAccess.com/forum/Add-National-public-Hol-t1995807.html

This sounds like an interesting project (I have done similar charts to this in the past, some of which are in the UA Archive) but there is possibly another technique you can use which works a lot better for this type of display but whether it will work for you depends on a few factors, if you can answer the questions below I can give you a few more details if it would be a viable proposition in your situation.

(1). What version of Access are you using for this project and is it the 32 bit or 64 bit version of Access?

(2). What are your skill levels in VBA coding?

(3). Is this database being used by just you or is being used by multiple users over a LAN or by users at a different site or what?

(4). Which country are you in (date and currency formats vary from country to country)?

Let me know your thoughts.

Peter.

Posted by: payfast8898 Dec 24 2018, 01:45 PM

Hey Peter I'm in NY USA.
Everything I do is for multiple users multiple sites. we have a lot of uses vpn in or rdp depending on security needed.
access 16 32 bit now covering everything over from 2000 some of the software is pretty much 20 years old.
on scale of 1-10 VBA skills, probably 6 or 7.

Nothing I need has to be much more elaborate. right now working on vacation planner part of it. when I showed the girls who would be using it the first question was how do we know how many hours the dash or image represented. I said we can make it so you can click on it and see the hours and I got the eye rolls they want to see the week by week but be able to see if it is 40 hours they are off or just 4 hours someone needs off for a dr's appointment if that makes sense. when they create the date range they can type in the hours it will be they don't mind that or have a list box 1-8 sometimes I create for them to make things easier. so basically they would click the day and if it was something different than 8 like how you have it if you click on the right side that it pops up and they can add if it is 2 hours or 4 hours or 5 hours etc. then save. pretty straight forward. what the trick is they would like to see on that graph if it was different than 8 hours to show 2hr, 4hr, 5hr or something so they know at a glance. I don't think it really matters for the vacation planner as long as the we have a different image to show it's not a full day. they can click on it and see the time. we could add a start time and end time again that is something they would enter manually anyways and that is what they do now so again we don't need much.
I do know where you are going on the nurse scheduler. right not they use codes like E312 for a 12 hours shift E2 for a straight 8 hour shift I don't remember all the codes but they don't need a hour graphic I think it would be too much for 35 nurses and 7 secretaries, they would just need to know the amount of hours someone was working at a glance to make sure that shift was covered.

ok heading out to a Christmas party have a great Christmas and thanks.

Posted by: Peter Hibbs Dec 27 2018, 06:56 AM

Hi Bob,

OK, thanks. My alternative suggestion was going to be to use a 'Third Party' ActiveX control instead of the Continuous type form which would do what you want but with multiple users on multiple sites, I don't think that would be very practical.

As I understand it, you want to be able to display the chart for your users but they need to be able to see their shift times without having to click any buttons, or whatever. You say that there are 8 possible shift periods and possibly some more shorter periods so I cannot see any easy method to show all that information. Remember, however, that you can change the colors of the lines and also change the shape and color patterns of each line so all I can suggest is that you create a different type of line in a different color for each possible shift period that you might have. I am not sure how many that would be in total so the users would have to remember which color/shape represents which shift (you would probably need to show an example of each type on the form for reference) but I cannot think of anything else at the moment.

Anyway, let us know how you get on and good luck with the project.

Peter.

Posted by: masa1 Dec 28 2018, 05:13 AM

Hello Peter,
This is a great example
I follow your projects with interest.

Regarding THIS, I have the following questions:

- how should the code be changed so that when the holidays are selected (frmHolidays> FindHolidays> open selected record) the dates displayed represent the same period? (Pic01)

- I made a small modification (for my needs) by adding text fields "outstanding annual leave" and "annual leave" (ONLY annual leave no other!) but I do not know how to sum them up - can I ask for help?

The "outstanding annual leave" and "annual leave" fields have been added to the "tblEmployees" table.

Best regards.
Andy.

 

Posted by: Peter Hibbs Dec 28 2018, 11:37 AM

Hi Andy,

You are right, this was a bug that I missed but you can fix it easily. You need to add one line of code to the database like this :-

CODE
Private Sub cmdFind_Click()

Dim vDate As Date
Dim vEmployeeID As Long

    gRecordID = Me.WindowTop + 600                                                                      'set gRecordID to Window + n for called form

    DoCmd.OpenForm "frmFindHoliday", , , , , acDialog                                                   'open Find Holiday form
    If gRecordID = 0 Then Exit Sub                                                                      'abort if user cancelled
    vDate = DLookup("StartDate", "tblHolidayDates", "HolidayID = " & gRecordID)                         'fetch record StartDate
    vEmployeeID = DLookup("EmployeeID", "tblHolidayDates", "HolidayID = " & gRecordID)                  'fetch record StartDate
    Me.txtDate = vDate - Weekday(vDate) + 1                                                             'calc first day of current week (Sunday)
    UpdateWeekDates Me.frmHolidaySub, Me.txtDate                                                        'display week dates and week Nos at top of subform  << INSERT THIS LINE HERE >>
    UpdateChart Me.txtDate                                                                              'show chart

'===== Remove next three lines to prevent 'Holiday Dates Editor' form opening immediately =====
    DoCmd.OpenForm "frmHolidayEdit", , , , , acDialog, vEmployeeID & "," & vDate                        'open Holiday Edit form
    If gDummy = False Then Exit Sub
    UpdateChart Me.txtDate                                                                              're-display chart
'============================================================================
==================

End Sub

Insert the new line of code as shown above immediately after the Me.txtDate = vDate - Weekday(vDate) + 1 line.

I am not entirely sure about your second question as I don't have a copy of your database but if you just want to add two fields together you could try entering this line in the Control Source property of the totals text box control :-
It would be something like - = [Zalegly urlop] + [Nalezny Urlop]
This assumes that the two Text box control have the Name property that I have used above, if they are different then you need to use whatever Name you used for controls. If it is possible that one or both of the values in the two controls could be Null then you will need to use the Nz function to force it to a zero value, something like this - Nz([Zalegly urlop],0)
You could also do the addition in the query if you are using a query to fetch the data for the form, it all depends on how you have set up these new fields (I am also assuming they are Numeric fields).

Hope that helps and let us know if you have any problems.

Peter.

Posted by: masa1 Dec 28 2018, 12:40 PM

I'm sorry, I asked a question too quickly ...

I managed to solve this topic myself
!

....................
- I made a small modification (for my needs) by adding text fields "outstanding annual leave" and "annual leave" (ONLY annual leave no other!) but I do not know how to sum them up - can I ask for help?

Posted by: masa1 Dec 28 2018, 01:00 PM

Hi Peter,
thank you for Your answer.

Of course ... "Insert the new line ..." solved the problem!

During tesu I noticed that you can't mark "PublicHols" if it falls on a Saturday or Sunday because it generates a value of -1 (!)
Best regards.

Andy.

Posted by: Peter Hibbs Dec 29 2018, 05:57 AM

Hi Andy,

During testing I noticed that you can't mark "PublicHols" if it falls on a Saturday or Sunday because it generates a value of -1 (!)

That is because the program calculates the holiday period for working days (Monday to Friday) only. If you want to include weekend days you will need to modify the Workdays function in module modGeneral so that it does not exclude weekend days but does still allow for half-days (if you are using those). If you need any help with that code then post back with your requirements.

Peter.

Posted by: mrcunknown Jan 8 2019, 03:08 PM

I'm realy glad with this and i have it up and running at work. But i have a question. Is there a possibility to have also a read only form? I use this planning in a dashboard but not everybody must be alble to change things in the planning.... they can look but not change. is something possible?

Posted by: Peter Hibbs Jan 8 2019, 06:07 PM

Hi,

Well I guess you can just disable the cmdDates button on form frmHolidaySub to prevent certain users adding a new holiday to the chart and also button cmdNames to prevent users opening the Employee Record form (unless you still need this option in which case you would have to change the code to prevent a new record being added on the form itself).

You would probably also need to disable the cmdUpdate button on form frmHolidayEdit to prevent the user changing the holiday after a search operation and also disable the cmdDelete button on the same form to prevent the holiday record being deleted.

If you want to prevent a new name being added to the list then you would also need to disable the cmdAddNew button on the main form.

HTH

Peter.

Posted by: masa1 Jan 10 2019, 05:30 AM

Hello Peter.
I test Your project and try to adapt it to my needs ... confused.gif

I can ask for help in the following case:

I need a field in frmEmployee (next to the txtTotal field) in which the number of eg holiday leave in the current year or for all years is displayed!

Any suggestions or example code?
Thank you for your help.
Best regards.

Posted by: Peter Hibbs Jan 11 2019, 06:15 AM

Hi Andy,

Which version of the planner are you using? If you download Version 3 (which you can find in Post #40 above) you will see that I have added that facility which you may be able to modify to do what you want (if it doesn't already).

Let me know how you get on.

Peter.

P.S. Just realised that you must be using that version since it is the only one that has a txtTotal field on the Employee form. So the question now is - why does the total here not show what you want?

Posted by: masa1 Jan 11 2019, 07:44 AM

Hello Peter.
I had and I have a problem with adding text fields in which the number of days eg Holiday leave would be displayed.
I wanted to present this field as the sum of the number of days spent used and planned for the current year, then calculate unused holiday leave.
In your example there is a "sum" of all absences.
Of course, I managed to do this by adding the criterion in the modified query "qryHolidayListUrWyp" - (my name)
However, since the last entry I managed to achieve the intended goal by repeating the database objects.
In short, I repeated the query, the list field and made relevant changes to the VBA lines to get the expected result. Surely I did something stupid, which an experienced database designer would not do, but it works, maybe with a small shortcoming but later if I can not help myself!

In my solution, I can simultaneously view all the absences of an employee and only vacation leave, which is convenient for me.


Using Your kindness, I would like to ask about the possibility of adding the following "function" ...
By clicking the appropriate date (day) I would like to get information on how many employees are present on a particular day?


For example, 100 people are employed, 7 people are on vacation, 3 people are on sick leave, ... what percentage is present at work?
Best regards.
Andy.

 

Posted by: Peter Hibbs Jan 11 2019, 02:30 PM

Hi Andy,

OK, it sounds like you have solved one question.

Using Your kindness, I would like to ask about the possibility of adding the following "function" ...
By clicking the appropriate date (day) I would like to get information on how many employees are present on a particular day?

Interesting question but I think I need a bit more information to give you an answer.

(1) Are you using half days or only whole days?

(2) Are you saying that you want to be able to click on one date on the main chart and (presumably) display a form which shows the number of employees that are working, on vacation or on sick leave for that date. Is this correct?

(3) It should be possible to count the number of days an employee is on vacation or on sick leave but what about the employees that are at work, do you show those on the chart in a different color or do you not show them at all and we should assume that if there is no colored line for an employee, then they must be at work?

(4) How many different categories are you using and want to show? There were 7 on the original design (Annual Leave, Compassionate Leave, etc) so what are you using now?

I will give it some thought when I have more information.

Peter.

Posted by: masa1 Jan 12 2019, 06:17 AM

1. I will only use whole days

2. The most important to show how many people are at work and how many are absent.

However, depending on the method used to solve the problem, showing all categories may be advantageous.

3. If there is no colored line, it means that the employee is at work, he works 8 hours and this is the default value (that would be very convenient).

I could then count how many hours the employee worked in a month.

4. The number of categories seems to be sufficient, or you can add the 8th category "Others".

Regards, I will be here often.

Andy

Posted by: Peter Hibbs Jan 13 2019, 11:33 AM

Hi Andy,

OK, try the attached version and see if it helps.

The way it works is this - the user can click on the day of the week label at the top of each date column (i.e. Sun, Mon, Tue, etc) which will then display a pop-up form which shows the number of employees that are absent for that date only and for each type of leave category. It also shows the total number of employees and the percentage of employees who are working on that date. I am not sure if this is exactly what you wanted but if not, then maybe you can extract any forms or code or whatever to use in your own database.

If you want to use the form as it is then you should be able to import the forms frmStaffing and frmStaffingSub, the query qryStaffing and make a small modification to the form frmHolidaySub (see below). I have also added the extra category record to the table tblHolidayTypes that you mentioned above called 'Other' which you may need to copy over as well.

The important item is the query (qryStaffing) which calculates and sums the number of employees for each category that are absent for the selected date and then that query is used for source of the subform (frmStaffingSub) on the pop-up form.

If you are intending to use this method as it stands then you will need to add a button and some code to one of your forms which is used to open the pop-up form for the selected date. To do this you should add a new button to the subform (frmHoldaySub), rename it cmdStaffing and position it exactly over the row of day-of-week labels (Label1 to Label28) at the top of the form. Change the Transparent property of the button to Yes and then add the code below to the Mouse Up event of the button.

CODE
Private Sub cmdStaffing_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)  '*** Code added to determine which date user clicked ****

Dim vDayNo As Long
Dim vDate As Date

    vDayNo = (X / Me.Label1.Width) - 1                      'calc Day No from X co-ordinate of button cmdStaffing
    vDate = CDate(Me.txtWeek1) + vDayNo                     'add day No to first date to get selected date
    DoCmd.OpenForm "frmStaffing", , , , , acDialog, vDate   'open form and pass date to form as OpenArgs

End Sub

or just copy it from the demo into your project.

What the code does is this - it uses the X co-ordinate of the mouse when the button is clicked to calculate the horizontal position of the mouse on the button and then adds that to the first date on the chart to produce the correct date which is then passed to the pop-up form for the query, etc. Pretty simple and is easier and quicker than adding a Click event to all 28 labels to do the same thing which you would have to do otherwise.

Anyway, have a play with this demo and see if you can use it to do your calculations in your own database.

Good luck,

Peter.


 Holiday_Planner_V3a.zip ( 238.1K ): 130
 

Posted by: masa1 Jan 14 2019, 07:25 AM

Hi Peter,
Again I have to write that what you do is unimaginable.
This is exactly what I expected and I certainly would not have done it myself! thumbup.gif

The only thing I noticed is the inaccurate color reproduction in the frmSaffing form, but this is a very small inconvenience, the most important are "percentages".

By the way, I would like to ask how many changes should be made to go back to the "whole" days?

I was considering using the ROOSTER_Planner_V2_Werkbestand__PH_.zip version, but this version is clearly flaring.

Best regards.

Posted by: masa1 Jan 14 2019, 12:18 PM

Hi Peter,
maybe you can tell what is the reason for the following "error";

All COPIES v3 that I have on disks, after running the "last with percent", have stopped displaying the number of absences.confused.gif
This is happening on Windows 8 x64 and Office 2010 x32.


Previously (today morning) I tested on Win Vista x32 and x32 Office 2007 and the number WERE!

I have bad luck, I recently crashing OTHER entirely created by me database - "Unrecognized database format ..." - and it turned out that this is Microsoft released a "bug" in Windows 10... it was deadly!
Best regards.
Andy

I am sorry, this is the original version without any changes.

 

Posted by: Peter Hibbs Jan 14 2019, 01:46 PM

Hi Andy,

I am afraid I don't know why you are getting these errors unless it is some corruption in the forms or code. I suggest you try and De-Compile the database which can often fix problems like this (Google 'Decompile in Access' for more information).

Incidentally there is a minor bug in my code which you should fix before you do anything else. Open the frmHolidaySub form in Design mode and switch to the VBA code module for that form, find the Private Sub cmdStaffing_MouseUp event and change the first line of code to look like this :-

CODE
    vDayNo = (X \ Me.Label1.Width)                          'calc Day No from X co-ordinate of button cmdStaffing

That is, change the divide sign to Integer Divide and remove the -1 after the calculation. This will make the mouse pointer on the labels more accurate.

Also, you asked if we could remove the Half-Days facility - the easiest way to do this is to open the module modGeneral and change the first line of code to this :-
CODE
Public Const conHalfDays = 0

Obviously this will not actually remove the extra check boxes and fields in the tables but it would be easier than trying to change the forms, tables, queries and VBA code.

Also you mentioned that the colors in the new pop-up form do not match the colors on the main chart - I was aware of that but I did not have time to do them properly as it is not easy to choose exact colors on the Conditional Formatting form for a control but I am working on that to work out the color codes for that control and I will post those here when I have sorted it.

Let me know how you get on.

Peter.

Posted by: masa1 Jan 14 2019, 02:23 PM

Hi Peter,
I think, keeping all respect that Microsoft is sick.

I just launched the same file without De Compilation in the last version on the virtualna machine - ONLY on Windows 7 , Office 2007 - and everything is OK!

Again I found a problem that no one knows about.

I changed "vDayNo = ..." but I did not notice the difference.

Best regards.
Andy.

I made De-compile;

"C: \ Program Files (x86) \ Microsoft Office \ Office14 \ MSACCESS.EXE" "C: \ Users \ as \ Desktop \ PrinterSelect \ V3aPH \ Holiday Planner V3a.mdb" / decompile

I made a compact and repair, then Debug and then SAME ...!

Peter, I'M SORRY I do not blame you, but Microsoft wants to finish me off.

Posted by: mrcunknown Jan 15 2019, 02:42 AM

Thnx Peter ... i will give it a go :-)

Posted by: Peter Hibbs Jan 15 2019, 06:35 AM

Hi Andy,

Regarding the colors for the pop-up form which don't quite match the main chart you can fix this by adding the the code below to the Open event of the frmStaffingSub form like this :-

CODE
Private Sub Form_Open(Cancel As Integer)

Dim rst As Recordset

    Me.Painting = False
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblHolidayTypes ORDER BY HolidayType")     'select all records from tblHolidayTypes
    Do Until rst.EOF
        Me.LineColor.FormatConditions(rst!HolidayType - 1).BackColor = rst!LineColor            'and change BackColor of CF condition
        Me.LineColor.FormatConditions(rst!HolidayType - 1).ForeColor = rst!LineColor            'and change ForeColor of CF condition
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Me.Painting = True

End Sub


What this does is it copies the color codes from the tblHolidayTypes table into the formatting codes of the LineColor control on the form so that the colors on the pop-up form (using Conditional Formatting) will always match the colors on the chart (if you should ever change them at a later date).

Regarding the mod' to the code for the vDayNo variable, you probably won't notice the difference but what happened with the original code was that if you clicked on the left hand edge of the weekday name labels it would use the previous date rather than the date you clicked on and this is because the calculation used the 'normal' divide function which returns a bunch of decimal points and so messed up the values slightly.

HTH.

Peter.

Posted by: masa1 Jan 15 2019, 02:21 PM

Hi Peter,

Thank you for the next portions of code and previous advice.

I have introduced all "updates".

Of course, Your updates are very good!
In reference to my previous problems, it was enough to "fix the MS Office package" and the errors resolved. dance.gif

I am sure I will need some help, but I have to work a little bit myself. cheers.gif
Best regards.
Andy.

Posted by: masa1 Jan 17 2019, 02:32 PM

Hi Peter,
I'm trying to further modify your application to suit my needs ...

I have to show for each employee each month how many days he was not present and then count the number of hours worked.

I found the SQL code of the query with the help of which I can calculate - every month - the number of absence days, even if the absence lasts from 2018-12-24 to 2019-01-03 (HolidayID = 194) and I get 8 days in December 2018 and 2 days in January 2019.

(h t t p s://www.experts-exchange.com/questions/28981903/How-to-count-days-per-month-between-two-dates-using-an-Access-function.html)
SELECT DISTINCT
tblHolidayDates.HolidayID, tblHolidayDates.StartDate, tblHolidayDates.EndDate, 10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10) AS Factor,
Year(DateAdd("m",[Factor],[StartDate])) AS [Year], Month(DateAdd("m",[Factor],[StartDate])) AS [Month], IIf(DateDiff("m",[StartDate],[EndDate])=0,
DateDiff("d",[StartDate],[EndDate]), IIf(DateDiff("m",DateAdd("m",[Factor],[StartDate]),[EndDate])=0, Day([EndDate])-1,
Day(DateSerial(Year(DateAdd("m",[Factor],[StartDate])),Month(DateAdd("m",[Factor],[StartDate]))+1,0))
-IIf([Factor]=0,Day([StartDate])-1,0))) AS Days, DateDiff("d",[StartDate],[EndDate]) AS DaysTotal
FROM msysobjects AS Uno, msysobjects AS Deca, tblHolidayDates
WHERE 10*Abs([Deca].[id] Mod 10)+Abs([Uno].[id] Mod 10)<=DateDiff("m",[StartDate],[EndDate]);




That's OK, I need it.
However, the execution of the query takes a long time - about 12 seconds and records of annual leave in This trial version is only 122.

My questions:

1. Did I solve my task correctly?

2. If the database will contain several thousand records, I will wait for the completion of calculations?

3. Do you know a different, more efficient solution?


Perhaps, to avoid long calculations, leave should be done on the last day of the month and the successors continue from the first day of the following month?

Best regards.
Andy.

 

Posted by: Peter Hibbs Jan 18 2019, 07:06 AM

Hi Andy,

Well, this sounds fairly complicated and would need some time to come up with a good solution and unfortunately I just don't have the time to look at this at the moment so all I can suggest is that you start a new thread and see if anyone else on the forum can give you some advice.

Sorry I can't be of more help at this time but hopefully you can find a good solution to the problem.

Good luck with the project.

Peter.

Posted by: Mickjav Jan 18 2019, 02:48 PM

Hi Andy,

Peter has been kind enough to allow me to add his holiday to my Calendar example Here https://access-programmers.co.UK/forums/showthread.php?t=303130

I intend a number of edits which I am still avaluating but It will be at least a week before I will be able to look at your problem but will add it to the project as I think Coding it would work better and it interests me lol

mick

Posted by: masa1 Jan 19 2019, 06:41 AM

Hi Mickjav,
Thank you for joining the project.
I think that you will find a solution to the problem described more quickly.

Unfortunately, the solution that I have placed does not suit my needs. thumbdn.gif

I will also be looking for a different solution.

Best regards

Posted by: Mickjav Jan 19 2019, 08:36 AM

From looking at your post and the requirements I think you are missing key items in your db as you want to track an employees hrs and a brakedown of that.

so off the top of my head you'll need to store the hrs worked for a given day (My firm works 8:30 hrs monday- Thursday and friday 5hrs) if an employee went of to the dentist he/she may have only taken 3hrs unpaid.

thats just the beginning it's a very complex beast as employees can be a nightmare lol

Once a have a schema I'll post a more detailed list.

Posted by: masa1 Jan 19 2019, 10:13 AM

Hey Mickjav,
I'm not sure if I understood your text well ...?

It is possible that at the beginning I was doing a very big mistake.

My goal is to make a simplified database, without a detailed account of the employee's appointment at the dentist. (!)
(In the future, this aspect can be taken into account.)

My scheme is to look like this:
name of the month, e.g. January (2019)
January has 31 days, 4 Saturdays, 4 Sundays and 1 Public Holiday.
Employee, for example, DonaldTrump had vacation leave from 27/12/2018 to 11/02/2019, ie 11 working days, but 3 of those days were in December 2018 the remaining 8 in January 2019.
The company works from Monday to Friday, 8 hours a day.
Calculations

31 - 4 - 4 - 1 - 8 = 14

The employee worked in January;

14 x 8 = 112 hours

This is the value that I would like to get in the report.

Best regards

Posted by: Mickjav Jan 19 2019, 11:35 AM

27/12/2018 to 11/02/2019

If your firm returned to work on 2nd of jan then the working days would be 29 Days or am I missing something?

The dates for my firm before 2nd of jan ware all company closure dates I.E. Christmas/New year

Posted by: masa1 Jan 19 2019, 12:35 PM

I'm sorry, my mistake!
Vacation ends 11-01-2019r

Posted by: Mickjav Jan 20 2019, 02:51 PM

I have just starting working on my update to peters holiday planner and have said I would post updates

I have found that the [No Of Employees] seems to only count those with department so made the following change in image attached I have updated the query name on mine so check but I think it will be Employee and not EmployeeName



I have also made a number of changes in the Initialise Sub See image Attached
NOTE: I have also edited the INSERT Startment but still testing that as only just edited the table so will cover that when I'm happy it's working correctly


Before you edit out the deleted query you should add the following sub query in QryEmployees EmployeeID Criteria
Not In (SELECT [EmployeeID] FROM [tbxWeekData])

This will then only add new employees to the employees table

I am taking thses steps at the moment to cut down on the potential bloting.

Im attaching images for the employees table with additional fields plus a shot of the holiday planner these are a work in progress and I understand not all will agree but should you see something you like then let me know and I'll create a howto

 

Posted by: masa1 Jan 21 2019, 02:17 AM

Hi Mickjav, compute.gif

I read the information from you with interest.

My observations...

1. I have found that the [No Of Employees] seems to only count those with department...

I do not know what you mean?
Just remove the department name in the employee form and it turns out that it is still counted.

I'm right?

If you can do is try it a change in the form of "frmStaffing" to refer to the department or to all departments!
If you click on the selected field of the day you should receive a message to enter the name or department number or everything.
That would be good!

2. Maybe I misunderstood, however after updated query and disabling the line of code;

..."CurrentDb.Execute "DELETE FROM tbxWeekData"...

every time an employee clicks on the list causes "ERROR", while adding a new employee is done correctly?

3. I do not understand the connection of the "Start Date" field, it is possible that this information has been from when I started my work but in my opinion it is unnecessary in this form.
However, if I understand correctly the next fields "Days" and "Left" show how much leave the employee had and how much used?

That would be good!

4. Regarding the flickering of colors, I can conclude, based on my own observations, that it depends on the system, the office suite, and maybe first of all about the possibility (computer configuration).
Personally, I use three computers in different configurations and have a different reaction on each one - graphic display.

I apologize if I did not understand something.
Best regards.

Posted by: Mickjav Jan 21 2019, 01:16 PM

You forgot to add the sub query that limits adding existing records hence the error

Please reread the post and the comments relating to "Not In (SELECT [EmployeeID] FROM [tbxWeekData])"


I did say the count only played up for me after I had been playing so wasn't sure if it did for others but as you can't add records through the employees screen without a department It don't really matter.

your are correct about the additional fields once an employee uses there holiday allotment they will have to be added as unpaid there is a lot of work involved with the holiday types but I only itend giving users a good foundation they can build on.

Just as an example:
At present in the UK if you work 5 days a week you are entitled to 28 days holidays, some employeers will give you bank holidays Others will requre you to use your hoilday allotment
Then theres Maternity Leave I need to look into that one in relation to holiday allotments as I'm not delaing with wages I don't think I'll need to do much unless it effects the allotment.

Peters done a very good job and makes it quite easy to understand his code I can see another very useful use for this holiday planner.

I've atteched an image of the main screen, I've added start dates for al employees and worked out a basic holiday entitlment but that but shouldn't change much most of the work is in code now.

 

Posted by: Mickjav Jan 22 2019, 03:31 PM

Updated The display plus added in the Company Closure dates which I will have to edit the entry system for as my calendar example didn't need to keep the dates but I think this system should keep the old dates just in case they need to be referenced at a later date.

 

Posted by: Mickjav Jan 25 2019, 09:04 AM

V4 Update information

I think I now have all the defaults I will require to complete my works on this version (Image Attached)

Working Days: This is a list of days the company is open for formated into a string
Currently Loged in employee: this is used by all other forms that need to gather information about an employee I.E. is a manager, or admin etc
-------------Above Works Completed---------------------
Warn If more than x booked on same day: A lot of firms only allow a number of employees to be off on the same day but may allow more depending on the reasons Etc so a warning will pop up should the number be exceeded, If it is set to 0 then no warning will popup
Company allows half days Some companies don't allow half days to be taken as holidays "Mine Is one:(" so will be updating the holiday planner to check the state of this if false then all half days will become full days
Company pays bank holidays: in the UK companys don't have to pay for bank holidays but a lot do as a result this needs to be taken into account when adding holiday allotments if the company pays then I only have to subtract the company Closures/shutdowns but if not then I also have to subtract the public holidays.
Allowance last run: This is a system item and will be used buy the holiday allowance sub so it can only be run once a year, new employees get there allowance when entered
Manager Responsable for holidays the person responsable for dealing with holiday requests be they electronic or paper, If no manager selected the manager Assigned for each department(s) will recieve the holiday Requests

There is still a large amount of work to be done but the ground work has been done and I now know my way around peters code so it should'nt be too long

I've also updated my calendar with the new items/ formates like the public holidays and company closures plus corrected a few of my own short falls I.E I now use the working days to only add closure/shutdowns for days working and if there is a public holiday the box colour will use the public holiday and the box will display both the shutdown and public holiday text (See Image Below)

I've updated the public holidays display and added a combo box to make entering new public holidays easier this is a flat file system but have no plans at present to change it as I would only expect 300-400 entries over the systems life (Image Attached)

I have redesigned the company events which has now become the comany closures this has been completly recoded and is currently being tested see above notes referencing this object (Image Attached)


 

Posted by: Mickjav Jan 27 2019, 01:00 PM

Update Employees and departments

first image is the updated employees form with the new departments assignments/Edit system

if an emplyee is a manager and has departments assigned these will be filtered on the holiday planner (see second Image) Still a minor error I just found to correct but that error is a minor update

If the employee is an admin viewing the holiday planner then all records will be displayed as admin can see everything (Image 3) but I have also added a button at the bottom so that if admin is also managing departments they can update the display to only show there assigned departments employees (Image 4)

This has to have been the most complex work I've done on the holiday planner to date but am happy with the result.


 

Posted by: ixxo09 Jan 27 2019, 01:55 PM

Great Job. Excellent Updates!!!
Joe

Posted by: masa1 Jan 27 2019, 11:59 PM

Hello Mickjav,
Your work looks very interesting.

Can you share the demo for testing?

Best regards.

Posted by: Mickjav Jan 28 2019, 08:22 AM

Hi masa1 sorry but no I won't release it until I am happy all the works have been carried out but with the works left on my project management system it says it should only take up to a couple of weeks depending on how much time I can put to it.

Posted by: Mickjav Jan 28 2019, 04:31 PM

Posting this for peter I just created a new Leave type and had to edit the images thanks peter for pointing me in the right direction as me and images don't mix lol

The reason I wanted to add a new type is when I have a day off sick I don't like losing the money, as I get on with my manager he converts the sick day(s) to holidays for me.

Now I have that record you could add a report if needed to see how many days an employee has had off and how many were converted.

Also updated the days left for the new type.

 

Posted by: Mickjav Feb 2 2019, 04:27 PM

At last all my work on the holiday screen has been completed and had hrs of testing I am happy with it both when half days are allowed and not.

Had a lot of fun trying to get the total days booked for annual leave and the new Converted To Holiday In the end I created a table to hold just those types that are deducted from the holiday allotment This has the advantage of not slowing the system down but somebody will prob find a way around that lol.

I am renaming the db to employee example as I have decided that my current diary needs updating so will build a new one in the example for next version
Added a image of both below

 

Posted by: Mickjav Feb 4 2019, 04:13 PM

All I have left now is the holiday Allotements screen then just a bit of checking and adding comments where needed

this is the requests system I was in two minds how to go about this I.E. Code adding the dates of allowing the user to add the date in the end I went for the simple version which worked much better than I could have hoped.

The first image is of the main screen I'm using as a Nav and preferences screen just for this example.

You will see the last button is the requests button under this if the manager/Admin Has holidays to approve red text will be displayed when the screen opens
plus you can see a list of the current users requests with the status this screen allows the user to edit a request should it show as not approved/needs edit

The second image is of the holiday planner with the request assignments screen these two screens work together the list will display in the same place everytime (Thanks to isladogs For that bit of code)

The screen acts like the find screen but allows you to work with the holiday planner as well you can do multiple seaches the holiday planner will light up the employee for you and move the start date to the first week.

The third image is the request entry screen and yes I cheated I copied the holiday edit and recoded it

I am looking at saturday/sunday to being able to upload this, it will be added to a new topic as there is a lot I wish to to with this


Future Improvments:

The Time in service should allow for part time and full time employees it only allows for full time at present
I intend building a new employees works diary wich will be included with version 2 of the employees example



 

Posted by: Mickjav Feb 5 2019, 04:00 PM

Uploaded a copy of my Employee Example to a new topic of same name I will cross link as soon as it's approved

Posted by: Mickjav Feb 6 2019, 12:36 PM

I have added my demo/example https://www.access-programmers.co.UK/forums/showthread.php?t=303671

I did add it here but it's not been enabled so might be I haven't enough posts.

Posted by: Mickjav Feb 9 2019, 03:00 PM

It's taken me 15.43 Hrs To get to this point I've allowed 83 hrs for the complete works.

I found using the Colour system from the Gautt chart slowed it down to much as there are 18 fields so Added 20 conditions per field with set colours which I think will be more then enough maybe when I finish it somebody will find a way but for now there are 20 colours.



 

Posted by: Mickjav Feb 16 2019, 04:02 PM

I've added info for the up and coming update which includes the works Diary https://www.access-programmers.co.UK/forums/showpost.php?p=1612145&postcount=9

Posted by: Mickjav Feb 21 2019, 04:38 PM

Added version 2 of the employees System Example https://www.access-programmers.co.UK/forums/showpost.php?p=1612938&postcount=2

 

Posted by: mrcunknown Mar 7 2019, 02:34 PM

Hi Peter,

I'm still very happy with this ... the only thing what i can not accomplice is a read only form. If i make a copy from frmHoliday and call it for example frmHolidayReadOnly It does not show the planning. My Idea if the following; I have a login with different levels. level (admin) can make the planning and level (employee) can only look at it and cant make any changes)

I hope you (or anyone else :-) ) can help me with this

 ROOSTER_Planner_V2_Werkbestand__PH_.zip ( 199.28K ): 58
 

Posted by: Peter Hibbs Mar 8 2019, 07:26 AM

Hi mrcunknown,

OK, this can be done but you have not really provided enough detailed information on how you want this to work so this is just a guide, you will have to work out the exact code you need if this is not quite what you wanted.

There are a few different ways to do this but I think the simplest method is to add a Check box control to the main form which is set to False if limited access is required and True if full access is required. I have added a Check box control called chkAllowEdit to the main form and have left it visible for now so that you can change it manually, if required. When you have the new facilities working OK you should make this control invisible so that your users cannot change it. I have also added some code to the control's After Update event for the same reason but you can remove that code when it is working. I have marked all the additional code with ### so that you can find it easily.

I have also added a new temporary form called frmLogIn so that you can test these facilities more easily and this form now opens at start up. Again, you will need to remove this form and use your own form/s once you have decided what changes you need to the main form/s.

The simplest method, I would suggest, is to open the main calendar form (frmHolidays) from your log in form (or whatever you use) and pass a False or True value to the form in the OpenArgs parameter, False would allow read only access and True would allow full access, the code in the main form's Open event would then set the check box control to True or False and enable/disable any button or controls, as required.

There is some extra code in the sub-form (frmHolidaySub) which disables the mouse button event if the check box is set to False which prevents any new holidays being added to the calendar.

You have not said how you want to handle the various buttons so I have just disabled them if limited access is enabled. This means that the users cannot open the forms to add new employees, import public holidays, print a report, display employee holiday information or display holiday information by clicking on a colored bar on the chart. If you need the users to be able to show those forms but NOT make any changes to them then you will need to use a similar technique that I have described above, that is to pass the value of the Check box to the called form in the OpenArgs parameter and then add code to the individual forms to enable/disable the various controls, as required.

Hope that helps a bit and good luck with the project,

Peter.



 ROOSTER_Planner_V2_Werkbestand__2_.zip ( 190.49K ): 122
 

Posted by: mrcunknown Mar 10 2019, 05:11 AM

Cool ... this is what i mean :-) Thnx for youre help! i really appreciate it.

Posted by: mrcunknown Mar 14 2019, 06:19 AM

If tryed to find it in the VBA code but can not find it. in the view mode trey is also de Print report button disabled. How can i put this one on true. So people that only have the read function can also print the rooster.

Posted by: Peter Hibbs Mar 14 2019, 11:46 AM

Hi,

To enable the Print button all the time, first change the Enabled property of the button to Yes and then remove this line of code from the Open event of the form frmHolidays :-

CODE
    Me.cmdPrint.Enabled = Me.chkAllowEdit               'enable/disable button ###

HTH

Peter.

Posted by: slynch Jul 9 2019, 02:44 PM

Hi There Looking to add allotment count as seen in my screenshot. My Company only approves so man employees off at a time I would like the flexibility to define the allotments and be able to track when we have hit the limit so the a vacation can be declined. I would also like to filter/query the chart to display only those that have active vacations.

Thanks all.

SL

 

Posted by: Peter Hibbs Jul 10 2019, 09:03 AM

Hi SL,

OK, so as I understand it, you want to know how many employees have booked vacation days for each day that are displayed on the calendar chart and highlight any days that are fully subscribed based on some fixed value. That should be easy enough but it raises more questions that are addressed below. The way I would do this is to add 28 Text boxes to the Footer section of the form (one for each day obviously) and then calculate the total number of employees that have booked vacations for that date and show the total in the appropriate box (see screen shot below). Any box that has a value of 9 or more (I used 9 in this demo but that can easily be changed) are highlighted in red. The VBA code in module modGeneral has to be changed a bit but it is not difficult.

The first question is - you seem to have several categories of vacation in different colors on your chart, so do these employee totals refer to all categories or just some specific category?

Second, you have several different departments (East, West and Central) so do these totals apply to the employees in all departments or do you need separate totals for each department?

Your second request about filtering out the employees with no booked vacations can be done but the method used may depend on your answers to the above questions.

Anyway, let me know your thoughts on the above.

Peter Hibbs.



 

Posted by: slynch Jul 10 2019, 01:52 PM

Hi Peter...please see below answer to your questions.

The first question is - you seem to have several categories of vacation in different colors on your chart, so do these employee totals refer to all categories or just some specific category?

[SL] So only three categories will be considered in the total count as the other categories are not counted as Culpable holidays.

Second, you have several different departments (East, West and Central) so do these totals apply to the employees in all departments or do you need separate totals for each department?

[SL] There are on three Regions/Departments and each would have it's own allotment count so the totals are based on the region and their respective employees.

Just to add, in your screen shot I would do the opposite meaning show how many spots are allowed and subtract from it once someone books that date so the red would show 0 once its full if you get me.

Tks

SL

Posted by: Peter Hibbs Jul 10 2019, 04:57 PM

Hi,

OK, the totals can easily be filtered on specific categories and the totals can be calculated as descending as you suggest (although I am not sure if they could ever go negative if, for example, you allowed a vacation when a date had been fully used by other employees).

Anyway, what I am wondering now is how you propose to display this data since you want the totals for the three departments to be calculated separately! Do you want to show all the employees in East department and then below those names show the totals and then West and then Central department employees in the same way? I think that could be done but it would entail a lot more VBA coding. I see that you also have a Region combo box on your form, does this filter the list by Department and if so, how is this used in conjunction with the totals facility?

It looks like you have designed your own form (based on my original demo) so how do you intend to proceed? Do you have enough information to do this yourself now or do you need any further assistance or what?

More info please!

Peter.

Posted by: slynch Jul 10 2019, 09:07 PM

I did not have to modify your original demo by much at all because it dose 90% of what I need. Just to clear thing up a bit the combo box I added filters the department field which is the region (Department and Region are one in the same I just forgot to relabel department to region). The combo box just filters between the three regions. What I need help with is just to count vacation bookings against the allotment prescribed for the three regions with out any major coding reconstruction. Here are the three approaches that I have in mind.

Option 1 (most favorable): based on the region I select from the drop down it will show the allotments for that particular region and respective days that I can define because different regions would have different allotments. As shown in the screen shot attached (our current excel sheet), there are two counter rows. One for the region allotment and one for the actual count of employees booked/filled for that date. Im ok to replicate this logic however I do like your suggestion to just have one row that will show how many spots are left as they are being booked and yes there is a possibility that we could have a -1 count which will show that you have exceeded the allotment. Again if that logic requires to much code change im ok to have the double row of text boxes (52 in total) one for region allotment count and the other for the count of employee that have booked vacation in the selected region. My thought is that the region combo selection should filter what I need based on the region. So for example if I select the East region for Jan 1 the numbers will update to show the allotment count of 5 for example and it would also show that 4 people are booked for that day and so on. If I then need to book vacation for a employee in the west I would select it in the combo box and the numbers would update now showing and allotment count of 7 for the east and only 2 people booked for that day which means I have 5 slots available for that day in the east. If I select all regions it would just aggregate the numbers.

Option 2 Instead of dynamic filtering for the regions with the combo box I would just have three tabs one for each region and have their allotment and filled vacation count done that way. Its less dynamic but it would still work.

Option 3 (least favorable and would hate to do) have 3 separate forms 3 separate employee tables one for each region with their allotments and employee count.

I hope this make more seance now. Like I said this level of coding is quite above my skill set but I am willing to try because the gains will be massive and it will be worth it in the end. Your demo gets me to 90% and the above is the 10% I need for this to be functional for me.

SL

 

Posted by: Peter Hibbs Jul 11 2019, 11:07 AM

Hi Shannon,

OK, I agree that Option 1 is the way to go but I have a few more questions below. Since you have made some changes to the demo like adding new controls and entering a bunch of employee records I suggest that the best way to proceed is if you can send me a copy of your version of the demo and I will add in the extra bits to do what you want (although the extra VBA code is not that complicated I can understand that it would be quite an undertaking for you to to try and modify it yourself). If it now contains any confidential data you can email it to me (zip it first) if you want or you can upload it to this site, whichever you prefer.

(1) What version of Access are you using? I am assuming it is A2007 or later.

(2) Is this facility to be part of a larger database or will it be a 'stand alone' system, i.e. just the demo database with a few extra 'bells and whistles'? It sounds like you are transferring the data from a spreadsheet into a database in order to make it easier to use and more easily expandable (a very wise choice if that is the case).

(3) Will this database be used on a single PC or will it be used in a Local Network environment where other PC users will have access to the database?

(4) You said that there are three vacation types that need to be counted but you did not say which three! Which types do you want to include in the count and is likely that you would ever change those types to include others or exclude one of those three (we can 'hard code' those three in the VBA code, not usually a good idea, or we can store them in a table which is more flexible but a lot more complicated to code).

(5) How exactly does the Region allotment system work? I can see that different regions will have different numbers for the number of vacation days allowed and that it varies a bit at different times of the year (I guess that summer and winter allocations would be different) but is this always the same for each year or can it vary from year to year? You will need to store this information in a table (which I guess you have not done yet) so that you have a record/s for each region and fields to hold the date range for each period of maximum number of vacations. I am guessing that it would be something like January 1st to March 31st would be (say) 7, April 1st to September 30th could be 10 and maybe November 1st to December 31st would be 7 (or whatever) and that this would be the same each year. Perhaps you can explain further.

(6) I agree that the method to use would be to add another row of 28 Text boxes above the green boxes that I have already done which would hold the maximum allocation for each date and then that figure would be compared to the actual number of employees on scheduled vacation to determine whether to highlight the box in red or whatever (not sure where you got the number of 52 Text box controls as 2 x 28 = 56!). If the count does go to a negative value, do you want to highlight those boxes in a different color?

(7) Do you want to remove any of the facilities that are currently included in the demo? For example, do you need the facility to show Public Holiday dates in a different color or to show the Saturday and Sunday dates in grey or any other built-in features. Since you are starting from scratch with this database you might as well remove anything that you are never going to need as it will simplify things if you should ever need to make further changes yourself at a later date.

(8) What size screen are you using (in pixels)? If you are going to use this database on more than one PC then you will need to determine the smallest size screen in use. We will need to make sure that the forms will fit on screen or maybe you will want to make the forms (the main form anyway) bigger if you are using a larger screen (I normally design any demo software to fit on the smallest screen size that is in general use).

Anyway, let me know what you want me to do and I will see what I can come up with.

Peter.

Posted by: slynch Jul 12 2019, 04:56 PM

see my answers below...The data is not confidential but just to be safe I will zip and email.


(1) What version of Access are you using? I am assuming it is A2007 or later.

[SL] Access 2010

(2) Is this facility to be part of a larger database or will it be a 'stand alone' system, i.e. just the demo database with a few extra 'bells and whistles'? It sounds like you are transferring the data from a spreadsheet into a database in order to make it easier to use and more easily expandable (a very wise choice if that is the case).

[SL] This will integrated with my main larger database. I have already tested the integration and it works like a charm. And yes this is a transfer from excel which will finally give us a full end to end system in access. this Holiday tracking is the final piece of the puzzle.

(3) Will this database be used on a single PC or will it be used in a Local Network environment where other PC users will have access to the database?

[SL] It will be over a shared Network environment. The main database is split with the back end on the network drive and local front end deployment. I did notice that the tblWeekData table was a temporary table so from a performance standpoint that is stored on the local front end.

(4) You said that there are three vacation types that need to be counted but you did not say which three! Which types do you want to include in the count and is likely that you would ever change those types to include others or exclude one of those three (we can 'hard code' those three in the VBA code, not usually a good idea, or we can store them in a table which is more flexible but a lot more complicated to code).

[SL] I actually only need two 1) "Vacation" and 2) "Birthday Floater" to be counted in against the allotment. I would like to keep this flexible but if hard coding is easier I have no problem with that as long as I can update it later if needed. I did add a checkbox in the tblHolidayType to specify what needs to be added but again if hard coding is faster im cool with that as it should not change much at all.

(5) How exactly does the Region allotment system work? I can see that different regions will have different numbers for the number of vacation days allowed and that it varies a bit at different times of the year (I guess that summer and winter allocations would be different) but is this always the same for each year or can it vary from year to year? You will need to store this information in a table (which I guess you have not done yet) so that you have a record/s for each region and fields to hold the date range for each period of maximum number of vacations. I am guessing that it would be something like January 1st to March 31st would be (say) 7, April 1st to September 30th could be 10 and maybe November 1st to December 31st would be 7 (or whatever) and that this would be the same each year. Perhaps you can explain further.

[SL] You nailed it exactly! for the most part these allotments are the same for each region every year. but Again I would like the flexibility to update it if business needs change. I have also added a mock Allotment_tbl

(6) I agree that the method to use would be to add another row of 28 Text boxes above the green boxes that I have already done which would hold the maximum allocation for each date and then that figure would be compared to the actual number of employees on scheduled vacation to determine whether to highlight the box in red or whatever (not sure where you got the number of 52 Text box controls as 2 x 28 = 56!). If the count does go to a negative value, do you want to highlight those boxes in a different color?

[SL] Sorry for the bad math I was tired lol it should be 56. If the count goes to a 0 or negative value it should highlight red to indicate full or over.

(7) Do you want to remove any of the facilities that are currently included in the demo? For example, do you need the facility to show Public Holiday dates in a different color or to show the Saturday and Sunday dates in grey or any other built-in features. Since you are starting from scratch with this database you might as well remove anything that you are never going to need as it will simplify things if you should ever need to make further changes yourself at a later date.

[SL] Public Holidays will be useful for sure everything else is fine with me. The only other piece is the frmEmployee form but again not a big deal to update.

(8) What size screen are you using (in pixels)? If you are going to use this database on more than one PC then you will need to determine the smallest size screen in use. We will need to make sure that the forms will fit on screen or maybe you will want to make the forms (the main form anyway) bigger if you are using a larger screen (I normally design any demo software to fit on the smallest screen size that is in general use).

[SL] 1920 X 1080 but the Demo size works just fine as well.

Tks

SL

Posted by: Mickjav Aug 25 2019, 04:30 PM

QUOTE
(4) You said that there are three vacation types that need to be counted but you did not say which three! Which types do you want to include in the count and is likely that you would ever change those types to include others or exclude one of those three (we can 'hard code' those three in the VBA code, not usually a good idea, or we can store them in a table which is more flexible but a lot more complicated to code).


I have been working with this

In the UK The following applies

Maternity Leave: This is payed at 90% of gross wages I think but not 100% this is a sliding scale
Sick Pay: Is Payed at a rate but not for the first 5 days of sickness I think It my be limited to 26 weeks but not 100%
Compassionate Leave: their is no regulation to say a company should pay it but a company would normally pay said inless it's being abused
Annual Leave: This uses the employees holiday allotment
I have added a type
Converted To Holiday: I added this as my manager changes sick days to Holiday days but wanted a way to remember how many days have been converted

I am working on this view image below but it wont be include as a fully working feature Until version 5

P.S. I think their is also a male Maternity leave will have to look into what it's about more time in my future on .gov lol


Posted by: Mickjav Sep 7 2019, 04:33 AM

Hope You don't mind peter but was going though my project management system updating the holiday planner to current version when I noticed the holiday report was showing all entries for all years as my project management system goes back a lot of years it was a lot.

so I made the following change to the query: qryHolidayListReport

I added a new field with the expression: HYear: Year([StartDate]) you can call it what you like

And A Criteria: Year([Forms]![frmHolidays]![txtDate])

For those new to access what this does is limits the output of the report to the current year being viewed on the holiday planner, I didn't thing it needed a date range popup.

P.S. I used the start date only as I was only interested in holidays starting in a given year.

hope you find this of use mick

Posted by: Peter Hibbs Sep 7 2019, 09:45 AM

Hi Mick,

OK, good idea - I hadn't thought much about the report side of the design.

Peter.

Posted by: mbsqw1d Oct 23 2019, 11:50 AM

Hi Peter

What an incredible bit of work this is!

I've done my best to read through the thread and see whether this has already been covered but couldnt see that it had. Basically, I wondered if it would be very simple to enable a filter for the department?:



Thank you so much

Martin

Posted by: Peter Hibbs Oct 23 2019, 02:02 PM

Hi Martin,

welcome2UA.gif and thanks for your kind comments.

It is certainly possible but it depends on how you want the chart to look when you select a specific department. For example, if you selected, say, the Accounts department, would you still want to see ALL the departments and their employees but only the Account department employees holidays - or - would you ONLY want to see the employees for the Accounts department along with their holidays?

It looks like you have downloaded Version 1 of the demo, is there a reason for that and if not, then I would recommend you use Version 2 for any further databases.

Let me know what you want I will have a look at doing the modifications for you (they might be a bit complicated so it will save you some time if I do it).

Peter Hibbs.

Posted by: mbsqw1d Oct 23 2019, 04:18 PM

Hi Peter - thank you for the kind welcome, my registration is long overdue!

Yes I expect it isnt too straightforward the more i look... I would prefer for the list to only show employees who belong to the selected dept showing. And then the popup which opens when clicking on the date column would give a count of leave for that team rather than all departments together. My other way round tgis would be to simply setup individual leave diaries for each team... not sure that would be incredibly efficient... but perhaps that could have some other benfits such as if more than one user is attempting to book leave at the same time etc.

I was looking at another setup of yours, the Outlook Calendar copycat design, and i think a member on here had it redesigned so he could select 'Chambers' (teams) from a drop down?

I have actually been working on a vers 3a actually, sorry i just pinched a screen shot of the v1 to point reference.

I'd also be wanting to include some form of validation with regards to leave entitlement. I'd add this into the employee table and look at having a query sum a running total, once reached, a msg should show and not allow anymore leave to be booked for that individual.

You are so incredibly kind to offer your services like that! I am reading through every part of your design in order to learn the best I can however I do expect these changes will be beyond me at this point 😔


Posted by: Peter Hibbs Oct 25 2019, 08:41 AM

Hi Martin,

OK, try this one. I have added a Combo box to the main form where you can select a department and just show the employess in that department. This also filters on some of the other facilities, for example - if you print the holidays it will only print the selected department and the same for the Find Holiday option, only the employees for the selected department are displayed in the list (although you can override that by clearing the Filter field at the top of the form).

I have added a new field to the Employees table which sets the maximum number of holidays they can have for the calendar year (if you use a different period like the financial year or whatever, then you would need to change the code a bit). On the pop-up Employee Record form the Total Days Taken field turns red if the number exceeds the maximum level for that employee (you will need to fill in that field for your employees first, of course). This applies to all types of holiday (or leave) so you would need to change that if it only applies to Annual Leave or whatever.

This is only a quick demonstration of what you could do with the form, there are probably all sorts of changes you will need to make. I guess, for example, that you could display a pop-up warning message if the user adds a holiday period to the chart by dragging the mouse over the dates or maybe you could open a pop-up form showing the leave status for employees in a selected department when you click on that Department column on the left, and so on.

Just one reminder though, if you are using the form in a multi-user system where the database is split into front-end and back-end files, the table tbxWeekdata must be in the front-end file for the form to work properly if two users are using it at the same time.

Anyway, let me know how you get on and good luck with the project.

Peter.

 Holiday_Planner_V8.zip ( 267.22K ): 59
 

Posted by: mbsqw1d Oct 30 2019, 02:25 PM

Thank you so much Peter, you are incredibly kind. I'll make a start at looking through your latest work very soon and yes I'll certainly let you know the outcome. Thanks again. Martin.