Microsoft KB Archive/233485

= ACC2000: How to Create a Running Sum on a Data Access Page =

Article ID: 233485

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q233485



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

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
Running sums can be displayed on reports by using the RunningSum property of a text box, and on forms by using the DSum function, but neither of these methods is available on a Data Access Page (DAP). This article shows you how to display running sums on a DAP in two ways, one basing the page on a query that calculates the running sum and the other using a script to calculate the sum on the page.



MORE INFORMATION
Running sums display the cumulative total of some value as records are listed on a form or report, or, as described in this article, a DAP. For additional information about running sums in reports and forms, click the article numbers below to view the articles in the Microsoft Knowledge Base:

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

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

The methods you use for reports and forms are not available with DAPs, but you can use either of the methods described here to accomplish the same result.

Method 1: Base the DAP on a Query That Calculates a Running Sum
Although DSum cannot be used in a DAP, it can be used in a query that provides data for the page, which will allow the page to display the required information. The following example will show you how to do this using the Northwind sample database.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

To create a query that calculates a running sum, follow these steps:  Start Access and open the sample database Northwind.mdb. Create a new query in Design view, add the Orders table, and click Close. On the View menu, click Totals.  In the first column of the query grid, add the following:   Field: EmpAlias:[EmployeeID] Total: Group By                     In the second column, add the following:   Field: [Freight] Total: Sum </li>  In the third column, add the following: <pre class="fixed_text">  Field: RunSum:Format(DSum("[Freight]","Orders","[EmployeeID]<="&[EmpAlias]),"Currency") Total: Expression </li> Save the query as qry Freight RunSum. Run the query.

The RunSum field displays the cumulative sum of the Freight totals.</li></ol>

For additional information about calculating running sums in queries, click the article number below to view the article in the Microsoft Knowledge Base:

208714 ACC2000: How to Create Running Totals in a Query

This query can now be used as the basis for a DAP that will display the running totals. To do that, follow these steps:
 * 1) Under Objects, click Pages, and then click New.
 * 2) In the Choose the table ... list, select qry Freight RunSum, and then click OK to create a new page based on that query.
 * 3) Drag all the qry Freight RunSum fields from the field list onto the Header area of the page and arrange them in a horizontal line.
 * 4) Delete the Navigation section of the page and allow the DataPageSize property to be set to All so that all records will be displayed on the same page, including the running sum.
 * 5) Shorten the Header section of the page and arrange the controls so that you can see the data conveniently.
 * 6) Save the page as Freight DSum . If you receive a message about the connection string path, click OK.
 * 7) Switch to Page view and confirm that the running sum is correctly displayed.

Method 2: Use a Script to Calculate the Running Sum on the DAP
Using DSum in a query has the disadvantage that the function is executed for each row of the query, which may slow response with larger recordsets. In those cases, it may be preferable to use a script written in the Microsoft Visual Basic scripting language to calculate the running sum on the DAP.

To use a script instead of DSum in the query as above, follow these steps: <ol> Open qry Freight RunSum in Design view, delete the field RunSum, and then save the query as qry Freight by Employee .</li> Create a new DAP as described above, using both fields from qry Freight by Employee, and saving the page as Freight Script .</li> Create an unbound text box on the Header area of the page; it will have the name Text0 by default.</li> On the Tools menu, point to Macro, and then click Microsoft Script Editor.</li> In the Script Outline window, double-click MSODSC, and then double-click DataPageComplete.</li>  IMPORTANT: When you create VBScript blocks for MSODSC events, you must add a parameter to the event name as follows:

<SCRIPT LANGUAGE=vbscript FOR=MSODSC EVENT=Current(oEventInfo)>

The <I>oEventInfo</I> parameter returns specific information about the event to the script. You must add this parameter, whether or not it will be used, because the script will not work without it.

In this case, the event is DataPageComplete, so the opening SCRIPT tag should be this: <SCRIPT language=vbscript for=MSODC event=DataPageComplete(oEventInfo)> Insert the following script: <SCRIPT event=DataPageComplete(oEventInfo) for=MSODSC language=vbscript> </SCRIPT> </li> Save the script and close the Script Editor.</li> Switch to Page view and confirm that the running sum is correctly displayed.</li></ol>

For more information about using scripts in Web pages, click Microsoft Access Help on the Help menu, type vbscript in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Keywords: kbhowto kbdapscript KB233485

-

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

© Microsoft Corporation. All rights reserved.