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

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

While reports have a RunningSum property, forms do not. This article describes a method to create 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 versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

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:

   ARTICLE-ID: Q150895
   TITLE     : ACC95: Microsoft Access Sample Forms Available on MSL

   ARTICLE-ID: Q175066
   TITLE     : ACC97: Microsoft Access 97 Sample Forms Available on MSL

MORE INFORMATION

To create a running sum on a form, the underlying table or query must have a unique, sequential ID field, such as 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 shows how to generate a running sum on a form

using the DSUM function.   For more information on how to generate a

   ARTICLE-ID: Q121509
   TITLE     : ACC: Sample Function to Create a Running Sum on a Form

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 Order ID.
   
      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.