PRA: Expressions Appear Blank in Form Header or Footer

Article ID: Q119752
Creation Date: 23-AUG-1994
Revision Date: 03-DEC-1996

The information in this article applies to:

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

SYMPTOMS

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

Calculated controls that compute expressions in your form header or footer appear empty.

CAUSE

This problem can be caused by either or both of the following property settings:

The Height property of the form's detail section is set to 0 (zero).
The Visible property of the form's detail section is set to No.

RESOLUTION

This situation can occur when you want to compute and display totals for a table or query in the header or footer of a form, but you do not want to see the actual records in the table or query. In order to keep the records from being displayed, you set the detail section's Height property to 0, or its Visible property to No.

To work around this problem, compute the totals in a query rather than from expressions on the form. In addition to working around this problem, computing the totals in a query provides the following advantages:

Calculation of the totals is faster in a query.
The form does not open until the calculations are complete. If you compute totals in expressions on the form, the form opens before the calculations are carried out, which may not be as attractive to users of the form.
You do not have to place the totals in the form's header. The totals can be displayed in the form's detail section.

Please see the "How to Compute Totals for a Form Using a Query" section later in this article for an example of how to compute totals using a query.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access 1.0, 1.1, 2.0, 7.0, and 97. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Problem

Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0) and create a blank, new form based on the Orders table.

In Microsoft Access 7.0 and 97: On the View menu, click Form Header/Footer.

In Microsoft Access 2.0: On the Format menu, click Form Header/Footer.

In Microsoft Access 1.x: On the Layout menu, click Form Header/Footer.

Set the form footer section's Visible property to No.

Set the detail section's Visible property to No, or set the detail section's Height property to 0 (zero).

Add the following two text boxes to the form's header section:

      Text Box
      ---------------------------------
      ControlSource: =Count([OrderID])

      NOTE: In versions 1.x and 2.0, type a space in [Order ID].

      Text Box
      ------------------------------
      ControlSource: =Sum([Freight])

Save the form as Form1, and then close it.
Open Form1 in Form view. Note that both text boxes appear empty.
Click either text box, and then click the other text box. Note that the values are displayed in the text boxes once you have clicked them.

How to Compute Totals for a Form Using a Query

The following example demonstrates how to compute totals for your form using a query, rather than computing the totals in expressions on the form:

Open the sample database Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0) and create the following new query based on the Orders table:

      Query: Order Totals Query
      -------------------------
      Type: select query

      Order Totals Query Fields
      ------------------------------------
      Field: OrdersCount: Count([OrderID])

      NOTE: In Microsoft Access 1.x and 2.0, type a space in [Order ID].

      Field: FreightSum: Sum([Freight])

Save the query as Order Totals Query.
Create a blank, new form based on the Order Totals Query.
In Microsoft Access 7.0 and 97: On the View menu, click Form Header/Footer.
In Microsoft Access 2.0: On the Format menu, click Form Header/Footer.
In Microsoft Access 1.x: On the Layout menu, click Form Header/Footer.
Set the form footer section's Visible property to No.
Set the detail section's Visible property to No, or set the detail section's Height property to 0 (zero).

Add the following two text boxes to the form's header section:

      Text Box
      --------------------------
      ControlSource: OrdersCount

      Text Box
      -------------------------
      ControlSource: FreightSum

Save the form as Form2, and then close it.
Open Form2 in Form view. Note that the values in the text boxes are displayed correctly.
NOTE: You do not have to place the text boxes in the form's header section when you use this method. You can place the text boxes in a visible detail section if you want.

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.