Printing

Introduction to Printing

Printing a Database Object

You can print one, a few, or all records from a database object. To do this, you can call the RunCommand() method of the DoCmd object. Before calling it, you must indicate what object you want to print. For example, if you want to print a table, you must first select it. We know that this can be done by calling the SelectObject() method of the DoCmd object. Here is an example:

Private Sub cmdPrintTable_Click()
    DoCmd.SelectObject acTable, "Employees", True
    DoCmd.RunCommand acCmdPrint
End Sub

You can print a form in the exact same way by simply changing the second argument of the DoCmd.SelectObject(). Here is an example:

Private Sub cmdPrintUndergraduateForm_Click()
    DoCmd.SelectObject acForm, "Customers", True
    DoCmd.RunCommand acCmdPrint
End Sub

The Print Dialog Box

In most cases, when the user wants to print something, he or she must display the Print dialog box, which is from the operating system. To represent a printer, Microsoft Access has an object (or class) named Printer, which is static, which means the object is available as soon as you start the application and you don't have to "create" that object. In reality, whenever you start Microsoft Access, if a printer is connected to the computer, that printer is readily available. This means that you don't have to write formal code to "load" or access the printer. On the other hand, you may want to get more information about the printer connected to the computer.

To get information about the printer connected to the computer where your Microsoft Access database is being used, declare a variable of type Printer. You can then use that variable to get such pieces of information as the name of the printer, whether it is duplex (whether it can print on both sides), whether it is set as the default printer, etc. Here is an example:

Private Sub cmdPrinter_Click()
    Dim pDevice As Printer
    
    Set pDevice = Me.Printer
    MsgBox pDevice.DeviceName
End Sub

As mentioned already, when the user decides to print, the Print dialog box displays:

This allows the user to make changes such as chagning the printer (if there is more than one available), the number of copies, etc. You too can programmatically change some characteristics:

Using Many Printers

It is not unusual for a computer to have access to more than one printer, especially in a networked environment. To give you the ability to work on many printers, Microsoft Access has a collection named Printers. As is usually for most collections, each printer can be accessed by its index. The first printer has an index of 0, the second has an index of 1, and so on. Once you have accessed a printer, you can use it. Here is an example:

Private Sub cmdPrinter_Click()
    MsgBox Printers(0).DeviceName
End Sub

Microsoft Access Reports

Introduction to Creating a Report

As mentioned for tables, queries, or forms, there are various ways you create a report. You can create a report that would be used to print simple text that is not related to any data source. To programmatically create a report, you can call the CreateReport() method of the Application object. The syntax of this method is:

CreateReport([database[, reporttemplate]])

Both arguments of this method are optional. Here is an example of calling it:

Private Sub cmdReport_Click()
    Dim rptMaintenance As Report

    Set rptMaintenance = CreateReport
End Sub

If you call the method like this, it generates a temporary report named Report1 but it doesn't save it. After the report has been generated, it appears in Microsoft Access. If you want to keep it, you can save it. If fact, if you try closing it, you would be asked whether you want to save it. If yes, you would be asked to give it a name.

The first argument of this method is the name of the database that will receive the report. If the report will be added to the current database, you can omit this argument. The second argument is the name of an existing report that you want to use as template. If you specify this argument, you must make sure that you provide the name of a report. Otherwise, you can omit it.

Selecting a Report

To perform an operation on a report, you may need to select it first. To programmatically select a report, you can use the DoCmd object that is equipped with the SelectObject() method. The syntax to use would be:

DoCmd.SelectObject acReport, [object-name][, indatabasewindow]

The first argument must be acReport to indicate that the object you are selecting is a report. The second argument is the name of the report you want to select. To select but only highlight the report in the Navigation Pane, you can pass the third argument as True.

If the report is already opened and it is displaying, and if you omit the third argument or pass it as False, the report would be displayed in the foreground. If the report is not opened and you omit the third argument or pass it as False, you would receive an error.

Opening a Report

To programmatically open a report, you can call the OpenReport() method of the DoCmd object. Its syntax is:

DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

The first argument of this method is the name of the report that you want to open. The second argument is a constant value that can be one of the following:

This third argument, optional, is the name of a query in the database. The fourth argument, also optional, allows you to specify what record would be printed. If you omit this argument, all records of the Record Source value of the report would be printed. If you want to print only one or a few selected records, you can create a WHERE statement and pass it as this argument. The fifth argument specifies how the report should be displayed. It is a constant value that can be acDialog, acHidden, acIcon, or acWindowNormal. This argument is almost never used as it has little to no effect.

In most cases, instead of writing the code manually, you can use the Command Button Wizard to select the report to print and how you want the printing to be done.

Conditionally Opening a Form or Report

Imagine you create a form that shows the records of a table or query. When navigating among the records, imagine the user comes to a particular record he or she wants to print. If you simply write normal code to open the related report, it would show all records from the beginning. Fortunately, Microsoft Access provides an easy mechanism to execute such a scenario.

If you add a button to a form in Design View, if the Button Wizard starts, you can follow it to select the report that would be opened when a button is clicked. Microsoft Access would write the code for you:

Private Sub cmdPreviewRentalOrder_Click()
On Error GoTo Err_cmdPreviewRentalOrder_Click

    Dim stDocName As String
    Dim strWHERECondition As String

    stDocName = "RentalOrders"
    strWHERECondition = "RentalOrderID = " & RentalOrderID
    DoCmd.OpenReport stDocName, acPreview, , strWHERECondition

Exit_cmdPreviewRentalOrder_Click:
    Exit Sub

Err_cmdPreviewRentalOrder_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewRentalOrder_Click
End Sub

Closing a Report

After using a report, you (or the user) can close it. To programmatically close a report, you can call the Close() method of the DoCmd object whose syntax is the same we saw for a form. Here is an example:

Private Sub cmdCloseStafMembers_Click()
    DoCmd.Close acReport, "Employees", acSavePrompt
End Sub

When this code runs, if a report named Employees is opened, it would be closed. If there is no report opened by that name, nothing would happen.

Report Maintenance

Introduction

As done for tables and queries, part of your job as a database developer consists of maintaining your reports. This include renaming, copying, or deleting the reports. Microsoft Access supports all of the necessary operations. As mentioned for a report, make sure that you need to perform the maintenance operation. If you perform an operation by mistake but have completed it, you cannot reverse it at will. You may have to recreate the object.

Renaming a Report

To programmatically rename a report, you can call the Rename() method of the DoCmd object. The syntax to use is:

DoCmd.Rename(NewName, acReport, OldName)

The first argument is the name that the new or other report will have. The second argument must be acReport. The third argument is the name of the existing report that you want to rename. The object must exist in the Navigation Pane's section as specified by the second argument. Here is an example:

Private Sub cmdRenameReport_Click()
    DoCmd.Rename "Contractors", AcObjectType.acReport, "Employees"
End Sub

Copying a Report

Instead of renaming a report, you can copy it and keep the original. To copy an existing report using the Microsoft Windows Save As routine, in the Reports section of the Navigation Pane, you can right-click the report and click Save As... This would open the Save As dialog box that allows you to enter the desired name of the copied report. Alternatively, you can right-click the report, click Copy, then right-click an empty area of the same section of the Navigation Pane and click Paste. This would open the Paste Report As dialog box in which you can enter the new name of the copied object.

To programmatically copy a report, you can call the CopyObject() method of the DoCmd object using the following syntax:

DoCmd.CopyObject [destinationdatabase] [, newname], acReport, sourceobjectname]

The destinationdatabase argument is the name or path of the database where the copied report would be sent. If you are copying the report in the same database, you can omit this argument. The newname argument is the name you want the new report to hold. The third argument must be acReport. The last argument is the name of the existing report. Here is an example:

Private Sub cmdCopyReport_Click()
    DoCmd.CopyObject , "Contractors", acReport, "Employees"
End Sub

Deleting a Report

To programmatically delete a report, you can call the DeleteObject() method of the DoCmd object using the following syntax:

DoCmd.DeleteObject acReport, [object-name]

The first argument must be acReport. When this method is called, if the report is already selected in the Navigation Pane, you can omit the second argument and the selected report would be deleted. Otherwise, to specify the report you want to delete, pass its name as the second argument of the method. Here is an example:

Private Sub cmdDeleteReport_Click()
    DoCmd.DeleteObject acReport, "Contractors"
End Sub