Access Cookbook

Programmatically Change Margin and Column Settings for Reports

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?

Solution

Access 2002 provides each form and report object with 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 ...