ACC2000: Displaying First and Last Record Per Page in Page Footer (208412)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q208412
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

This article demonstrates how you can 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 want to create a phone book-like index on each page of a report.

This article demonstrates how you can 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 Next three times and then ,when asked What title do you want for your report, type Index Report.
  5. On the View menu, click Design View.
  6. 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 technique 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 box, type a number for how many columns you want on your report.

REFERENCES

For more information about multi-column reports, click Microsoft Access Help on the Help menu, type Create a multiple-column report in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:6/28/2004
Keywords:kbhowto kbusage KB208412
©2004 Microsoft Corporation. All rights reserved.