By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Index At The Top Of Pages In Report

P: n/a
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
Nov 7 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Nov 7 '08 #2

P: n/a

"Steve" <no******@nomsense.comwrote in message
news:f-******************************@earthlink.com...
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

Can the index can be on the bottom of the page?

Place a unbound text box in the page header.
In the on format event of the page header fill the control with the employee
name
Place a text box in the page footer using the unbound control in the page
header as the source.
Place a 2nd text box in the page footer using the employee name as the
source.
Nov 7 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.