Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Microsoft: Access Forms FAQ

Running Sum In Forms

How to get a Running Sum In Forms
Posted: 28 Jun 04 (Edited 28 Mar 11)

How's Everyone . . . . .

See FAQ701-5268: How to get a Running Sum in Queries for Running Sum in Queries.

I've been asked and researched a great many requests for a running sum in a form. Research has come up with a DSum method (which is slow) and other methods which are dependant on a key field, specifically sorted in ascending order (this is a severe limitation). Finally ran into a routine that is faster than DSum and independant of sorting. I don't know who to give the credit too for the foundation of the base routine, but you can find it in Microsofts Knowledge Base http://support.microsoft.com/default.aspx?scid=kb;en-us;210338. What I present here is a modification, plus additions to the routine, to make it global to any form/subform on which you wish to perform a running sum.

Advantages of the routine:
1) Faster than DSum.
2) Independent of Sorting.
3) Performs running sum for any form/subform.

The routine requires Microsoft DAO 3.6 Object Library. To check/install the library, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

Now . . . . create a new module in the module window. Name the module modRunSum. Add the following to the declarations section if it doesn't already exist:

CODE

Option Explicit
Next . . . . copy/paste the following function to the same module, (this is the global running sum routine):

CODE

Public Function 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)
 
   'Running Sum (subTotal) for each record group occurs here.
   'After the starting point is set, we sum backwards to record 1.

   If Not rst.BOF Then
      Do Until rst.BOF
         subTotal = subTotal + Nz(fld, 0)
         rst.MovePrevious
      Loop
   Else
      subTotal = 0
   End If
 
   frmRunSum = subTotal
   
   Set fld = Nothing
   Set rst = Nothing
 
End Function
Next . . . . for each form where you require a runnung sum, add the following code to the corresponding forms code module. This code must be in form module as were passing 'Me' as the current form object. The programmer must supply all items in purple.

CODE

Private Function SubSum()
'*************************************************************
'* pkName   - Existing unique fieldname (usually primarykey) *
'* sumName  - Name of the field to runsum                    *
'*************************************************************

   
   If Trim(Me!pkName & "") <> "" Then 'Skip New Record!
      SubSum = frmRunSum(Me, "pkName", "sumName")
   End If

End Function
Almost there . . . . .

In the details section of the form, add an unbound textbox. Set the Control Source to:

CODE

=SubSum()
Finally, to see updates on the fly, in the AfterUpdate event of the field to runsum copy/paste the following:

CODE

   DoEvents
   Me.Recalc
Thats it! . . . . give it a whirl and let me know if anyone has any problems.

Any input on this schema, good or bad, is most certainly welcome.

Cheers All! . . . thumbsup2

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close