ACC: Displaying First and Last Record Per Page in Page Footer

Last reviewed: July 16, 1997
Article ID: Q153029
The information in this article applies to:
  • Microsoft Access versions 7.0, 97

SUMMARY

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

This article demonstrates how to create a report that displays the first and last record on a report page in the page footer for that page. This is useful if you are trying to create a phone book-like index on each page of a report.

The following steps will create two fields in the page footer that will display the first record and last record on the current page. A third invisible field, placed in the page header, will actually retrieve the value of the first record. This value is then transferred to one of the fields in the page footer using a SetValue macro. This method is meant to duplicate the index that is normally seen at the bottom of a phone book page.

MORE INFORMATION

To create a report that displays an index of the first and last record on a page in the page footer, follow these steps:

  1. Open the sample database Northwind.mdb, and create the following macro:

          Macro Name    Action
          ----------------------
          SetFirstRec   SetValue
    
          SetFirstRec Actions
          --------------------------------------------------
          SetValue
          Item: [Reports]![Index Report]![FirstRec]
          Expression: [Reports]![Index Report]![IdxFirstRec]
    
    

  2. Start the Report Wizard and create a report based on the Customers table. In the "Which fields do you want on your report" box, select the following fields, and then click Next:

          CompanyName
          ContactName
          City
          Region
          Country
    

  3. In the "Do you want to add any grouping levels" box, click Next. In the "What sort order do you want for your records" box, select CompanyName in the first combo box.

  4. Click Finish to display the new report in Print Preview.

  5. On the File menu, click SaveAs/Export. Enter "Index Report" (without the quotation marks) as the report name and click OK.

  6. On the View menu, click Report Design, and add the following text box to the page header:

          Text box:
          Name: IdxFirstRec
          ControlSource: [CompanyName]
          Visible: No
    

  7. Add the following two text boxes to the Page Footer:

          Text box:
          Name: FirstRec
    

          Text box:
          Name: LastRec
          ControlSource: [CompanyName]
    

  8. Set the Page Header OnFormat property to the SetFirstRec Macro, and save and close the report.

  9. Open the report in Print Preview. Note that the first and last company names on the page are displayed in the page footer. Go to the next page of the report. Note that the first and last company names for that page are now displayed in the page footer.

NOTE: This will also work with a multiple-column report. To make the report have multiple columns, click Page Setup on the File menu. Click the Layout tab. In the Items Across, type a number for how many columns you want on your report.

REFERENCES

For more information about multiple-column reports, search the Help Index for "multiple-column reports," and then "Create a multiple-column report," or ask the Microsoft Access 97 Office Assistant.


Keywords : FmrHowto kbusage
Version : 7.0 97
Platform : WINDOWS
Hardware : X86
Issue type : kbhowto


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.

Last reviewed: July 16, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.