Microsoft KB Archive/210495

= ACC2000: How to Use DSum to Create a Running Sum on a Form =

Article ID: 210495

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210495



Advanced: Requires expert coding, interoperability, and multiuser skills.



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 file, FrmSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

233324 Microsoft Access 2000 Sample Forms Database 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 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 additional information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

210338 ACC2000: Sample Function to Create a Running Sum on a Form

The following example uses the sample database Northwind.mdb 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:  Start Microsoft Access and open the sample database Northwind.mdb.  Create the following new form based on the Orders table.

NOTE: To improve readability, the underscore (_) is used in the following example 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]")

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.  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: inf subtotal query

Keywords: kbhowto KB210495

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.