'From' and 'To' in Report Header

  • Thread starter Thread starter John_U
  • Start date Start date
J

John_U

I have a multipage report and want to put an indicator
in the header as to what is on that particular page.
Say for 'cities' the header would look like
'From New Rochelle to New York'.

I have no trouble getting the city for the first record on
the report page (New Rochelle). But how do I get the city
for last record on the page (New York).

I can put the last data in the footer but I would really
like it in the header.

Thanks,
John
 
I have a multipage report and want to put an indicator
in the header as to what is on that particular page.
Say for 'cities' the header would look like
'From New Rochelle to New York'.

I have no trouble getting the city for the first record on
the report page (New Rochelle). But how do I get the city
for last record on the page (New York).

I can put the last data in the footer but I would really
like it in the header.

Thanks,
John
Click to expand...

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.

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:
As Control Source:
=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