Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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 correctlywithout blank
rowsin 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:

1. 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.

2. 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).

3. Import the basCrLf module from

   03-08.MDB into
   your database.

4. 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.

5. 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
nullsa 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.