Access Diva Customer Page Headers

Custom Page Header...

Adapted from https://bytes.com/topic/access/answers/852264-index-top-pages-report

The difficult I do immediately, the impossible takes a little bit longer.

Reports

Tips (Main)

Home

Print Full or Partial Sheet of Same Labels

Print One or More Labels for One or More Clients (Records)

Multiple copies of 1(one) Report using a Cartesian Product query

Keep Main Report/Subreport Records Together

Multicolumn Report with Images

Filtering Reports

Print Constant Number of Lines (or add a Constant Number of Lines)

Ranking Reports Using Shapes

Send Report (or Labels) to Specific Printer

Function fCompileNumbers(lngHowManyRecord As Long)
'8.16.2019 From https://www.access-diva.com/
'?fCompileNumbers(10)
 
    Dim lngNo As Long
    Dim lngCount As Long
    Dim rs As DAO.Recordset
 
    Set rs = CurrentDb.OpenRecordset("tblPageHeaders", dbOpenDynaset)
 
    Do
        lngNo = lngNo + 1
        rs.AddNew
        rs!phCount = lngNo
        rs.Update
        lngCount = lngCount + 1
    Loop Until lngCount = lngHowManyRecord
 
End Function

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

Fields	Data Type
phID	Autonumber, Primary Key
phCount	Number, Long
phFirstRecord	Text, 75
phLastRecord	Text, 75

While the answer given worked for the that specific scenario, I wanted something a bit more adaptable. So, the answer below is somewhat modified from the one given here. To download the example of what is below click here.

Showing the last record in the Page Header requires a little work so let's get started. Add a new table to your database named tblPageHeaders with the below fields.

Copy the below and place it in a module and run it to fill your tblPagerHeaders or you can set up a Form to run prior to opening your Report. Just remember you need to add enough records to cover your Report's pages so always add extra, i.e. if you think the Report is 10 pages add 20 to be safe because Report Headers and Footers can take up space increasing your Reports length.

On the Report that you want to show Custom Headers, add a control to the Page Footer and put...

= [Page] & " of " & [Pages]

...as the Control Source, if you don't already have it there. Note, adding the [Pages] Control cause Reports to render twice which can make it take longer to load. 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.

Then in the Report's Page Header On_Format Event Procedure place...

In the Page Header where you wish to display the Custom Label you can use...

    strSQL = "UPDATE tblPageHeaders " & _
                "SET phFirstRecord = """ & [pSection] & """ " & _
                    "WHERE [phCount] = " & [Page] & ""
             CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

In the Report's Page Footer On_Format Event Procedure place...

    strSQL = "UPDATE tblPageHeaders " & _
                "SET phLastRecord = """ & [pSection] & """ " & _
                    "WHERE [phCount] = " & [Page] & ""
             CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

    strSQL = "UPDATE tblPageHeaders " & _
                "SET tblPageHeaders.phFirstRecord = Null, tblPageHeaders.phLastRecord = Null"
             CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges

In the Report's On_Unload Event Procedure place...

One last tip, if you are looking for ALPHA order, then be sure to put that field in ascending order in the query. Now run the report.

=DLookUp("[phLastRecord]","tblPageHeaders","[phCount] = " & [Page])

=IIf(DLookUp("[phLastRecord]","tblPageHeaders","[phCount] = " & ([Page]+1)) Is Null,DLookUp("[phLastRecord]","tblPageHeaders","[phCount] = " & ([Page])),DLookUp("[phLastRecord]","tblPageHeaders","[phCount] = " & ([Page]+1)))

=DLookUp("[phFirstRecord]","tblPageHeaders","[phCount] = " & [Page])

=IIf(DLookUp("[phFirstRecord]","tblPageHeaders","[phCount] = " & ([Page]+1)) Is Null,DLookUp("[phFirstRecord]","tblPageHeaders","[phCount] = " & ([Page])),DLookUp("[phFirstRecord]","tblPageHeaders","[phCount] = " & ([Page]+1)))

This line will show the Last record on current page...

This line will show the Last record on next page. If it is the last page it will show the last record.

This line will show the First record on current page...

This line will show First record on next page. If it is the first page it will show the first record.

This Function is based on tblPageHeaders and its Field names. If you change the either you will need to change the Function.

Best option

Within the sample download is a Form that has links to all Access Diva's web pages of tips. Enjoy!