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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
4 Pages V « < 2 3 4  (Go to first unread post)
   Reply to this topicStart new topic
> Holiday Planner Demo, Access 2003    
 
   
masa1
post Jan 14 2019, 02:23 PM
Post#61



Posts: 30
Joined: 24-March 18



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.
This post has been edited by masa1: Jan 14 2019, 02:43 PM
Go to the top of the page
 
mrcunknown
post Jan 15 2019, 02:42 AM
Post#62



Posts: 27
Joined: 22-September 18



Thnx Peter ... i will give it a go :-)
Go to the top of the page
 
Peter Hibbs
post Jan 15 2019, 06:35 AM
Post#63


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


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.
Go to the top of the page
 
masa1
post Jan 15 2019, 02:21 PM
Post#64



Posts: 30
Joined: 24-March 18



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.
Go to the top of the page
 
masa1
post Jan 17 2019, 02:32 PM
Post#65



Posts: 30
Joined: 24-March 18



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.
This post has been edited by masa1: Jan 17 2019, 02:34 PM
Attached File(s)
Attached File  qry.PNG ( 19.67K )Number of downloads: 12
 
Go to the top of the page
 
Peter Hibbs
post Jan 18 2019, 07:06 AM
Post#66


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


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.
Go to the top of the page
 
Mickjav
post Jan 18 2019, 02:48 PM
Post#67



Posts: 12
Joined: 25-November 18



Hi Andy,

Peter has been kind enough to allow me to add his holiday to my Calendar example Here Calendar Example

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
Go to the top of the page
 
masa1
post Jan 19 2019, 06:41 AM
Post#68



Posts: 30
Joined: 24-March 18



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
Go to the top of the page
 
Mickjav
post Jan 19 2019, 08:36 AM
Post#69



Posts: 12
Joined: 25-November 18



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.
This post has been edited by Mickjav: Jan 19 2019, 08:36 AM
Go to the top of the page
 
masa1
post Jan 19 2019, 10:13 AM
Post#70



Posts: 30
Joined: 24-March 18



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
Go to the top of the page
 
Mickjav
post Jan 19 2019, 11:35 AM
Post#71



Posts: 12
Joined: 25-November 18



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
Go to the top of the page
 
masa1
post Jan 19 2019, 12:35 PM
Post#72



Posts: 30
Joined: 24-March 18



I'm sorry, my mistake!
Vacation ends 11-01-2019r
Go to the top of the page
 
Mickjav
post Jan 20 2019, 02:51 PM
Post#73



Posts: 12
Joined: 25-November 18



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
Attached File  2019_01_20__1_.png ( 86.42K )Number of downloads: 5


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
Attached File  2019_01_20__7__LI.jpg ( 799.74K )Number of downloads: 5


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
This post has been edited by Mickjav: Jan 20 2019, 03:21 PM
Attached File(s)
Attached File  2019_01_20__5_.png ( 64.51K )Number of downloads: 4
Attached File  2019_01_20__6_.png ( 75.39K )Number of downloads: 5
 
Go to the top of the page
 
masa1
post Yesterday, 02:17 AM
Post#74



Posts: 30
Joined: 24-March 18



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.
Go to the top of the page
 
Mickjav
post Yesterday, 01:16 PM
Post#75



Posts: 12
Joined: 25-November 18



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.
This post has been edited by Mickjav: Yesterday, 01:40 PM
Attached File(s)
Attached File  2019_01_21.png ( 111.62K )Number of downloads: 6
 
Go to the top of the page
 
4 Pages V « < 2 3 4


Custom Search


RSSSearch   Top   Lo-Fi    22nd January 2019 - 10:05 AM