UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
6 Pages V < 1 2 3 4 5 > »   (Go to first unread post)
   Reply to this topicStart new topic
> Holiday Planner Demo, Access 2003    
 
   
payfast8898
post Dec 23 2018, 09:26 PM
Post#41



Posts: 303
Joined: 23-April 15
From: NY


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

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
Peter Hibbs
post Dec 24 2018, 11:25 AM
Post#42


UtterAccess VIP
Posts: 1,741
Joined: 17-June 10
From: Dorset. UK.


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 HERE. 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 HERE.

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.
Go to the top of the page
 
payfast8898
post Dec 24 2018, 01:45 PM
Post#43



Posts: 303
Joined: 23-April 15
From: NY


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.

--------------------
"When a collection of Brilliant minds, hearts and talents come together...
Expect a masterpiece"
Go to the top of the page
 
Peter Hibbs
post Dec 27 2018, 06:56 AM
Post#44


UtterAccess VIP
Posts: 1,741
Joined: 17-June 10
From: Dorset. UK.


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.
Go to the top of the page
 
masa1
post Dec 28 2018, 05:13 AM
Post#45



Posts: 33
Joined: 24-March 18



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.
This post has been edited by masa1: Dec 28 2018, 05:16 AM
Attached File(s)
Attached File  Pic01.PNG ( 67.68K )Number of downloads: 39
Attached File  Pic02.PNG ( 43.54K )Number of downloads: 30
 
Go to the top of the page
 
Peter Hibbs
post Dec 28 2018, 11:37 AM
Post#46


UtterAccess VIP
Posts: 1,741
Joined: 17-June 10
From: Dorset. UK.


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.
Go to the top of the page
 
masa1
post Dec 28 2018, 12:40 PM
Post#47



Posts: 33
Joined: 24-March 18



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?
Go to the top of the page
 
masa1
post Dec 28 2018, 01:00 PM
Post#48



Posts: 33
Joined: 24-March 18



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.
Go to the top of the page
 
Peter Hibbs
post Dec 29 2018, 05:57 AM
Post#49


UtterAccess VIP
Posts: 1,741
Joined: 17-June 10
From: Dorset. UK.


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.
Go to the top of the page
 
mrcunknown
post Jan 8 2019, 03:08 PM
Post#50



Posts: 32
Joined: 22-September 18



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?
Go to the top of the page
 
Peter Hibbs
post Jan 8 2019, 06:07 PM
Post#51


UtterAccess VIP
Posts: 1,741
Joined: 17-June 10
From: Dorset. UK.


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.
Go to the top of the page
 
masa1
post Jan 10 2019, 05:30 AM
Post#52



Posts: 33
Joined: 24-March 18



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.
Go to the top of the page
 
Peter Hibbs
post Jan 11 2019, 06:15 AM
Post#53


UtterAccess VIP
Posts: 1,741
Joined: 17-June 10
From: Dorset. UK.


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?
Go to the top of the page
 
masa1
post Jan 11 2019, 07:44 AM
Post#54



Posts: 33
Joined: 24-March 18



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.
Attached File(s)
Attached File  frmEmployee.PNG ( 60.95K )Number of downloads: 31
 
Go to the top of the page
 
Peter Hibbs
post Jan 11 2019, 02:30 PM
Post#55


UtterAccess VIP
Posts: 1,741
Joined: 17-June 10
From: Dorset. UK.


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.
Go to the top of the page
 
masa1
post Jan 12 2019, 06:17 AM
Post#56



Posts: 33
Joined: 24-March 18



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
Go to the top of the page
 
Peter Hibbs
post Jan 13 2019, 11:33 AM
Post#57


UtterAccess VIP
Posts: 1,741
Joined: 17-June 10
From: Dorset. UK.


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.

Attached File(s)
Attached File  Holiday_Planner_V3a.zip ( 238.1K )Number of downloads: 125
 
Go to the top of the page
 
masa1
post Jan 14 2019, 07:25 AM
Post#58



Posts: 33
Joined: 24-March 18



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.
Go to the top of the page
 
masa1
post Jan 14 2019, 12:18 PM
Post#59



Posts: 33
Joined: 24-March 18



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.
This post has been edited by masa1: Jan 14 2019, 12:20 PM
Attached File(s)
Attached File  Percentage.PNG ( 35.42K )Number of downloads: 7
Attached File  frmEmployee.PNG ( 20.67K )Number of downloads: 6
Attached File  References.PNG ( 16.52K )Number of downloads: 4
 
Go to the top of the page
 
Peter Hibbs
post Jan 14 2019, 01:46 PM
Post#60


UtterAccess VIP
Posts: 1,741
Joined: 17-June 10
From: Dorset. UK.


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.
Go to the top of the page
 
6 Pages V < 1 2 3 4 5 > » 


Custom Search


RSSSearch   Top   Lo-Fi    12th November 2019 - 09:46 PM