Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/103183

From BetaArchive Wiki
Knowledge Base


ACC: How to Use DSum to Create a Running Sum on a Form

Article ID: 103183

Article Last Modified on 1/18/2007



APPLIES TO

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q103183

SUMMARY

Reports have a RunningSum property; forms do not. This article shows you how to create a running sum on a form.

NOTE: This article explains a technique demonstrated in the sample files, FrmSampl.exe (for Microsoft Access for Windows 95 version 7.0) and FrmSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

150895 ACC95: Microsoft Access Sample Forms Available in Download Center


175066 ACC97: Microsoft Access 97 Sample Forms Available in Download Center


MORE INFORMATION

To create a running sum on a form, the underlying table or query must have a unique, sequential, ID field, such as an AutoNumber (or Counter in versions 1.x and 2.0) or a Date/Time timestamp field. If your table does not have such a field, you can open the table in Design view and add a new

field to create one.

NOTE: The following example uses the DSum() function to generate a running sum on a form. For more information on how to generate a running sum on a form by using code, please see the following article in the Microsoft Knowledge Base:

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


The following example uses the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0) to show you how to create a text box on a form that sums the freight amount for the current customer order and all preceding customer orders:

  1. Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0.)
  2. Create the following new form based on the Orders table.

    NOTE: To improve readability, the underscore (_) is used in the example below as a line-continuation character. Remove the underscore from the end of the line when you are re-creating this example.

          Form:  Form1
          --------------------
          RecordSource: Orders
    
          Text Box: Order ID
             ControlSource: OrderID
          Text Box: Total
             ControlSource: =DSum("[Freight]","Orders",_
                                    "[OrderID] <= Forms![Form1]![OrderID]")
                            

    NOTE: In versions 1.x and 2.0, there is a space in the Order ID field name.

    The DSum() function calculates the sum of all freight charges on orders with an Order number less than or equal to the currently displayed order number. For this reason, it is important to have a unique and sequential ID field.

  3. View the form in Form view. Note that the Total text box accumulates total freight charges for each order as you move from record to record.



Additional query words: subtotal query

Keywords: kbhowto kbprogramming kbusage KB103183