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.

Figure 5-8. The frmShowReports example form

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

Figure 5-9. The Show Report Printers example report

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

Table 5-5. Objects to import from 05-08.MDB, allowing the creation of output status report

Object type	Object name
Table	tblReportPrinters
Form	frmShowReports
Report	rptReportPrinters

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.