|[ Team LiB ]|
Recipe 5.3 Programmatically Change Margin and Column Settings for Reports
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?
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.
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:
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.
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.
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.
|[ Team LiB ]|