Access Cookbook - Ken Getz [69]
Public Function acbMakeLine(varValue as Variant)
If IsNull(varValue) Then
acbMakeLine = Null
Else
acbMakeLine = varValue & vbCrLf
End If
End Function
acbMakeLine uses the built-in vbCrLf constant, which is equivalent to typing Chr$(13) & Chr$(10).
If you use a concatenated expression for an address, you can accommodate more fields on a label than you could if you placed each address text box on a separate line. This method works fine as long as you know that each address will be missing at least one row of address data. If your labels have room for only four lines of data, for example, you could put five lines of data into a concatenated expression if you know that no address will use all five lines.
Unlike specialized label-printing programs, Access does not lock the report size to the label's dimensions to prevent you from accidentally changing the sizes of labels after you have created them with the Mailing Label Report Wizard. It is very easy to accidentally nudge the right edge or bottom edge of a mailing-label report (by moving a control, for example) so that the report contents overprint the labels.
We could have used a series of IIf functions here instead of using the acbMakeLine function, but using acbMakeLine is simpler and less confusing.
Another approach would be to take further advantage of the fact that the + operator propagates nulls—a feature we're already using to avoid printing commas after blank cities or extra spaces after blank states. For example, the following expression will eliminate extra lines, because everything inside a set of parentheses that includes a null value will be converted to Null:
([Address1]+Chr$(13)+Chr(10)) & ([Address2]+Chr$(13)+Chr(10)) _
& ([POBox]+Chr$(13)+Chr(10)) & (([City]+", ") & ([StateProvince]+" ") _
& [ZipPostalCode] +Chr$(13)+Chr(10)) & ([Country])
TIP
When you first create a mailing-label report, write down its width and detail section height so that you can quickly recover from any accidental resizing of the report, which could result in label text printing outside of the label's boundaries.
3.9. Suppress Printing a Report if There Are No Records to Print
Problem
You have a report that prints records you select from a criteria form. Sometimes there aren't any records that match the criteria and the report opens with #Error in the detail section, which is unattractive and confusing. Is there any way you can prevent the report from printing when it has no records to print?
Solution
Access includes an event, OnNoData, that fires when no records are present in the report's underlying recordset. This solution shows you how to use this new event to suppress printing of the report when no records match the specified criteria.
To create a report that suppresses printing when there are no records, follow these steps:
Create a new report or open an existing report in design view.
Create an event procedure attached to the report's OnNoData property. (If you're unsure of how to do this, see How Do I Create an Event Procedure? in the the preface of this book.) Enter the following VBA code in the event procedure:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "Sorry, no records match these criteria!", _
vbExclamation, "No Records to Print"
Cancel = True
End Sub
Save and run the report. If you enter criteria that do not match any records, you will get a message box telling you that no records meet the criteria (like the one shown in Figure 3-21).
The following example demonstrates this solution. Load the 03-09.MDB database. Open the frmCriteria1 pop-up criteria form. This form allows you to enter criteria for the rptSelect1 report (see Figure 3-19).
Figure 3-19. The frmCriteria1 pop-up criteria form with default values
When you press the traffic-light button, a simple event procedure will execute that opens the report in print preview