On Fri, 7 Nov 2008 16:11:35 -0500, Steve wrote:
I'm working on a report for an existing database. The report will be a
directory of over 500 employees and thus multiple pages. What do I need to
do to show the first employee and the last employee on each page at the top
of each page? For example,
on page 2: Clinton to Kennedy
on page 5: McCain to Obama
Thanks!
Steve
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
a control bound to the EmployeeName field in the header.
Whatever the first detail 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 500 pages, make 500+ records.
(This can be done using code, but that would be another post.)
You now have a table with over 500 records and the [ID] numbers are 1
to up to the number of records you have added.
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 to 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:
CurrentDb.Execute "Update tblPageHeader Set FinalName = " & Chr(34) &
[EmployeeName] & Chr(34) & "Where [ID] = " & [Page], dbFailOnError
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
Employee 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.
No need to delete any of the values in the table. The next time you
run the report, new values will simply replace existing ones.
Change [EmployeeName] to whatever the actual name of that field is.
Add error handling as needed.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail