Recipe 5.3 Programmatically Change Margin and Column Settings for Reports

5.3.1 Problem

You'd like to give your applications' users some control over report layout, especially in designating column and margin settings. You could just let them loose in report design mode, but you'd like to maintain a little control over their actions. Is there some way to modify these layout settings from VBA?

5.3.2 Solution

Starting with Access 2002, each form and report object includes a Printer property. Retrieving this property gets you an object with many properties, several of which deal with margin and column settings for reports.

You can use properties of a report's Printer object to retrieve and set layout properties. You'll find properties representing the left, top, bottom, and right margins; the number of columns; and the size, spacing, and item order of the columns. In addition, the Printer object contains the Data Only option in the File Page Setup dialog. This solution demonstrates how to use the print layout properties provided by the Printer object.

Load and run the form frmPrintSettings from 05-03.MDB. Figure 5-3 shows the form (which emulates Access's File Page Setup dialog) after the report Report1 has been selected from the list of reports. Choose a report from the drop-down list, and the form will load that report in preview mode. You can change the settings for the selected report by typing new values into the text boxes. To save the changes to the selected report, click on Save Settings. You'll see those changes in the preview window.

Figure 5-3. frmPrintSettings provides the same functionality as the Access File Page Setup dialog

Some of the items on the form are available only if you've specified more than one column for the Items Across value, so you'll want to use a number greater than 1 in that field. (Because this example opens the report in preview mode, changes you make aren't saved with the report. If you want to permanently save the report with the new settings, you'll need to modify the code so that the report opens in design view; changes you make will then be saved with the report when you close and save it.) The following sections explain both how to use the sample form from 05-03.MDB and how to work with these properties and your own objects.

To use the sample form in your own applications, follow these steps:

5.3.3 Discussion

The sample form for this topic does very little work—it simply copies values from the Printer property of the selected report to controls on the form, converting from twips (1/1,440 inch) to inches for display purposes. When you click Save Settings, the code writes the settings back to the appropriate properties of the report's Printer property.

Access's Printer object provides a number of properties dealing with print layout. Table 5-1 describes the subset of Printer object properties used in this example form.

Table 5-1. Printer object properties associated with print layout

Property	Comments
LeftMargin	Distance between the left edge of the paper and the object to be printed (in twips)
TopMargin	Distance between the top edge of the paper and the object to be printed (in twips)
RightMargin	Distance between the right edge of the paper and the object to be printed (in twips)
BottomMargin	Distance between the bottom edge of the paper and the object to be printed (in twips)
DataOnly	If True (-1), Access prints just data, not labels, control borders, gridlines, and display graphics; if False (0), Access prints all elements
ColumnSpacing	Distance between detail section columns (if ItemsAcross > 1), in twips
DefaultSize	If True (-1), Access uses the width and height of the design-mode detail section when printing; if False (0), Access uses the values specified in the ItemSizeWidth and ItemSizeHeight properties
ItemLayout	acPRHorizontalColumnLayout (Across, then Down), or acPRVerticalColumnLayout (Down, then Across) for multiple-columned reports
ItemSizeWidth	Width of the detail section; if the DefaultSize property is False and the ItemsAcross property is greater than 1, the width of each column (in twips)
ItemSizeHeight	Height of the detail section (read-only)
ItemsAcross	Integer that specifies the number of columns across the page for multiple-columned reports
RowSpacing	Vertical distance between detail sections (in twips)

After you select a report on the sample form, the combo box's AfterUpdate event procedure calls the following code, which opens the report in preview mode, then copies the report's properties to the controls on the form:

strReport = Me.cboReportList
DoCmd.OpenReport strReport, View:=acViewPreview
With Reports(strReport).Printer
    Me.txtLeft = ToInches(.LeftMargin)
    Me.txtRight = ToInches(.RightMargin)
    Me.txtTop = ToInches(.TopMargin)
    Me.txtBottom = ToInches(.BottomMargin)
    Me.chkDataOnly = .DataOnly
    Me.txtXFormSize = ToInches(.ItemSizeWidth)
    Me.txtYFormSize = ToInches(.ItemSizeHeight)
    Me.txtCxColumns = .ItemsAcross
    Me.txtYFormSpacing = ToInches(.ItemSizeHeight)
    Me.chkfDefaultSize = .DefaultSize
    Me.txtXFormSpacing = ToInches(.ColumnSpacing)
    Me.grpRadItemOrder = .ItemLayout
End With

Don't forget that all the measurements in the Printer object are stored in twips. The ToInches function simply divides its parameter value by 1,440 and adds the text "in." to its output value. The corresponding FromInches function does the opposite—it strips off extra text and multiplies its parameter value by 1,440 to convert back to twips. Why 1,440? A twip is defined as 1/20 of a point. There are 72 points per inch and 20 twips per point; therefore, 72 x 20 = 1,440 twips per inch.

When you click Save Settings, the command button's Click event procedure copies data back to the properties of the report, like this:

Dim strChosen As String
Dim rpt As Report

strChosen = Me.cboReportList
With Reports(strChosen).Printer
    .LeftMargin = FromInches(Me.txtLeft)
    .RightMargin = FromInches(Me.txtRight)
    .TopMargin = FromInches(Me.txtTop)
    .BottomMargin = FromInches(Me.txtBottom)
    .DataOnly = Me.chkDataOnly
    .DefaultSize = Me.chkDefaultSize
    If Not .DefaultSize Then
        .ItemSizeWidth = FromInches(Me.txtXFormSize)
        .ItemSizeHeight = FromInches(Me.txtYFormSize)
    End If
    .ItemsAcross = Val(Me.txtCxColumns)
    .RowSpacing = FromInches(Me.txtYFormSpacing)
    
        .ColumnSpacing = FromInches(Me.txtXFormSpacing)
    .ItemLayout = Me.grpRadItemOrder
End With

The combo box containing the list of reports uses a common but undocumented technique. The Access system tables (check Tools Options View System Objects to see the system tables in the database container) contain information about the current database. One table in particular, MSysObjects, contains a row for each object in the database. To fill the combo box with a list of reports, you can use this SQL expression:

SELECT Name FROM MSysObjects WHERE Type = -32764 ORDER BY Name;

The Name column includes the name for each object, and the Type column contains -32764 for reports (or -32768 for forms). Microsoft suggests using DAO or ADO instead of querying against the system tables to retrieve lists of items; however, our method is much faster and much simpler for filling lists. This method has worked in every version of Access so far; we can only assume it will continue to do so.

The Printer object provides one more bit of unexpected behavior: unless you've set the DefaultSize property to False, you cannot set the ItemSizeWidth or ItemSizeHeight properties—you'll trigger a runtime error if you try. The sample code determines the value in the DefaultSize property and attempts to change the other two properties only if doing so won't cause an error.