DictionaryStyle Headings
Phone books and dictionaries typically add the first entry and last entry at the top of the page to make it easier to find information. You can accomplish this on your reports by adding some code in several different sections. The code uses the Format event to create listings at the top of each page. Because this event does not fire for reports open in Report view, you'll need to open the report in Print Preview to see the effect.
The report you'll create is a listing of the attendees for the conference. It is grouped by the first letter of the last name. Figure 11-5 shows part of the report.
|
Last Name |
First Name |
Company |
|
Aneel Paolino |
Miguel |
Tortuga Restaurante |
|
Accorti |
Paolo |
Franchi S.p-A. |
|
Anders |
Maria |
Alfreds Futterkiste |
|
Afonso |
Pedro |
Comercio Mineiro |
|
Ashworth |
Victoria |
B's Beverages |
|
Buchanan |
Steven | |
|
Braunschweiger |
Art |
Split Rail Beer & Ale |
|
Bertrand |
Marie |
Paris spécialités |
|
Bergulfsen |
Jonas |
Santé Gourmet |
|
Berglund |
Christina |
Berglunds snabbkop |
|
Batista |
Bernardo |
Que De líe ia |
|
Brown |
Elizabeth |
Consolidated Holdings |
|
Bennett |
Helen |
island Trading |
You'll modify the report to add two text boxes to the Page Header section. The first control, named txtFirst, will be hidden and store the first entry on each page by setting the ControlSource property to the last name of the attendee. The second control, named txtLast, will be visible and display the attendee entries.
The trick is to force Access to do two passes on the report because you need to determine the last attendee on the page and subsequently display it on the top of the page. To do this, use the Pages property in the Page Footer. To calculate the number of pages in the report, Access will format the report twice. You also need a report footer section to run some code. Add a text box to the page footer with the following expression:
="Page " & [Page] & " of " & [Pages]
Now you'll start adding the code. First, add two variables. The first is a flag to indicate whether you are on the first or the second pass. The second will be used to store the last name of the attendee when you get to the bottom of each page.
Dim blnFirstPass As Boolean
Dim astrLastNames() As String
Next, add code to the Open event of the report. It sets up the array and initializes the flag.
Private Sub Report_Open(Cancel As Integer) ' start the first pass ReDim astrLastNames(O) blnFirstPass = True
End Sub
Save the last name of the last attendee on the page. This is the value you want to display at the top of the page. For this, add the following code in the Format event of the Page Footer section:
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer) ' Resize the array
ReDim Preserve astrLastNames(UBound(astrLastNames) + 1) ' Save the Last Name of the last attendee on the page astrLastNames(Me.Page - 1) = Me![Last Name]
End Sub
Now you need to display the first entry and the last entry on the page in the Page Header section. Add the following code to the Format event of the section:
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer) ' Make sure we are not on the first pass If (Not blnFirstPass) Then
Me.txtLast = Me.txtFirst & " - " & astrLastNames(Me.Page - 1) End If
End Sub
Finally, you set the first pass flag to False to indicate that the first pass is complete. Add the following code to the Format event of the Report Footer section. The Report Footer is the last formatted section in the report so it's a good place to reset the flag.
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer) ' first pass is complete blnFirstPass = False End Sub
When you run the report, it should look something like the one shown in Figure 11-6.
- Figure 11-6
Post a comment