Access Cookbook, 2nd Edition [Electronic resources]

Recipe 5.8 Find Which Reports Are Not Set to Print to the Default Printer

5.8.1 Problem

You are about to distribute your application to other Access users. You want to ensure that all your reports are set to print to the default printer so that they will work with the users' installations of Windows. How do you create a list of all your reports and show whether or not they have been saved with the default printer setting?

5.8.2 Solution

Building on the code examples in this chapter, you can investigate the UseDefaultPrinter property of each report to determine if it has the default printer selected. This solution uses this property, along with some simple ActiveX Data Objects (ADO) code, to get a list of reports in your database, to check the default printer setting, and to save the results to a table. This table feeds a report that you can print, rptReportPrinters. Once you have this list, you can set the output device for each report that has been set to print to a specific printer rather than to the Windows default printer.

Open and run frmShowReports from

05-08.MDB . Figure 5-8 shows the form once it's done all its calculations. It will show the name of every report in your database, along with the default printer setting for each.

You can obtain a printout of this information by pressing the Print Analysis button, which prints the rptReportPrinters report (Figure 5-9).

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

1. Import the objects listed in Table 5-5 from

   05-08.MDB .

2. Once you've imported the objects, open the form frmShowReports to create the list of reports in your application, along with their output status.

5.8.3 Discussion

To see how this technique works, open the frmShowReports form in design view, then open the form's module window and locate the Form_Load event procedure. This subroutine calls the

GetReports subroutine, which does most of the actual work. Iterating through the AllReports collection of the CurrentProject object gives

GetReports access to each report in your database:

Private Sub GetReports( )
' Get a list of reports from the current database and write the name,
' along with the default printer status, to the output table.
Dim rst As ADODB.Recordset
Dim doc As AccessObject
On Error GoTo HandleErrors
Call EmptyTable("tblReportPrinters")
Set rst = New ADODB.Recordset
rst.Open "tblReportPrinters", CurrentProject.Connection, _
adOpenDynamic, adLockOptimistic
' Loop through all the reports in the container's documents 
' collection, opening each report in turn and checking
' to see if that report is formatted to send its output
' to the default printer.
With rst
For Each doc In CurrentProject.AllReports
DoCmd.OpenReport doc.Name, View:=acViewDesign, WindowMode:=acHidden
.AddNew
.Fields("ReportName") = doc.Name
.Fields("DefaultPrinter") = Reports(doc.Name).UseDefaultPrinter
.Update
DoCmd.Close acReport, doc.Name
Next doc
End With
ExitHere:
On Error Resume Next
rst.Close
Exit Sub
HandleErrors:
Resume ExitHere
End Sub

This code needs to empty the tblReportPrinters table. It uses the following procedure to clear the data from the table:

Private Sub EmptyTable(strTable As String)
' Remove all the rows from the table whose name is in strTable.
With DoCmd
.SetWarnings False
.RunSQL "DELETE * FROM " & strTable
.SetWarnings True
End With
End Sub

This procedure uses a simple SQL DELETE statement to delete all the rows from the table, first turning off Access's warnings and then turning them back on once it's done.

GetReports uses ADO to write information about each report into the tblReportPrinters table. See Chapter 6 for more information on working with data programmatically.