Time Management System issue (1 Viewer)

Fozi

Registered User.
Local time
Today, 18:50
Joined
Feb 28, 2006
Messages
137
Hi all

Those close to this board will know I've been working on a Timesheet system recently. I'm self taught in access and know very little VBA so when it gets to doing clever stuff I rely heavy on the support from the good people her - so thanks to all who have helped thus far.

Such is my limited knowledge that on occasions before I've posted for asssistance thinking I knew what was wrong but inadvertantly led some down the garden path. So apologies for that also.

Here's the thing....

Users enter their timesheets weekly. These get stored in a table showing their day to day entries per week. I have a query which totals that table to show their weekly times and then calculated their surplus/deficit worked per week - called [Fleximins]. However is Fleximins gets higher that 14 hours and 24 minutes (864 minutes) the total must be capped and all further times capped at that limit. So if in the next week they work a surplus it stays at 14.24, if they work less it deducts from 14.24. On this issue I've had tremendous support from DCrake and Kharwar to name but a few.

I have a form called FRM_Activity and Flexi Entries By Week, which contains three subforms - FRM_Flexi Log, FRM_Activity Log and FRM_Flexi. Users enter their times through FRM_Flexi Log. FRM_Flexi is then meant to show their current Flexi balance from previous entries and then recalcute following the entry of the most recent submission.

I having tremendous difficulty getting this to work. Simply becasue I'm pushing the limits of my knowledge.

I've attached the system in question if anyone has then time to look. I have worked on this for over 10 days but got nowhere myself and unfortunately need to get it back to the client very soon. Thanks to all.

Frank
 

Attachments

  • ActivityLogger.zip
    238.2 KB · Views: 169

khawar

AWF VIP
Local time
Today, 22:50
Joined
Oct 28, 2006
Messages
870
Is this limit for one week only ?
In the next week will the running sum be started from 0 or it will be continued from running sum at the end of last week

if it is continued when it will have to be zero

one more thing how do you calculate the surplus and from which table it comes from
 
Last edited:

Fozi

Registered User.
Local time
Today, 18:50
Joined
Feb 28, 2006
Messages
137
Is this limit for one week only ?
In the next week will the running sum be started from 0 or it will be continued from running sum at the end of last week

if it is continued when it will have to be zero

one more thing how do you calculate the surplus and from which table it comes from

Hi Kharwar

No. There are no weekly limits on time unless the user exceeds the 14hr 24 mins max. All subsequent balances continue from the previous entries.
On the subform FRM_Flexi Log, you'll see a calculated control in the footer calculating each weeks surplus deficit. I then have a control within the main form [Text103] which reference this. At present I'm attempting to drop the surplus per week into the table TBL_Staff Entries by Week by way of a SetValue macro. I know this breaks normalisation rules but don;t know how else to go about achieving what i need.

Hopefully this will all make sense when you chek the attachment.

Thanks
Frank
 

Fozi

Registered User.
Local time
Today, 18:50
Joined
Feb 28, 2006
Messages
137
Bump

Anyone? Sorry folks but I'm struggling here, as my previous post will show I've been on this for some time.

All help greatly appreciated.

Thanks
Frank
 

khawar

AWF VIP
Local time
Today, 22:50
Joined
Oct 28, 2006
Messages
870
You can creat a custom function get value at the time of entry on form

Value can be calculated by looping through the recordset instead of saving data
 

khawar

AWF VIP
Local time
Today, 22:50
Joined
Oct 28, 2006
Messages
870
I have created a sample with a custom function which calculates value with limit on form after each entry without saving any value in table

Download the attached sample
 

Attachments

  • Sample Running total with a max limit on form.zip
    25.5 KB · Views: 209

jardiamj

Registered User.
Local time
Today, 11:50
Joined
Apr 15, 2009
Messages
59
Hello Fozi!
If you can't achieve this before I will take a look at the db you uploaded tomorrow. I have to go to sleep cause it's getting late.
Cheers!
 

jardiamj

Registered User.
Local time
Today, 11:50
Joined
Apr 15, 2009
Messages
59
I didn't see that khawar just posted before me!... I guess I won't need to see it.
Cheers!
 

Fozi

Registered User.
Local time
Today, 18:50
Joined
Feb 28, 2006
Messages
137
I have created a sample with a custom function which calculates value with limit on form after each entry without saving any value in table

Download the attached sample

Kharwar

Nearly there. So many thanks for all your support. I've adapted the form to pull from my query containing the relevant data and amended the module code and form code accordingly. When I open up the form it shows the data but at the first record I get an "Runtime Error 3021 - No Current Record".

PHP:
Function GetFlexiMax(FDate As Date, FId As IdleEnum, EmpId As Long, Maxlimit As Long)

Dim Rs1 As Recordset
Dim StrSql As String
StrSql = "SELECT QRY_FlexibyWeekwithRN.* FROM QRY_FlexibyWeekwithRN WHERE (((QRY_FlexibyWeekwithRN.xDate)<=#" & FDate & "#) AND ((QRY_FlexibyWeekwithRN.IDRN)<=" & FId & ") AND ((QRY_FlexibyWeekwithRN.EmployeeId)=" & EmpId & "));"

GetFlexiMax = 0

Set Rs1 = CurrentDb.OpenRecordset(StrSql)

Rs1.MoveFirst
Do While Not Rs1.EOF
    GetFlexiMax = IIf(Rs1!SuplusTime + GetFlexiMax > Maxlimit, Maxlimit, Rs1!SuplusTime + GetFlexiMax)
    Rs1.MoveNext
    Loop
Rs1.Close

Set Rs1 = Nothing

End Function

Thanks
Frank
 

Fozi

Registered User.
Local time
Today, 18:50
Joined
Feb 28, 2006
Messages
137
I didn't see that khawar just posted before me!... I guess I won't need to see it.
Cheers!

Thanks for the offer Jariamj. I'll post back if needed.

Cheers
Frank
 

Fozi

Registered User.
Local time
Today, 18:50
Joined
Feb 28, 2006
Messages
137
Kharwar

By way of an update, I managed to integrate the function you had composed within my application. It now works extremely well. As regards my earlier message about Runtime Error. In testing it appears that the xDate field and/or certain validation, does not like the date 02-Mar-09. As long as this was changed or removed the form worked perfectly! Very Strange!

Is it possible to integrate the GetFlexiMax function into a report also. Essentially upon entering a weeks times, the users needs to print a report containing the exact same details as the data entry screen for approval.

I cant easily link the report to the query containing the rest of the data but don't know how to bring through the FlexiMax for each entry.

Thanks for all your help.

Frank
 

Users who are viewing this thread

Top Bottom