Access Cookbook - Ken Getz [70]
Figure 3-20. rptCriteria1 prints a page of errors when no records are selected
Now open the frmcriteria2 pop-up criteria form. This form is identical to the first, except that the event procedure attached to its command button runs the rptSelect2 report instead. If you accept the default values, the rptSelect2 report will attempt to run, again with no records. But this version of the report has an event procedure attached to its OnNoData event that suppresses printing and instead displays the message box shown in Figure 3-21.
Figure 3-21. rptCriteria2 displays this message and cancels printing when there are no records
Discussion
The OnNoData event is triggered whenever a report attempts to print with no records. If you attach an event procedure to the OnNoData event, your code will run whenever the report prints without any records. While the MsgBox statement informs the user what has happened, the key line of code is:
Cancel = True
This line tells Access to cancel printing of the report (by setting the passed Cancel argument to True).
If you use VBA code to open a report that has no data and allow the report's OnNoData event to cancel the report, you will get an error in the code that attempted to open the report. So, in this solution, you'll find error-handling code in the button-click event that opens the report in frmCriteria2. When an error occurs, the code checks whether it's the expected error, which has a number of 2501. If so, it ignores the error. Here's the code behind the cmdPrint button:
Private Sub cmdPrint_Click( )
On Error GoTo HandleErr
Me.Visible = False
DoCmd.OpenReport "rptSelect2", acPreview
ExitHere:
DoCmd.Close acForm, Me.Name
Exit Sub
HandleErr:
Select Case Err.Number
Case 2501
' The OpenReport action was canceled.
' There were no rows. So do nothing.
Case Else
MsgBox Err.Number & ": " & Err.Description
End Select
Resume ExitHere
End Sub
The report header contains controls to display the selection criteria, which are picked up from the criteria form, using expressions like this one:
=[Forms]![frmCriteria1]![txtLastOrderAfter]
The form disappears from view when the report opens in print preview mode because the event procedure attached to the traffic-light button sets the form's Visible property to False before opening the report. Making the form invisible (rather than closing it) ensures that the selection criteria are still available for the report's data source.
See Also
For more information on printing query criteria on reports, see the Solution in Recipe 3.2.
3.10. Print Different Headers or Footers on Odd and Even Pages
Problem
Some of your reports are printed double-sided, and you would like to have mirror-image headers and footers on odd and even pages. How do you do this in Access?
Solution
This technique makes use of two sets of header and footer controls, one for odd pages and one for even pages. An event procedure run from the section's Format event uses the Page property and the Mod operator to determine whether the page is odd or even and makes the appropriate controls visible or invisible.
The following steps show you how to create your own report that prints different headers and footers on odd and even pages:
Open the report you want to print double-sided (or even single-sided, with different odd and even headers and footers).
Make a copy of the header control, and place one of the copies of the control on the left of the header and the other on the right. Make the lefthand control left-aligned (to print on even-numbered pages) and the righthand control right-aligned (to print on odd-numbered pages).
Create an event procedure attached to the OnFormat property of the report's page header section. In