Microsoft KB Archive/282381

= ACC2002: Reports Saved as Data Access Pages Are Missing Grand Totals =

Article ID: 282381

Article Last Modified on 9/27/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q282381



Novice: Requires knowledge of the user interface on single-user computers.

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



SYMPTOMS
When you save a report that contains a grand total as a data access page, the Grand Total box is blank.



CAUSE
This behavior occurs because you cannot bind controls in the Caption or Record Navigation section of a data access pages.



RESOLUTION
To work around this behavior, use one of the following methods.

Method 1
If the report is not based on any group, it will not create group headers/footers when you save it as a data access page. Therefore, to create a grand total, use the following method:  In the sample database Northwind.mdb, click Reports in the Database window, and then click New. In the New Report dialog box, click Report Wizard, click the Products table in the Choose the table or query where the object's data comes from box, and then click OK. In the Report Wizard, select the following fields from the Available Fields list, and then move them to the Selected Fields list:

ProductName

CategoryID

UnitPrice

 Click Next, remove any group levels, and then click Next two times. For Layout, select Tabular, and then click Finish. On the View menu, click Design View.</li> On the File menu, click Save As.</li> In the Save As dialog box, click Data Access Page in the As list, and then click OK.</li> In Page view of the newly created data access page, click Design View on the View menu.</li> In the Header: Products section of the data access page, click in the UnitPrice text box.</li> On the toolbar, click the arrow on the AutoSum toolbar button. Note that there are options for Sum, Average, Min, Max, Count, StDev, and Any.</li> Click Sum in the list. This creates a Footer: Products-UnitPrice section with a Bound Span control named SumOfUnitPrice.</li> On the View menu, click Page View to view the sum of UnitPrice column in the data access page.</li></ol>

Method 2
If the report is based on groups and each group has one or more totals followed by a grand total, the grand total will appear blank when you save the report as a data access page. The following steps show you how to workaround this behavior: <ol> In the sample database Northwind.mdb, click Reports in the Database window, and then click New.</li> In the New Report dialog box, click Report Wizard, click the Products table in the Choose the table or query where the object's data comes from box, and then click OK.</li> In the Report Wizard, select the following fields from the Available Fields list, and then move them to the Selected Fields list:

ProductName

CategoryID

UnitPrice

</li> Click Next two times, and then click Summary Options.</li> In the Summary Options dialog box, click to select the Sum check box, click OK, and then click Finish.</li> On the View menu, click Design View.</li> On the File menu, click Save As.</li> In the Save As dialog box, click Data Access Page in the As list, and then click OK.</li> <li>In Page view of the newly created data access page, scroll to the bottom of the page and note that the Grand Total box is blank.</li> <li>On the View menu, click Design View.</li> <li>On the Tools menu, point to Macro, and then click Microsoft Script Editor.</li> <li>In the Object list, click MSODSC.</li> <li> In the Events list, click DataPageComplete. The following script is inserted: <SCRIPT language=vbscript event=DataPageComplete for=MSODSC> </SCRIPT> IMPORTANT: When you create VBScript blocks for MSODSC events, you must add a parameter to the event name, as follows: <SCRIPT language=vbscript event=DataPageComplete(obj) for=MSODSC> </SCRIPT> The obj parameter added above is used to return specific information about the event to the script. You must add this parameter, regardless of whether it will be used or not, because the script will not work without it. </li> <li> Enter the following script within these script tags: Const FooterLevel = 3 Dim curGrandTot Dim sectionFooter

'[ProductSales Grand Total Sum] is the name of a text box that 'should already reside in the Navigation: GroupLevel0 'section of this data access page. If [ProductSales Grand Total Sum].value = &quot;&quot; Then Set sectionFooter = obj.DataPage.FirstSection

While Not (sectionFooter Is Nothing) 'Test to make sure that focus is in         'the Footer: GroupLevel0 section. If sectionFooter.type = FooterLevel Then 'The following line accumulates each of the totals per group 'to create a grand total. curGrandTot = curGrandTot + CCur(sectionFooter.HTMLContainer.children(&quot;Sum Of ProductSales&quot;).value) End If         Set sectionFooter = sectionFooter.NextSection Wend 'Fill the control with the frand total. [ProductSales Grand Total Sum].value = curGrandTot End If                   </li></ol>

<div class="moreinformation_section">

Steps to Reproduce the Behavior
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.

<ol> <li>Start Microsoft Access and then open the sample database Northwind.mdb or the sample project NorthwindCS.adp.</li> <li>In the Database window, click Reports, and then click New.</li> <li>In the New Report dialog box, click Report Wizard, click Product Sales for 1997 in the Choose the table or query where the object's data comes from box, and then click OK.</li> <li>In the Report Wizard, select the following fields from the Available Fields list, and then move them to the Selected Filelds list:

CategoryName

ProductSales

</li> <li>On the next page, add CategoryName as a grouping level, and then click Next.</li> <li>On the next page, click Summary Options.</li> <li>In the Summary Options dialog box, click to select the Sum check box, click OK, and then click Finish.</li> <li>Use the navigation buttons to move to the last page of the report and note that the Grand Total figure is $608,846.76.</li> <li>On the View menu, click Design View.</li> <li>On the File menu, click Save As, click Data Access Page in the list, and then click OK two times. When the Product Sales for 1997 data access page opens, scroll to the bottom of the page. Note that the Grand Total text box is blank.</li></ol>

<div class="references_section">