Show All

Hide All

You can set page-setup options for each report in a database, but the settings are not stored for each user of the database. In other words, if you make changes to the print settings of the Invoice report, your changes will overwrite the changes made by the previous user.

By writing code, you can enable users to specify custom settings at run time, without modifying the report's default printer or page-setup options. You also need to provide users with a dialog box or a form where they can specify the settings they want.

The following illustration shows the Printer Settings form, which allows the user to customize print settings, and preview or print a report.

Select a report that you want to either preview or print.

Select the printer that you want to use for the selected report.

Specify the paper size and orientation.

Click to preview the report.

Click to apply the latest print settings to a report that is already open in the Preview window.

Click to print the report.

Create the Printer Settings form

1. Create a form in Design view, and add the following controls as shown in the above illustration.
   • lbxSelectReport: list box to display the list of reports
   • cmbPrinter: combo box to display the list of available printers
   • cmbPaperSize: combo box to display the list of paper sizes
   • opgOrientation: option group with two options to display the orientation options
   • cmdPreview: command button to open a report in preview mode
   • cmdApplyChanges: command button to apply the latest settings to the current report
   • cmdPrint: command button to print the selected report using the settings specified in the form
2. Add code to the form's Open event to initialize the controls.

   Sample Open event subprocedure

   Private Sub Form_Open(Cancel As Integer)
   
   ' Variable to hold the default printer index.
   Dim strDefaultPrinter As String
   
   ' Variable to hold the printer object.
   Dim prt As Printer
   
   ' Variable to hold the report object while cycling
   ' through the AllReports collection.
   Dim accObj As AccessObject
   
   ' Fill the printer list.
   ' Make sure the RowSource is empty.
   Me!cmbPrinter.RowSource = ""
   Me!lbxSelectReport.RowSource = ""
   
   ' Cycle through the printers installed on the machine and add them to the combo box.
   For Each prt In Application.Printers
   ' Use the new AddItem method to add the printer name to the combo box.
   Me!cmbPrinter.AddItem prt.DeviceName
   Next
   
   ' Remember the default printer.
   strDefaultPrinter = Application.Printer.DeviceName
   
   ' Set the combo box to the default printer.
   Me!cmbPrinter = strDefaultPrinter
   Me!cmbPaperSize = 1
   
   ' Fill the report list.
   For Each accObj In CurrentProject.AllReports
   Me!lbxSelectReport.AddItem accObj.Name
   Next
   
   ' Set the list box to the first report.
   Me!lbxSelectReport.SetFocus
   Me!lbxSelectReport.ListIndex = 0
   
   End Sub
   

3. Add code to the Preview Report command button to open the selected report in preview mode.

   Sample OnClick event procedure for the Preview Report command button

   Private Sub cmdPreview_Click()
   ' Variable to hold the printer object to manipulate.
   Dim prt As Printer
   
   ' Grab the printer object for the selected printer.
   Set prt = Application.Printers(Me!cmbPrinter.Value)
   
   ' Read user-specified settings.
   prt.PaperSize = Me!cmbPaperSize
   prt.Orientation = Me!opgOrientation
   
   ' Open the report in preview mode.
   DoCmd.OpenReport Me!lbxSelectReport, acViewPreview
   
   ' Set the report's printer to the modified printer object.
   Reports(Me!lbxSelectReport).Printer = prt
   
   End Sub
   

4. Add code to the Apply Changes command button to apply changes to the report that is already open in preview mode.

   Sample OnClick event procedure for the Apply Changes command button

   Private Sub cmdApplyChanges_Click()
   
   If CurrentProject.AllReports(Me!lbxSelectReport).IsLoaded Then
       With Reports(Me!lbxSelectReport).Printer
       .PaperSize = Me!cmbPaperSize
       .Orientation = Me!opgOrientation
       End With
   Else
       MsgBox "Please preview the report first."
   End If
   
   End Sub
   

5. Add code to the Print Report command button to print the selected report based on the settings specified by the user.

   Sample OnClick event procedure for the Print Report command button

   Private Sub cmdPrint_Click()
   
   ' Check if the report is open.
   If CurrentProject.AllReports(Me!lbxSelectReport).IsLoaded Then
       ' Print report if already open.
       DoCmd.OpenReport Me!lbxSelectReport, acViewNormal
   Else
       ' Set up the application printer with custom settings.
       Application.Printer = Application.Printers(Me!cmbPrinter.Value)
       Application.Printer.PaperSize = Me!cmbPaperSize
       Application.Printer.Orientation = Me!opgOrientation
   
       ' Open and print the report using the new application-level printer settings.
       DoCmd.OpenReport Me!lbxSelectReport, acViewNormal
   
       ' Reset the application printer as the default.
       Set Application.Printer = Nothing
   End If
   
   End Sub
   

For more information about programming in Access, visit the Office Developer Center on the Microsoft Developer Network (MSDN).