Access Cookbook - Ken Getz [120]
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:
Import the objects listed in Table 5-5 from 05-08.MDB.
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
Once you've imported the objects, open the form frmShowReports to create the list of reports in your application, along with their output status.
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.
Chapter 6. Data
The point of a database program is to manage data. Although Access provides most of the tools you'll need, there are many tasks for which you have to roll your own solution. This chapter concentrates on working with data in ways that traditional database operations don't support. You'll learn how to search for records phonetically, back up your database objects, perform lightning-fast finds on linked tables, save housekeeping information, and more. All the examples in this chapter use some form of Visual Basic for Applications (VBA) code, but don't worry—they are all clearly explained, and "testbed" applications are supplied to show you how each technique works. We present more tips for working with data in Chapter 14, focusing on techniques you can use when your data is stored in SQL Server, rather than in an Access Jet database (an .MDB or .MDE file).
WARNING
Many