Code falling pver first week of every month... (1 Viewer)

Fozi

Registered User.
Local time
Today, 05:28
Joined
Feb 28, 2006
Messages
137
Folks

In my system users enter their timesheet weekly. It seems that on the first week of every month the following code falls over. Returning a Runtime Error 3021 'No Current Record'

Code:
Option Compare Database

Function GetFlexiMax(FDate As Date, FId As IdleEnum, Username As String, 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.Username)='" & Username & "'));"

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
On the subform itself the following code exists within OnCurrent Property
Code:
Private Sub Form_Current()
If NewRecord Then
FlexiMax = 0
Else
FlexiMax = GetFlexiMax([xDate], [IDRN], [Username], 864)
End If
End Sub
Other than that there's also a instruction within the OnOpen property
Code:
Private Sub Form_Open(Cancel As Integer)
DoCmd.GoToRecord , , acLast
End Sub
All/any help greatly appreciated.

Thanks
Frank
 

DCrake

Remembered
Local time
Today, 05:28
Joined
Jun 8, 2005
Messages
8,634
After you have set Rs1 you may want to check for .EOF before issuing the Rs1.MoveFirst command. Such as.

Code:
If Not Rs1.EOF Or Not Rs1.BOF Then
    Rs1.MoveFirst
    .... Your code
    Rs1.Close
Else

    ..... Other code

End If

Set Rs1 = Nothing
Remember, trying to close a recordset that is both EOF and BOF will raise an error, this is why it is prudent to issue the Rs1.Close within the corrsponding code.

David
 

Fozi

Registered User.
Local time
Today, 05:28
Joined
Feb 28, 2006
Messages
137
Thanks DCrake

Tries what you suggested but so limited is my VBA knowledge that I must have broke the code. I ended up in an eternal loop and had to reboot the application.

This is what I tried

Code:
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)


Do While Not Rs1.EOF or Rs1.BOF
Rs1.MoveFirst
    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, 05:28
Joined
Feb 28, 2006
Messages
137
Hi

Would someone mind having a look at my application to see if you can see my error?

I've tried in vain for a couple of weeks now but to no avail. Long and the short of it is I have a routine which is calculating my total Flexitime per user per week. However on the first week of any month the code falls over failing to return a record.

Let me know if you've got time and I'll post.

Thanks
Frank
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 05:28
Joined
Sep 12, 2006
Messages
13,892
this sort of thing

Code:
While Not Rs1.EOF 
    GetFlexiMax = IIf(Rs1!SuplusTime + GetFlexiMax > Maxlimit, Maxlimit, Rs1!SuplusTime + GetFlexiMax)
    Rs1.MoveNext
wend

Rs1.Close
you dont need to check for .bof (beginning of file) - just end of file

you can surround while/wend with do/loop but you dont have to

what are you doing with getfleximax inside the loop? all this is doing is resetting for each iteration
 

Fozi

Registered User.
Local time
Today, 05:28
Joined
Feb 28, 2006
Messages
137
Dave

Thanks for the reply. I got the code of Kharwar who was a superb help. The routine calculates Fleximax on a week by week basis and if it exceeds a certain amount it caps at 14.24 for that users next entry.

Whether this is significant I honestly dont know! I'll try your suggestion and see how it goes.

If you're up for a quick piece of freelance work let me know. I need this solved ASAP.

Cheers
Frank
 

Fozi

Registered User.
Local time
Today, 05:28
Joined
Feb 28, 2006
Messages
137
hey all

Database attached if anyone would like some paid work please let me know.

Thanks
Frank
 

Attachments

namliam

The Mailman - AWF VIP
Local time
Today, 06:28
Joined
Aug 11, 2003
Messages
11,552
Code:
Function GetFlexiMax(FDate As Date, FId As IdleEnum, Username As String, 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.Username)='" & Username & "'));"

GetFlexiMax = 0

Set Rs1 = CurrentDb.OpenRecordset(StrSql)
if rs1.recordcount = 0 then ExitSub
Rs1.MoveFirst
Do While Not Rs1.EOF
    GetFlexiMax = IIf(Rs1!SuplusTime + GetFlexiMax > Maxlimit, Maxlimit, Rs1!SuplusTime + GetFlexiMax)
    Rs1.MoveNext
Loop
ExitSub:
Rs1.Close

Set Rs1 = Nothing

End Function
Or try something like:
Code:
Function GetFlexiMax(FDate As Date, FId As IdleEnum, Username As String, Maxlimit As Long)

Dim Rs1 As Recordset
Dim StrSql As String
StrSql = ""
StrSql = StrSql & " SELECT nz(sum(SuplusTime),0) as SumSurplus "
StrSql = StrSql & " FROM   QRY_FlexiByWeekwithRN "
StrSql = StrSql & " WHERE  xDate>=#" & FDate & "# "
StrSql = StrSql & "   AND  IDRN<=" & FId
StrSql = StrSql & "   AND  Username='" & Username & "';"

Set Rs1 = CurrentDb.OpenRecordset(StrSql)

GetFlexiMax = Rs1!SumSurplus

If GetFlexiMax > Maxlimit Then GetFlexiMax = Maxlimit

Rs1.Close

Set Rs1 = Nothing

End Function
Some tips for sanity's sake...
Disambiguate..
Always have DAO.Database and DAO.Recordset in order to prevent mishaps/unclarity

Naming
Inside functions dont use names identical to the names in the forms/tables. Use something like txtUsername instead of username... Again goes to consistancy.

Formatting
Compare your SQL to mine, Splitting the SQL over multiple lines like this will keep your code more readable/maintainable. For your self but also other people.

Macro's
Brrrr. I hate macro's anything that can be done in a Macro can better be done in code... I appriciate your not good at it, but try to use minimum macro's i.e. Mac_CloseMain which just closes the main form...
Also opening a form is quite easy (docmd.openform) with the same parameters. Except you can do much more in code if you need to... Macro's brrrrr.

Good luck on your project
 

Fozi

Registered User.
Local time
Today, 05:28
Joined
Feb 28, 2006
Messages
137
Thanks namliam

Thanks also for your tips, most appreicated.

Your first code didn't like the ExitSub command. Tried the second one as is. This got round the problem with the Runtime Error but for each record the flexi returned was simply their total for that week and not the accumulated total.

Playing about with the < > symbols, I found that when both are set to <, it was fine for entries one and two (27/4 and 4/5 respectively but on entry three 11/5 the total returned was zero.

Any clues as to where it's going wrong?
 

namliam

The Mailman - AWF VIP
Local time
Today, 06:28
Joined
Aug 11, 2003
Messages
11,552
The first should be:
if rs1.recordcount = 0 then Goto ExitSub
Sorry for the oversight...

I dont know how to fix your logic though? As it is your logic... You are feeding the "Week commencing" into your function, so it will search anything >= your current week... Could that have something to do with your problem?
 

DCrake

Remembered
Local time
Today, 05:28
Joined
Jun 8, 2005
Messages
8,634
Please see revised mdb

Changes made:

How employee is selected in main form
GetflexiMax function revised
Duplicate GetFlexiMax Function removed from Report
Summary of total hours in sub form revised.

Also see PM

David
 

Attachments

Fozi

Registered User.
Local time
Today, 05:28
Joined
Feb 28, 2006
Messages
137
First of all thanks to everyone for taking the time.

David, I downloaded your amendments however although the error no longer appears, what I need is the Available Flexi to accummulate according to previous entries. So for example if the user has a surplus of 13 hours in week one, and a deficit of 10 hours in week two the Available flexi should show 3 hours. Currently it meerely reflects the overtime or deficit for that week.

I'm truly grateful and hope I dont sound disingenious.

Thanks
Frank
 

DCrake

Remembered
Local time
Today, 05:28
Joined
Jun 8, 2005
Messages
8,634
In that case if you have a query that is grouped by the week number of the date and then sum the total mins for that week using an iff condition it should give you the end result. Will give it a test and get back to you

David

Copy this sql into a new query and test if this is what you mean

Code:
SELECT Format([Week],"w") AS WkNo, Table1.Name, Sum(Table1.FlexiMins) AS SumOfFlexiMins, Sum(Table1.FlexiMax) AS SumOfFlexiMax, IIf(Sum([FlexiMins])>Sum([FlexiMax]),Sum([flexiMax]),Sum([FlexiMins])) AS CarryThroughMins, MinsToTime([CarryThroughMins]) AS CarryThroughHours, IIf([CarryThroughMins]>854,854,[CarryThroughMins]) AS CappedMins, MinsToTime([CappedMins]) AS CappedHours
FROM Table1
GROUP BY Format([Week],"w"), Table1.Name;
I added a couple of records to the Table1 table to create data for a different week no.
 
Last edited:

Fozi

Registered User.
Local time
Today, 05:28
Joined
Feb 28, 2006
Messages
137
In that case if you have a query that is grouped by the week number of the date and then sum the total mins for that week using an iff condition it should give you the end result. Will give it a test and get back to you

David

Copy this sql into a new query and test if this is what you mean

Code:
SELECT Format([Week],"w") AS WkNo, Table1.Name, Sum(Table1.FlexiMins) AS SumOfFlexiMins, Sum(Table1.FlexiMax) AS SumOfFlexiMax, IIf(Sum([FlexiMins])>Sum([FlexiMax]),Sum([flexiMax]),Sum([FlexiMins])) AS CarryThroughMins, MinsToTime([CarryThroughMins]) AS CarryThroughHours, IIf([CarryThroughMins]>854,854,[CarryThroughMins]) AS CappedMins, MinsToTime([CappedMins]) AS CappedHours
FROM Table1
GROUP BY Format([Week],"w"), Table1.Name;
I added a couple of records to the Table1 table to create data for a different week no.
David

That look great! I'm sending you a PM with some further comments. Please check

Thanks Frank
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom