Time Recording Running Sum error

[Triacona]

Dear All,

Thanks for a great forum with great people!

I have a problem...

I have a form Employees
form Employees has a subform DatesSubform

I have several fields on the subform DatesSubformDetails area.

EmployeeID
Date
TimeInMorning
TimeOutLunch
TimeInLunch
TimeOutEvening
txtTimeDoneMorning
txtTimeDoneEvening
txtTotalTimeDone
txtTimeRequired
txtFlexi


I then have a field on the DatesSubform form footer.
RollingTime

The RollingTime is meant to show the total of txtFlexi.

I have the following module code from a module called :modRunSum

[blue]Option Compare Database[/blue]

[blue]Option Explicit[/blue]
[blue]Public Function [/blue]frmRunSum(frm As Form, pkName As String, sumName As String)
   Dim rst As DAO.Recordset, fld As DAO.Field, subTotal
 
   Set rst = frm.RecordsetClone
   Set fld = rst(sumName)
   
   'Set starting point.
   rst.FindFirst "[" & pkName & "] = " & frm(pkName)
 
[green]   'Running Sum (subTotal) for each record group occurs here.
   'After the starting point is set, we sum backwards to record 1.[/green]
[blue]   If Not [/blue]rst.BOF [blue]Then[/blue]
     [blue] Do Until[/blue] rst.BOF
         subTotal = subTotal + Nz(fld, 0)
         rst.MovePrevious
[blue]      Loop
   Else[/blue]
      subTotal = 0
[blue]   End If[/blue]
 
   frmRunSum = subTotal
   
   Set fld = Nothing
   Set rst = Nothing
 
[blue]End Function[/blue]

And then in the subform I have the code:

[blue]Private Function [/blue]SubSum()
[green]'*************************************************************
'* pkName   - Existing unique fieldname (usually primarykey) *
'* sumName  - Name of the field to runsum                    *
'*************************************************************[/green]
   
[blue]   If [/blue]Trim(Me!EmployeeID & "") <> "" Then [green]'Skip New Record![/green]
      SubSum = frmRunSum(Me, "EmployeeID", "RollingTime")
[blue]   End If[/blue]

[blue]End Function[/blue]

I keep getting a total of 0.00...where it should be 2:00:00 or 2.

Please help it is a hum dinger.

Thank you!

Thank you,

Kind regards

Triacona

 

[Hap007]

Hi,

I would think that if you are going to move from the last record to the first record in your routine: frmRunSum, that you should begin by move to the last record in the recordset at the beginning

Seems very vague why you are summing backwards.

I would have created my own SQL cury and used the resulting returned recordsset for the sum, not the form recordset.

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[Triacona]

Dear Hap007,

Thank you very much for your reply...

I have used a query...but want to really do it in a form.

The code above was one of the FAQ's provided by Tek-Tips, so I'm trying to use this to work in my Access form.

Thanks again for your help...I will use the queries as a backup...there is one problem though...negative time...



Thank you,

Kind regards

Triacona

 

[Hap007]

Hi Triacona,

I suppose it is nice to work with the form data, but you never know what temporary filters or sort sequences the user may select while running/viewing the data.

From what you describe, it seems the user may change the current record and without seeing what you are doing, I can imagine that might also cause summing problems.

I always use a query to retrieve data for a form's recordset, and use a subquery for any sub forms. FYI, I Never trust access to link subforms to the main form. This just seems to be the most secure method.

I am not suggesting you use this methods, but I have found it very reliable for retrieving data and totals.

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121