ACC: Sample Function to Create a Running Sum on a Form

ID: Q121509


The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97


SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article shows you how to create a sample user-defined function to compute a running sum on a form.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.


MORE INFORMATION

One way to compute a running sum on a form is to use the DSum() function. For information about how to use the DSum() function to compute a running sum on a form, please see the following article in the Microsoft Knowledge Base:

Q103183 ACC: How to Create a Running Sum on a Form
Using the method demonstrated in this article has the following advantages over using the DSum() function:

  • The method demonstrated in this article does not require a sequential ID field. A required sequential ID field limits you to computing a running sum using a single ordering of your records.


  • Using the method demonstrated in this article is significantly faster than using the DSum() function to compute a running sum.


How to Create and Use the RunSum() Function

  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0). Create a new module, and type the following line in the Declarations section if it is not already there:
    Option Explicit


  2. Enter the following code in the module.

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.
    
            Function RunSum (F As Form, KeyName As String, KeyValue, _
            FieldToSum As String)
          '***********************************************************
          ' FUNCTION: RunSum()
          ' PURPOSE:  Compute a running sum on a form.
          ' PARAMETERS:
          '    F        - The form containing the previous value to
          '               retrieve.
          '    KeyName  - The name of the form's unique key field.
          '    KeyValue - The current record's key value.
          '    FieldToSum - The name of the field in the previous
          '                 record containing the value to retrieve.
          ' RETURNS:  A running sum of the field FieldToSum.
          ' EXAMPLE:  =RunSum(Form,"ID",[ID],"Amount")
          '***********************************************************
             Dim RS As Recordset
             Dim Result
    
             On Error GoTo Err_RunSum
    
             ' Get the form Recordset.
             Set RS = F.RecordsetClone
    
             ' Find the current record.
             Select Case RS.Fields(KeyName).Type
                ' Find using numeric data type key value?
                Case DB_INTEGER, DB_LONG, DB_CURRENCY, _
                   DB_SINGLE, DB_DOUBLE, DB_BYTE
                   RS.FindFirst "[" & KeyName & "] = " & KeyValue
                ' Find using date data type key value?
                Case DB_DATE
                   RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
                ' Find using text data type key value?
                Case DB_TEXT
                   RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
                Case Else
                   MsgBox "ERROR: Invalid key field data type!"
                   GoTo Bye_RunSum
             End Select
    
             ' Compute the running sum.
             Do Until RS.BOF
                Result = Result + RS(FieldToSum)
    
                ' Move to the previous record.
                RS.MovePrevious
             Loop
    
          Bye_RunSum:
             RunSum = Result
             Exit Function
    
          Err_RunSum:
             Resume Bye_RunSum
    
          End Function 


  3. Create the following new query based on the Orders table and the Order Subtotals query. Save the query as qryOrders:
    
          Query: qryOrders
          ------------------------------------------------------
          Type: select query
          Join: Orders.[OrderID] <-> [Order Subtotals].[OrderID]
    
          NOTE: In version 2.0, there is a space in Order ID.
    
          Field: OrderID
             Table: Orders
             Sort: Ascending
          Field: Subtotal
             Table: Order Subtotals 


  4. Use the Tabular Form Wizard to create a new form based on the qryOrders query. Include both fields on the form.


  5. Add a text box with the following properties to the new form:
    Name: RunningSum
    ControlSource: =RunSum([Form],"OrderID",[OrderID],"Subtotal")
    Format: Currency
    NOTE: In version 2.0, there is a space in the Order ID field.


  6. Save the form and then view it in Form view.


  7. Select different records using the record selector. Note that the Runningsum field shows an accumulated total based on the Subtotal field.


Additional query words: total

Keywords : kbusage FmsHowto
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: October 12, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.