You are currently offline, waiting for your internet to reconnect

How to eliminate white space in reports with CanShrink and code in Access 2002

This article was previously published under Q299011
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Access 97 version of this article, see 95390.
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

For a Microsoft Access 2000 version of this article, see 210589.
Before you print a form or a report, you can save space by conditionally reducing the size 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 property.
  • Controls located in the page header or page footer can shrink and grow. 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).
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
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 be printed 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 controls 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 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.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Create a new module and type the following line in the Declarations section if it is not already there:
    Option Explicit						
  2. 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 FunctionFunction ISB (V) As Integer   If IsNull(V) or V = "" Then ISB = True Else ISB = FalseEnd Function						
  3. To test this function, create a blank report based on the Employees table in Northwind.mdb. Set the Height property of the detail section to 0.1667 inches.
  4. Create a single text box control with the CanGrow property set to Yes, the Height property set to 0.1667", and delete the associated label. Set the ControlSource property of the text box to the following expression:
When you print the report, it will automatically run the function, and keep unnecessary space to a minimum.
For more information about the CanShrink or CanGrow property, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type canshrink, cangrow in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
shrinking functions inf

Article ID: 299011 - Last Review: 12/06/2015 02:44:49 - Revision: 5.0

Microsoft Access 2002 Standard Edition

  • kbnosurvey kbarchive kbhowto kbprogramming kbdta kbreport kbofficeprog KB299011