Displaying First and Last Record Per Page in Page Header

  • Thread starter Thread starter Abe
  • Start date Start date
A

Abe

Trying to create phone-book style report that shows first
and last record on the page in the page header. I had no
problem creating it in the page footer but can't seen to
get it in the header.

Any suggestions?

Thank you.
 
Abe,
The PageHeader has access to the first detail record.
So to show the first record on each page all you need do is to place
the customer name control in the header.
Whatever the first record on the page is will be shown in the Header.

To show the last name in the Page Header requires a little work.
Add a new table to the database.
ID Field (AutoNumber No Duplicates)
FinalName (Text)
Name the table 'tblPageHeader'

For the first record in the table enter a space (or anything)
in the FinalName field.
Continue adding records (by adding a space in the FinalName field
for as many pages as you expect the report to have,
incrementing the ID field by 1 each record.
So if you expect 20 pages, make 20+ records.
(This can be done using code, but that would be another post.)

In the Report, add a control to compute [Pages].
If you don't already have one
= [Page] & " of " & [Pages]
will do.

Then add a control in the Page Header where you
wish to display the final name on the page:
=DLookUp("[FinalName]","tblPageHeader","[ID] = " & [Page])

The Page Footer has access to the last Detail record.
Code the Report's PageFooter Format event:

DoCmd.SetWarnings False
DoCmd.RunSQL "Update tblPageHeader Set FinalName = " & Chr(34) &
[CompanyName] & Chr(34) & "Where [ID] = " & [Page]
DoCmd.SetWarnings True

Run the report.
The [Pages] control forces the report to be formatted twice.
On the first pass, the table is updated after each page
with the final company name on that page.
Then the report is displayed and the DLookUp in the Page Header
control reads the corresponding page name from the table.

Worked fine for me.
 
Back
Top