Recipe 3.8 Avoid Unwanted Blank Rows on Mailing Labels

3.8.1 Problem

When you print mailing labels, especially when you use a small font size and place the address text boxes close together, you sometimes get unwanted blank rows in the addresses when the labels print. You also can't seem to use lines or graphics on your labels without causing blank rows. How can you get your labels to print correctly—without blank rows—in these situations?

3.8.2 Solution

The CanGrow and CanShrink text box properties for reports allow text boxes to grow or shrink vertically as needed. These properties normally work well, but sometimes overlapping text boxes or graphics can interfere with text boxes' ability to shrink or grow. This solution shows how you can avoid these problems by combining the output of several fields into a single expression and using that expression as the row source of a single text box.

Open the tblCompanyAddresses table from 03-08.MDB in datasheet view. You can see that this table contains typical address data, with three address fields (Address1, Address2, and PO Box). Some of the sample records have blanks in at least one of these address fields.

Close the table and open the rptLabels report in preview mode. This is a typical mailing-label report, as might have been produced by the Mailing Label Report Wizard. Notice that there are no blank rows in the addresses. Now open the rptLabelsWithImageBroken report in preview view (see Figure 3-16). We added to the left side of each label an Image control that causes unwanted blank lines. Finally, open the rptLabelsWithImageFixed report in preview view (see Figure 3-17). Notice that this version of the report doesn't have any unwanted blank lines, even though the same image appears on the left side of each label.

Figure 3-16. rptLabelsWithImageBroken prints labels with unwanted blank rows

Figure 3-17. A modified version of the report, rptLabelsWithImageFixed, prints fine

Follow these steps to create a mailing-label report, complete with a graphic on each label but without any unwanted blank lines:

Create a new mailing-label report. The easiest way to do this is to use the Mailing Label Report Wizard. The rptLabels sample report was created using this wizard. Its record source is tblCompanyAddresses.
Add a line, unbound object frame, or Image control to the label. In the sample database, rptLabelsWithImageBroken includes an Image control containing a gray triangle (a Paintbrush image) to the left of the addresses. Here, the Image control prevents the text boxes' CanShrink property from working, resulting in numerous blank rows in the addresses (see Figure 3-16).
Import the basCrLf module from 03-08.MDB into your database.
Delete the multiple address-line controls (five in rptLabelsWithImageBroken) and replace them with a single text box that concatenates each of the address lines together. For each text box that may be missing data, create an expression to wrap the field in the acbMakeLine function (discussed in Section 3.8.3). The final control-source expression should look something like the control source for the txtWholeAddress control in rptLabelsWithImageFixed, which is shown here:
```
=acbMakeLine([Address1]) & acbMakeLine([Address2]) & acbMakeLine([POBox]) & 
acbMakeLine(([City]+", ") & ([StateProvince]+"  ") & [ZipPostalCode]) & 
acbMakeLine([Country])
```

Press Shift+F2 when your cursor is in the text box's ControlSource property (or any other property) to open up the Zoom box, which lets you see the whole expression as you work with it.

Save the report and run it to make sure it produces the desired output (like that shown in Figure 3-17). The completed report is shown in design view in Figure 3-18.

Figure 3-18. rptLabelsWithImageFixed in design view

3.8.3 Discussion

When you combine several address fields into a single expression and use that expression as the row source of a single text box, you have only one text box to grow or shrink as needed. The elimination of multiple text boxes prevents problems with CanShrink/CanGrow that occur when a text box that needs to shrink is placed on the same row as a text box or other control (such as an Image control) that can't shrink.

We used the acbMakeLine function to check for nulls in a text field and return a null value for the line if the varValue argument is Null; otherwise, acbMakeLine adds carriage-return and line-feed characters after the field. Thus, a new line is created only if the address line is non-null, giving us essentially the same effect as using the CanShrink property. The acbMakeLine function is shown here:

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])

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.

[ Team LiB ]