ACC: Eliminating White Space in Reports with CanShrink and Code

ID: Q95390

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

Before you print a form or a report, you can save space by conditionally reducing the sizes of the controls on the form or report. For example, you might want to reduce the size of text boxes that contain varied-length text or reduce the size of text boxes bound to fields that could be empty.

Before you try to reduce the size of controls in reports, however, consider the following:

Overlapping controls will not shrink, even when the CanShrink property is set to Yes.

Controls shrink line by line (vertically). This means, for example, that if there is a group of controls placed on the left side of the page and a large control (for example, an OLE picture) on the right side of the page, the controls on the left side of the page will not shrink, unless the picture is blank and hidden.

Space between controls is not affected by the CanShrink or CanGrow properties.

Controls located in the Page Header or Page Footer will not shrink or grow in Microsoft Access version 1.x.

Controls located in the Page Header or Page Footer can shrink and grow in Microsoft Access 2.0, 7.0, and 97. However, the sections do not have CanGrow and CanShrink properties. This means, you can still have blank space in a Page Header or Page Footer section (even though its controls may shrink).

Text and memo fields can contain hidden characters or line feeds following the data. This can add to the overall size of the control on the report and give the appearance that the "Can Shrink" property is not set.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0

NOTE: This article explains a technique demonstrated in the sample files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0) and RptSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

Q145777 ACC95: Microsoft Access Sample Reports Available in Download Center

Q175072 ACC97: Microsoft Access 97 Sample Reports Available in Download Center

MORE INFORMATION

A control containing null information automatically shrinks to nothing and disappears when its CanShrink property is set to Yes, but the space between the controls is not affected by the CanShrink property.

For example, say you have 11 controls in a column of a report, and there is .1 inch of space between each control. Even if all 11 controls contain null information and all 11 have their CanShrink property set to Yes, the space between the controls adds up to 1 inch. When you print the report, even though the 11 controls are not printed, the first control that does contain text will print 1 inch lower than you might expect because of all the accumulated space.

A good way to manage the size of your controls is to have as few as possible. This will minimize the white space between the controls.

The following example demonstrates how to print mailing labels by using a Visual Basic function to minimize the number of controls and to give you nice, even spacing. Say you want to create mailing labels containing a name, business name, address, city, state, and zip code. You could use several different fields, but it would be better to use a single text box, as in the following step-by-step example:

Create a new module and type the following line in the declarations section if the line is not already there:

Option Explicit

Type the following procedures:


      Function AddressBlock$ (AName, Addr1, Addr2, City, State, Zip)
         Dim A1$, A2$, A3$, A4$, CR$

         CR$ = Chr(13) & Chr(10)  'Carriage return and line feed.

         A1$ = IIf(ISB(AName),"",AName & CR$)
         A2$ = IIf(ISB(Addr1),"",Addr1 & CR$)
         A3$ = IIf(ISB(Addr2),"",Addr2 & CR$)
         A4$ = City & ", " & State & " " & Zip

         AddressBlock = A1$ & A2$ & A3$ & A4$   'Concatenate the strings.
      End Function

      Function ISB (V) As Integer
         If IsNull(V) or V = "" Then ISB = True Else ISB = False
      End Function

To test this function, create a blank report based on the Employees table in the Northwind.mdb (or NWIND.MDB in versions 1.x and 2.0). Set the detail section Height property to 0.1667 inches.

Create a single text box control with the CanGrow property set to Yes, the Height property set to the 0.1667", and delete the associated label. Set its ControlSource property to the following expression:

NOTE: In the following example, an underscore(_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this example.
=AddressBlock([LastName],[Title],[Address],[City],[Region],_ [PostalCode])
NOTE: In Microsoft Access 2.0 and earlier there are spaces in the [Last Name] and [Postal Code] fields.

Now you can print your report, and the report will automatically run the function and keep wasted space to a minimum.

REFERENCES

For more information about the CanShrink or CanGrow property, search the Help Index for CanShrink or CanGrow, or ask the Microsoft Access 97 Office Assistant.

Additional query words: shrinking functions

Keywords : kbusage kbdta AccCon RptLayou
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto