| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
Chapter 7, "What Every Developer Needs to Know About Report Basics," covers all the basics of report design. Reports are an integral part of almost every application. The Access 95 report design tool is very powerful. Although it is easy to create most reports, as you mature as an Access developer, you will probably want to learn the intricacies of Access report design. This chapter covers report events, advanced techniques, and tips and tricks of the trade.
Although the events that reports have are not as plentiful as the events that forms have, the report events you can trap for enable you to control what happens as your report runs. This section discusses report events. The section titled Events Available for Report Sections and When to Use Them covers events that are specific to report sections.
The Open event is the first event that occurs for a report. It occurs before the report begins printing or displaying. In fact, it occurs before the query underlying the report is run. An example of the use of the Open event is as follows:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmReportDateRange", , , , , acDialog, _
"Project Billings by Work Code"
If Not IsLoaded("frmReportDateRange") Then
Cancel = True
End If
End Sub
This code can be found in rptProjectBillingsByWorkCode in CHAP14.MDB on the sample code CD. It attempts to open the form called frmReportDateRange. This is the criteria form that is used to supply the parameters for the query underlying the report. If the form cannot be loaded, the report is canceled.
The Close event occurs as the report is closing, before the Deactivate event occurs. The following example illustrates the use of the Close event.
Private Sub Report_Close()
DoCmd.Close acForm, "frmReportDateRange"
End Sub
This code is found in the report called rptProjectBillingsByWorkCode in CHAP14.MDB on the sample code CD. It closes the criteria form frmReportDateRange when the report is closing.
The Activate event of a report occurs when the report becomes the active window. It occurs after the Open event and before the report starts printing. It is often used to display a custom toolbar that will be visible whenever the report is active. Here's an example:
Private Sub Report_Activate()
' Used by Solutions to show toolbar with Show Me button.
' Hide built-in Print Preview toolbar.
' Show Custom Print Preview toolbar.
DoCmd.ShowToolbar "Print Preview", acToolbarNo
DoCmd.ShowToolbar "Custom Print Preview", acToolbarYes
End Sub
This code is found in the Activate event of the EmployeeSales report, which is part of the SOLUTIONS.MDB database that ships with Access. It hides the Print Preview toolbar and shows the custom toolbar called Custom Print Preview. As you will see, this event works along with the Deactivate event to show and hide the custom report toolbars when the report becomes the active window and the user moves the focus to another window.
The Deactivate event occurs when you move to another Access window or close the report. The Deactivate event does not occur when focus is moved to another application. Here's an example of how the Deactivate event is used:
Private Sub Report_Deactivate()
' Used by Solutions to hide toolbar that includes Show Me button.
' Hide Custom Print Preview toolbar.
' Show built-in Print Preview toolbar.
DoCmd.ShowToolbar "Custom Print Preview", acToolbarNo
DoCmd.ShowToolbar "Print Preview", acToolbarWhereApprop
End Sub
This routine hides the custom toolbar that was displayed during the Activate event and indicates that the Print Preview toolbar should once again display where appropriate. You do not want to show the Print Preview toolbar here. Instead, you just "reset" it to display whenever Access's default behavior would tell it to display.
If no records meet the criteria of the recordset underlying a report's RecordSource, the report prints without any data. The report displays #Error in the Detail section of the report. To eliminate this problem, you can code the NoData event of the report. Here's an example:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub
This code is found in the NoData event of the rptProjectBillingsByWorkCode in CHAP14.MDB on the sample code CD. In case no data is returned by the report's underlying recordset, a message is displayed to the user and Cancel is set equal to True. This exits the report without running it.
The Page event gives you the opportunity to do something immediately before the formatted page is sent to the printer. It enables you to do something such as place a border around a page. Here's an example:
Private Sub Report_Page()
'Draw a page border around this report.
Me.Line (0, 0)-(Me.LogicalPageWidth, Me.LogicalPageHeight), , B
End Sub
This code is found in the SalesLetter report, which is part of the SOLUTIONS.MDB database that ships with Access. It draws a line on the report, starting in the upper-left corner of the report and going to the lower-right corner of the report. It uses the LogicalPageWidth and LogicalPageHeight properties to determine where the lower-right corner of the printable area of the report will be.
If a Jet engine error occurs when the report is formatting or printing, the Error event is triggered. This error usually occurs if the RecordSource for the report does not exist or if someone else is has exclusive use over the report's RecordSource. Here's an example:
Private Sub Report_Error(DataErr As Integer, Response As Integer)
If DataErr = 2580 Then
MsgBox "Record Source Not Available for This Report"
Response = acDataErrContinue
End If
End Sub
This code responds to a DataErr of 2580. This error code means that the RecordSource for the report is not available. A custom message is displayed to the user and the Access error is suppressed.
Just as it is important to understand the order of events for forms, it is also important to understand the order of events for reports. When the user opens a report, previews it, and then closes it, the following sequence of events occurs:
Open®Activate®Close®Deactivate
When the user switches to another report or to a form, the following sequence occurs:
Deactivate(Current Report)®Activate(Form or Report)
The Deactivate event does not occur when the user switches to a dialog box, to a form whose PopUp property is set to Yes, or to a window of another application.
Just as the report itself has events, so does each section of the report. The three section events are the Format event, Print event, and Retreat event. These events are covered in this section.
The Format event occurs after Access has selected the data to be included in a report section, but before it formats or prints the data. The Format event enables you to affect the layout of the section or to calculate the results of data within the section, before the section actually prints. Here's an example:
Private Sub Detail2_Format(Cancel As Integer, FormatCount As Integer)
' Determine whether to print detail record or "Continued."
' Show Continued text box if at maximum number of
' detail records for page.
If (Me!Row = Me!OrderPage * (Me!RowsPerPage - 1) + 1) And Me!Row <> _
Me!RowCount Then
Me!Continued.Visible = True
End If
' Show page break and hide controls in detail record.
If Me!Continued.Visible Then
Me!DetailPageBreak.Visible = True
Me!ProductID.Visible = False
Me!ProductName.Visible = False
Me!Quantity.Visible = False
Me!UnitPrice.Visible = False
Me!Discount.Visible = False
Me!ExtendedPrice.Visible = False
' Increase value in Order Page.
Me.NextRecord = False
Me!OrderPage = Me!OrderPage + 1
' Increase row count if detail record is printed.
Else
Me!Row = Me!Row + 1
End If
End Sub
This code is found in the Invoice report included in the SOLUTIONS.MDB database that ships with Access. The report contains controls that track how many rows of detail records should be printed on each page. If the maximum number of rows have been reached, a control containing the text Continued on Next Page... is visible. If the control is visible, the page break control is also made visible, and all the controls that display the detail for the report are hidden. The report is kept from advancing to the next record.
Another example of the Format event is found in the Format event of the Page Header of the EmployeeSales report, which is part of the SOLUTIONS.MDB database that ships with Access. Because the report is an unbound report whose controls are populated using VBA code at runtime, the report needs to determine what will be placed in the report header. This varies depending on the result of the crosstab query on which the report is based. The code looks like this:
Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)
Dim intX As Integer
' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format$(intX)) = rstReport(intX - 1).Name
Next intX
' Make next available text box Totals heading.
Me("Head" + Format$(intColumnCount + 1)) = "Totals"
' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format$(intX)).Visible = False
Next intX
End Sub
The code loops through each column of the recordset resulting from the execution of the crosstab query. The controls in the Page Header of the report are populated with the name of each column in the query result. The final column header is set equal to Totals. Finally, any remaining (extra) Text boxes are hidden. More examples of the use of the Format event are covered throughout this chapter.
By placing logic in the Format event of the detail section of a report, you are able to control what happens as each line of the detail section is printed.
The code within the Print event is executed when the data has been formatted to print in the section, but before it is actually printed. The Print event occurs at the following times for the various sections of the report:
Detail Section: Just before the data is printed.
Group Headers: Just before the group header is printed; the Print event of the group header has access to both the group header and to the first row of data in the group.
Group Footers: Just before the group footer is printed; the Print event of the group detail has access to both the group footer and to the last row of data in the group.
The following code is found in the Print event of the Detail section of the EmployeeSales report included in the SOLUTIONS.MDB database that ships with Access.
Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)
Dim intX As Integer
Dim lngRowTotal As Long
' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0
For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in detail section.
lngRowTotal = lngRowTotal + Me("Col" + Format$(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + _
Format$(intX))
Next intX
' Place row total in text box in detail section.
Me("Col" + Format$(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
End If
End Sub
The code begins by evaluating the PrintCount property. If the PrintCount property is equal to one, indicating that this is the first time that the Print event has occurred for the detail section, the row total is set equal to zero. The code then loops through each control in the section, accumulating both totals for each column of the report and a total for the row. After the loop has been exited, the routine places the row total into the appropriate control and adds the row total to the grand total for the report. The detail section of the report is now ready to be printed.
Many people are confused as to when to place code in the Format event and when to place the code in the Print event. If you are doing something that doesn't affect the page layout, you should use the Print event. If you are doing something that affects the physical appearance (the layout) of the report, use the Format event.
Sometimes Access needs to move back to a previous section when printing. This occurs, for example, when a Group's Keep Together property is set to With First Detail or Whole. Access needs to format the Group Header and the first detail record or, in the case of Whole, the entire group. It then determines whether it can fit the section on the current page. It retreats from the two sections, formats them, and prints them. A Retreat event occurs for each section. Here's an example of the Retreat event for a report Detail section:
Private Sub Detail1_Retreat()
' Always back up to previous record when detail section retreats.
rstReport.MovePrevious
End Sub
This code is placed in the Retreat event of the EmployeeSales report that is part of the SOLUTIONS.MDB. Because the report is an unbound report, the report needs to return to the previous record in the recordset whenever the Retreat event occurs.
Whenever you are working with an unbound report, you need to be careful to ensure that the record pointer remains synchronized with the report. For example, if the record pointer has been advanced and the Retreat event occurs, the record pointer must be moved back to the previous record.
Just as report events have an order, report sections also have an order of events. All the Format and Print events for each section occur after the Open and Activate events of the report, but before the Close and Deactivate events for the report. The sequence looks like this:
Open(Report)®Activate(Report)®Format(Report Section)®Print(Report Section)® Close(Report)®Deactivate(Report)
Several report properties are available only at runtime. They enable you to refine the processing of your report significantly.
The MoveLayout property is used to indicate to Access whether it should move to the next printing location on the page. By setting the property to False, the printing position is not advanced.
The NextRecord property is used to specify whether a section should advance to the next record. By setting this property to False, you suppress advancement to the next record.
The PrintSection property is used to indicate whether the section is printed. By setting this property to False, you can suppress the printing of the section.
By using the MoveLayout, NextRecord, and PrintSection properties in combination, you can determine exactly where, how, and whether data is printed. Table 14.1 illustrates this point.
MoveLayout |
NextRecord |
PrintSection |
Effect |
|
True |
True |
True |
Move to the next position, get the next record, and print the data. |
|
True |
False |
True |
Move to the next position, remain on the same record, and print the data. |
|
True |
True |
False |
Move to the next position, get the next record, and don't print the data. This has the effect of skipping a record and leaving a blank space. |
|
True |
False |
False |
Move to the next position, remain on the same record, and don't print. This causes a blank space to appear without moving to the next record. |
|
False |
True |
True |
Remain in the same position, get the next record, and print the data. This has the effect of overlaying one record on another. |
|
False |
False |
True |
Not allowed. |
|
False |
True |
False |
Remain in the same position, get the next record, and refrain from printing. This has the effect of skipping a record without leaving a blank space. |
|
False |
False |
False |
Not allowed. |
The FormatCount property is used to evaluate the number of times the Format event has occurred for the current section of the report. The Format event occurs more than once whenever the Retreat event occurs. By checking the FormatCount property, you can ensure that complex code placed within the Format event is executed only once.
The PrintCount property is used to identify the number of times the Print event has occurred for the current section of the report. The Print event occurs more than once whenever the Retreat event occurs. By checking the value of the PrintCount property, you can ensure that logic within the Print event is executed only once.
The HasContinued property is used to determine whether part of the current section is printed on a previous page. You can use this property to hide or show certain report controls (for example, Continued From...), depending on whether the section is continued.
The WillContinue property is used to determine whether the current section will continue on another page. Like the HasContinued property, you can use this property to hide or display certain controls when a section continues on another page.
In developing reports, you should ensure that the report can be used in as many situations as possible. This means that you build as much flexibility into the report as possible. By using the events and properties that have been covered in this chapter, you can build flexibility into your reports. This might involve changing the report's RecordSource at runtime, using the same report to print summary data, detail data, or both, changing the print position, or even running a report based on a crosstab query with unbound controls. All these aspects of report design are covered in this section.
There are many times when you might want to change a report's recordsource at runtime. By doing this, you can allow your users to alter the conditions for your report and transparently modify the query on which the report is based. The rptClientListing report, contained in CHAP14.MDB, has the following code in its Open event:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmClientListingCriteria", WindowMode:=acDialog
If Not IsLoaded("frmClientListingCriteria") Then
Cancel = True
Else
Select Case Forms!frmClientListingCriteria!optCriteria.Value
Case 1
Me.RecordSource = "qryClientListingCity"
Case 2
Me.RecordSource = "qryClientListingStateProv"
Case 3
Me.RecordSource = "qryClientListing"
End Select
End If
End Sub
This code begins by opening up the frmClientListingCriteria form if it is not already loaded. It loads the form modally and waits for the user to select the report criteria (see Figure 14.1). Once the user clicks to preview the report, the form sets its own Visible property to False. This causes execution to continue within the report, but leaves the form in memory so that its controls can be accessed using VBA code. The value of the form's optCriteria option button is evaluated. Depending on which option button is selected, the report's RecordSource property is set to the appropriate query. The following code is placed in the Close event of the report:
Private Sub Report_Close() DoCmd.Close acForm, "frmClientListingCriteria" End Sub
Figure 14.1. The criteria selection used to determine RecordSource.
This code closes the criteria form as the report is closing. The frmClientListingCriteria form has some code that is important to the processing of the report. It is found in the After Update event of the optCriteria option group:
Private Sub optCriteria_AfterUpdate()
Select Case optCriteria.Value
Case 1
Me!cboCity.Visible = True
Me!cboStateProv.Visible = False
Case 2
Me!cboStateProv.Visible = True
Me!cboCity.Visible = False
Case 3
Me!cboCity.Visible = False
Me!cboStateProv.Visible = False
End Select
End Sub
This code evaluates the value of the option group. It hides and shows the visibility of the cboCity and cboState combo boxes, depending on which option button is selected. The cboCity and cboState combo boxes are then used as appropriate criteria for the queries that underlie the rptClientListing report.
Many programmers create three reports for their users: one that displays summary only, one that displays detail only, and another that displays both. This is totally unnecessary. Because report sections can be optionally hidden or displayed at runtime, you can create one report that meets all three needs. The rptClientBillingsByProject report included in the CHAP14.MDB database illustrates this point. Place the following code in the Open event of the report:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmReportDateRange", WindowMode:=acDialog, OpenArgs:= _
"rptClientBillingsbyProject"
If Not IsLoaded("frmReportDateRange") Then
Cancel = True
Else
Select Case Forms!frmReportDateRange!optDetailLevel.Value
Case 1
Me.Caption = Me.Caption & " - Summary Only"
Me!lblTitle.Caption = Me.lblTitle.Caption & " - Summary Only"
Me.Detail.Visible = False
Case 2
Me.Caption = Me.Caption & " - Detail Only"
Me!lblTitle.Caption = Me.lblTitle.Caption & " - Detail Only"
Me.GroupHeader0.Visible = False
Me.GroupFooter1.Visible = False
Me!CompanyNameDet.Visible = True
Case 3
Me.Caption = Me.Caption & " - Summary and Detail"
Me!lblTitle.Caption = Me.lblTitle.Caption & " - Summary and _
Detail"
Me!CompanyNameDet.Visible = False
End Select
End If
End Sub
The code begins by opening up frmReportDateRange (see Figure 14.2) included in CHAP14.MDB. The form contains an option group asking users whether they want a Summary report, Detail report, or Report that contains both Summary and Detail. If Summary Only is selected, the caption of the report window and of the lblTitle label are modified, and the Visible property of the detail section is set to False. If the user selects Detail only, the caption of the report window and of the lblTitle label are modified, and the Visible property of the Group Header and Footer sections are set to False. A control in the detail section containing the company name is made visible. The CompanyName control is visible in the detail section when the Detail Only report is printed, but it is invisible when the Summary and Detail report is printed. When the Summary and Detail report is selected, no sections are hidden. The Caption of the report window and of the lblTitle label are modified and the CompanyName control is hidden.
Figure 14.2. The criteria selection used to determine detail level.
The code behind the Preview button of the form looks like this:
Private Sub Preview_Click()
If IsNull([BeginDate]) Or IsNull([EndDate]) Then
MsgBox "You must enter both beginning and ending dates."
DoCmd.GoToControl "BeginDate"
Else
If [BeginDate] > [EndDate] Then
MsgBox "Ending date must be greater than Beginning date."
DoCmd.GoToControl "BeginDate"
Else
Me.Visible = False
End If
End If
End Sub
This code ensures that both the beginning date and the ending date are filled in, and that the beginning date is before the ending date. If both of these rules are fulfilled, the Visible property of the form is set to False. Otherwise, an appropriate error message displays.
Many times, users want to print multiple copies of the same label. This can be accomplished by using the MoveLayout, NextRecord, PrintSection, and PrintCount properties of a report. The form shown in Figure 14.3 is called frmClientLabelCriteria and is found in CHAP14.MDB. It asks that the users select a company and the number of labels they want to print for that company. The code under the Print Labels command button looks like this:
Sub cmdPrintLabels_Click()
On Error GoTo Err_cmdPrintLabels_Click
Dim stDocName As String
stDocName = "lblClientMailingLabels"
DoCmd.OpenReport stDocName, acPreview, , "CompanyName = '" _
& Me!cboCompanyName.Value & "'"
Exit_cmdPrintLabels_Click:
Exit Sub
Err_cmdPrintLabels_Click:
MsgBox Err.Description
Resume Exit_cmdPrintLabels_Click
End Sub
Figure 14.3. The criteria selection used to specify company name and number of labels to print.
Notice that the routine uses the company name selected from the combo box as criteria to run the lblClientMailingLabels report. The Open event of lblClientMailingLabels looks like this:
Private Sub Report_Open(Cancel As Integer)
If Not IsLoaded("frmClientLabelCriteria") Then
MsgBox "You Must Run This Report From Label Criteria Form"
Cancel = True
End If
End Sub
This code tests to ensure that the frmClientLabelCriteria form is open. If not, a message is displayed and the report is canceled. The key to the whole process is found in the Print event of the detail section. It looks like this:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount < Forms!frmClientLabelCriteria!txtNumberOfLabels Then
Me.NextRecord = False
End If
End Sub
This code compares the PrintCount property to the number of labels that the user wants to print. As long as the PrintCount is less than the number of labels requested, the record pointer is not advanced. This causes multiple labels to be printed for the same record.
One problem facing users is that they want to print multiple copies of the same label, but another common problem is that users might want to print mailing labels in a specific position on the page. This is done so that users can begin the print process on the first unused label. The frmClientLabelPosition form found in CHAP14.MDB is shown in Figure 14.4. It requests that the user specify the first label location on which to print by designating the number of labels that are needed to be skipped. The Open event of the lblClientMailLabelsSkip looks like this:
Private Sub Report_Open(Cancel As Integer)
If Not IsLoaded("frmClientLabelPosition") Then
MsgBox "You Must Run This Report From Label Criteria Form"
Cancel = True
Else
mfFirstLabel = True
End If
End Sub
Figure 14.4. The criteria selection used to indicate the number of labels to skip.
The code tests to ensure that the frmClientLabelPosition form is loaded. It also sets a private variable, mfFirstLabel, equal to True. The Detail section Print event looks like this:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount <= Forms!frmClientLabelPosition!txtLabelsToSkip _
And mfFirstLabel = True Then
Me.NextRecord = False
Me.PrintSection = False
Else
mfFirstLabel = False
End If
End Sub
This routine checks to see whether the PrintCount property of the report is less than or equal to the number of the labels to skip. It also checks to ensure that the mfFirstLabel variable is equal to True. If both these conditions are true, the report does not move to the next record and does not print anything. The print position is advanced. Once the PrintCount becomes greater than the number of labels to skip, the mfFirstLabel variable is set to False and printing proceeds as usual. If mfFirstLabel is not set to False, the designated number of labels are skipped between each record! One additional event makes all this work{md]the Print event of the Report Header:
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) mfFirstLabel = True End Sub
The ReportHeader Format event sets mfFirstLabel back to True. This is a necessary step in case the user previews and then prints the labels. If the mfFirstLabel variable is not reset to True, the selected number of labels is not skipped on the printout, because the condition that skips the labels is never met.
It is very difficult to base a report on the results of a crosstab query, because the number of columns in the crosstab query generally varies. Consider the example shown in Figure 14.5. Notice that the employee names appear across the top of the report as column headings, and the products appear down the side of the report. This report is based on the crosstab query called EmployeeSales (see Figure 14.6), which is part of the SOLUTIONS.MDB database that ships with Access. The problem is that the number of employees, and therefore column headings, can vary. This report is coded to handle such an eventuality.
Figure 14.5. A report based on a crosstab query.
Figure 14.6. A crosstab query underlying a report.
When the report runs, the Open event of the report is executed:
Private Sub Report_Open(Cancel As Integer)
' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.
Dim intX As Integer
Dim qdf As QueryDef
' Don't open report if EmployeeSalesDialogBox form isn't loaded.
If Not (IsLoaded("EmployeeSalesDialogBox")) Then
Cancel = True
MsgBox "To preview or print this report, you must open the Employee _
Sales Dialog Box in Form view.", 48, "Must Open Dialog Box"
Exit Sub
End If
' Set database variable to current database.
Set dbsReport = CurrentDb()
' Open QueryDef.
Set qdf = dbsReport.QueryDefs("EmployeeSales")
' Set parameters for query based on values entered in _
EmployeeSalesDialogBox form.
qdf.Parameters("Forms!EmployeeSalesDialogBox!BeginningDate") = _
Forms!EmployeeSalesDialogBox!BeginningDate
qdf.Parameters("Forms!EmployeeSalesDialogBox!EndingDate") = _
Forms!EmployeeSalesDialogBox!EndingDate
' Open Recordset.
Set rstReport = qdf.OpenRecordset()
' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
End Sub
The code first checks to ensure that the criteria form, EmployeeSalesDialogBox, is open. This form supplies the criteria for the EmployeeSales query that underlies the report. The Open event then sets a database object variable to the current database. It opens the EmployeeSales query definition and passes it the parameters from the EmployeeSalesDialogBox criteria form. Next, it opens a recordset based on the query definition, using the criteria found on the EmployeeSalesDialogBox form. The number of columns returned from the crosstab query is very important. It is stored in a Private variable called intColumnCount and is used throughout the remaining functions to determine how many columns to fill with data.
After the Open event, the Activate event occurs. This event displays the Custom Print Preview toolbar that appears whenever the report is the active object:
Private Sub Report_Activate()
' Used by Solutions to show toolbar that includes Show Me button.
' Hide built-in Print Preview toolbar.
' Show Custom Print Preview toolbar.
DoCmd.ShowToolbar "Print Preview", acToolbarNo
DoCmd.ShowToolbar "Custom Print Preview", acToolbarYes
End Sub
Next, the Report Header Format event occurs. It moves to the first record in the recordset created during the Open event. It also calls an InitVars routine:
Private Sub ReportHeader3_Format(Cancel As Integer, FormatCount As Integer)
' Move to first record in recordset at beginning of report
' or when report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst
'Initialize variables.
InitVars
End Sub
The InitVars routine initializes some variables used within the report:
Private Sub InitVars()
Dim intX As Integer
' Initialize lngReportTotal variable.
lngReportTotal = 0
' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX
End Sub
The lngReportToal variable is used for the report grand total (all products, all salespeople), and the lngRgColumnTotal array contains the total for each salesperson. After the Report Header Format event occurs, the Page Header Format event takes place:
Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)
Dim intX As Integer
' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format$(intX)) = rstReport(intX - 1).Name
Next intX
' Make next available text box Totals heading.
Me("Head" + Format$(intColumnCount + 1)) = "Totals"
' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format$(intX)).Visible = False
Next intX
End Sub
The PageHeader Format event has the important responsibility of using the names of the fields in the query results as column headings for the report. It is a smart routine because, after it fills in all the column headings, it hides all the extra controls on the report.
Next, the Detail Section Format event occurs:
Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
' Place values in text boxes and hide unused text boxes.
Dim intX As Integer
' Verify that not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, place values from recordset into text boxes
' in detail section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert null values to 0.
Me("Col" + Format$(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX
' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format$(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
End If
End Sub
The Detail Section Format event checks the EOF property of the recordset to determine whether the last record in the query has already been read. If not, the FormatCount property of the section is tested to see whether it is equal to 1. If so, each column in the current record of the recordset is read. Each control in the detail section is filled with data from a column in the recordset, and any unused text boxes in the report detail section are hidden. Finally, the code moves to the next record in the recordset, readying the report to print the next line of detail. The xtabCnulls function, which converts Null values into zeros, is called each time the recordset underlying the report is read:
Private Function xtabCnulls(varX As Variant)
' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If
End Function
The xtabCnulls function evaluates each value it is sent to determine whether the value is Null. If so, a zero is returned from the function. Otherwise, the value that the function was passed is returned.
After the Detail Section Format event is executed, the Detail Section Print event occurs:
Private Sub Detail1_Print(Cancel As Integer, PrintCount As Integer)
Dim intX As Integer
Dim lngRowTotal As Long
' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0
For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in detail section.
lngRowTotal = lngRowTotal + Me("Col" + Format$(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + _
Format$(intX))
Next intX
' Place row total in text box in detail section.
Me("Col" + Format$(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
End If
End Sub
The Detail Print event is responsible for generating the row total value, placing it in the last column of the report, accumulating column totals, and accumulating the lngReportTotal value, which is the grand total for all columns and rows. It accomplishes this by ensuring that the PrintCount of the section is 1. If so, it resets the lngRowTotal variable to 0. Starting at column 2 (column 1 contains the product name), it begins accumulating a row total. It does this by looking at each control in the row, adding its value to lngRowTotal. As it traverses each column in the row, it also adds the value in each column to the appropriate element of the private array called lngRgColumnTotal, which maintains all the column totals for the report. It prints the row total and adds the row total to the report's grand total.
When the Retreat event occurs, the following code executes:
Private Sub Detail1_Retreat()
' Always back up to previous record when detail section retreats.
rstReport.MovePrevious
End Sub
This code forces the record pointer to be moved back to the previous record in the recordset. Finally, the report footer prints. This causes the Report Footer Format event to execute:
Private Sub ReportFooter4_Print(Cancel As Integer, PrintCount As Integer)
Dim intX As Integer
' Place column totals in text boxes in report footer.
' Start at Column 2 (first text box with crosstab value).
For intX = 2 To intColumnCount
Me("Tot" + Format$(intX)) = lngRgColumnTotal(intX)
Next intX
' Place grand total in text box in report footer.
Me("Tot" + Format$(intColumnCount + 1)) = lngReportTotal
' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" + Format$(intX)).Visible = False
Next intX
End Sub
The Report Footer Format event loops through each control in the footer, populating each control with the appropriate element of the lngRgColumnTotal array. This provides the column totals for the report. Finally, the grand total is printed in the next available column. Any extra text boxes are hidden from display.
Another useful technique is printing the first and last entries from a page in the header of the report. This is illustrated in the CustomerPhoneList report, which is part of the SOLUTIONS.MDB database that ships with Access (see Figure 14.7). The code behind this report relies on the fact that Access makes two passes through the report. During the first pass, a variable called gLastPage is equal to False. The gLastPage variable becomes True only when the Format event of the report footer is executed at the end of the first pass through the report. This is an important fact to keep in mind as you review the code behind the report.
Figure 14.7. The first and last entry printed in the report header.
The first routine that affects the report processing is the Report Header Format event routine:
Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)
' During second pass, fill in FirstEntry and LastEntry text boxes.
If gLastPage = True Then
Reports!CustomerPhoneList!FirstEntry = _
Reports!CustomerPhoneList!CompanyName
Reports!CustomerPhoneList!LastEntry = _
gLast(Reports!CustomerPhoneList.Page)
End If
End Sub
The Page Header Format routine tests to see whether the gLastPage variable is equal to True. During the first pass through the report, the gLastPage variable is equal to False. During the second pass through the report, the FirstEntry and LastEntry text boxes (both of which appear in the header of the report) are populated with data. The FirstEntry text box is filled with the value within the CompanyName control of the current record (the first record on the page), and the LastEntry text box is populated with the appropriate element number from the CustomerPhoneList array. Each element of the CustomerPhoneList array is populated by the Format event of the Page Footer for that page during the first pass through the report.
Next, the Page Footer Format event is executed:
Private Sub PageFooter2_Format(Cancel As Integer, FormatCount As Integer)
' During first pass, increase size of array and enter last record on _
page into array.
If Not gLastPage Then
ReDim Preserve gLast(Reports!CustomerPhoneList.Page + 1)
gLast$(Reports!CustomerPhoneList.Page) = _
Reports!CustomerPhoneList!CompanyName
End If
End Sub
The Page Footer Format event determines whether the gLastPage variable is equal to False. If so (which it is during the first pass through the report) the gLast array is redimensioned to add an element. The value from CompanyName control of the last record on the page is stored in the new element of the gLast array. This value eventually appears in the page header of that page as the last company name that appears on the page. Finally, the Report Footer Format event executes:
Private Sub ReportFooter4_Format(Cancel As Integer, FormatCount As Integer)
Dim dbs As Database
Dim rst As Recordset
' Set flag after first pass has been completed.
gLastPage = True
' Open recordset for report.
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Customers")
' Move to last record in recordset.
rst.MoveLast
' Enter last record into array.
ReDim Preserve gLast(Reports!CustomerPhoneList.Page + 1)
gLast(Reports!CustomerPhoneList.Page) = rst!CompanyName
End Sub
The Report Footer routine sets the gLastPage variable equal to True. It opens a recordset based on the Customers table. This is the recordset on which the report is based. It moves to the last record in the recordset and adds the CompanyName value from the last record of the recordset in an additional element of the array.
Now the first pass of the report has completed. As the user moves to each page of the report during a print preview, or as each page is printed to the printer, the Format event executes for the Page Header. The company name from the first record on the page is placed in the FirstEntry control, and the appropriate element from the gLast array is placed in the LastEntry control.
By nature, crosstab queries are limited in that they do not enable you to place multiple rows of data in the result. For example, you cannot display months as column headings and then show the minimum, average, and maximum sales for each employee as row headings. The report shown in Figure 14.8 solves this problem.
Figure 14.8. Example of multifact crosstab treport.
Each time the Format event of the Page Header executes, the value of a control on the report, called PrintWhat, is reset to 0:
Private Sub PageHeader1_Format(Cancel As Integer, FormatCount As Integer)
' Set PrintWhat text box to 0 at top of page.
Me!PrintWhat = 0
End Sub
After the Page Header Format event executes, the Group Header Format event launches:
Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As Integer)
' Print information in row headings in correct order.
' Print SalespersonLastName and FirstName text boxes,
' hide Minimum, Average, and Maximum labels,
' set PrintWhat text box to -1, and don't advance to next record.
If Me!PrintWhat = 0 Then
Me!SalespersonLastName.Visible = True
Me!FirstName.Visible = True
Me!LMinimum.Visible = False
Me!LAverage.Visible = False
Me!LMaximum.Visible = False
Me!PrintWhat = -1
Me.NextRecord = False
' Hide SalespersonLastName and FirstName text boxes,
' print Minimum, Average, and Maximum labels,
' and set PrintWhat text box to 0.
Else
Me!SalespersonLastName.Visible = False
Me!FirstName.Visible = False
Me!LMinimum.Visible = True
Me!LAverage.Visible = True
Me!LMaximum.Visible = True
Me!PrintWhat = 0
End If
End Sub
The first time the Format event for LastName Group Header (GroupHeader2) executes, the value of the PrintWhat is equal to 0. The SalePersonLastName and the FirstName controls are made visible. The LMinimum, LAverage, and LMaximum controls are hidden. The value of the PrintWhat control is set to -1, and movement to the next record is suppressed by setting the value of the NextRecord property to 0.
The second time the Format event for the LastName Group Header executes, the SalepersonLastName and FirstName controls are hidden. The LMinimum, LAverage, and LMaximum controls are made visible, and the value of the PrintWhat control is reset to 0.
The only other code for the report is found in the Format event of the Shipped Date Header (GroupHeader3):
Private Sub GroupHeader3_Format(Cancel As Integer, FormatCount As Integer)
' Print data in correct column.
' Don't advance to next record or print next section.
If Me.Left < Me!LeftMargin + (Month(Me!ShippedDate) + 1) * _
Me!ColumnWidth Then
Me.NextRecord = False
Me.PrintSection = False
End If
End Sub
This code compares the Left property of the report to the result of an expression. The Left property of the report is the amount the current section is offset from the left edge of the page. This number is compared with the value contained within the LeftMargin control added to the current month plus one, then multiplied by the value within the ColumnWidth control. If this expression evaluates to True, the NextRecord and PrintSection properties of the report are both set to False. This causes the printer to move to the next printing position, but to remain on the same record and not print anything. This forces a blank space to appear in the report. You might wonder what the complicated expression is all about. Simply put, it is an algorithm that ensures that printing occurs and that Access moves to the next record only when the data for the month of January is ready to print.
Almost every report contained within the Time and Billing application implements at least one of the techniques discussed in this chapter. In fact, the rptClientListing, rptClientBillingsByProject, lblClientMailingLabels, and lblClientMailingLabelsSkip reports are all discussed in this chapter. They are an integral part of the Time and Billing application.
One report not covered in the chapter is the rptEmployeeBillingsByProject report. This report contains the following code in its NoData event:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "There is no data for this report. Canceling report..."
Cancel = -1
End Sub
If no data is found within the report's RecordSource, a message box is displayed and the report is canceled. The Open event of the report looks like this:
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmReportDateRange", , , , , acDialog, _
"Employee Billings by Project"
If Not IsLoaded("frmReportDateRange") Then
Cancel = True
End If
End Sub
The Open event of the report opens a form called frmReportDateRange (see Figure 14.9). This form is required because it supplies criteria to the query underlying the report. If the form is not loaded successfully, the report is canceled.
Figure 14.9. Criteria selection form.
Finally, the Close event of the report looks like this:
Private Sub Report_Close()
DoCmd.Close acForm, "frmReportDateRange"
End Sub
The report cleans up after itself by closing the criteria form.
In order to take full advantage of what the Access report writer has to offer, you must understand—and be able to work with—report and section events. This chapter went through the report and section events, providing detailed examples of when to employ each event.
In addition to the report events, there are several special properties that are available to you only at runtime. By manipulating these properties, you can greatly control the behavior of your reports. After covering the report and section events, the chapter covered the properties that you can manipulate only at runtime. Examples were provided to highlight the appropriate use of each property.
There are many tips and tricks of the trade that help you to accomplish things that you might otherwise think are impossible to accomplish. This chapter concluded by providing several practical examples of these tips and tricks, making it easy for you to employ the tips and tricks in your own application development.