Microsoft Access Database Development With VBA

Printing

 

Report Design

 

Introduction

Printing allows a user to print the values of a database. To print from a table, a query, or a form, you can right-click the object and click Print... When you do this, the document is sent directly to the printer.

Like a table, a query, or a form, 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. You can create such a report in the Design View. After creating the report, to keep it in your database, you must save it by giving it a name. The name of a report follows the rules and suggestions we have applied to tables, queries, and forms.

Programmatically Creating a Report

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.

Using a Report

 

Selecting a Report

To perform an operation on a report, you may need to select it first. To do this, in the Reports section of the Navigation Pane, you can simply click it, once. 

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, [objectname][, 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.

Visually Opening a Report

When you start Microsoft Access and open a database, if it has some reports, obviously they would be closed. To use a report, you can open it first. A  report can be opened in Design View or in Print Preview. If you (or the user) double-click(s) a report in the Reports section of the Navigation Pane, it opens in Print Preview. This views allows the user to review the document before printing. By default, the view may appear blurred to show as much of its area as possible. To be able to read it, you can click the body of the report to zoom. To print it, you can click the Print button on the toolbar to send the document to the printer.

A report can also display in Design View. To show it, if the report is currently closed, you can right-click it and click Design View. You can also select it first, then click the Design button under the title bar of the Navigation Pane. If the report is already opened, to display it in Design View, as done for the form, you can click the View button on the toolbar. You can also click View -> Design on the main menu.

Programmatically 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:

View Name Result
acViewDesign The report will display in Design View
acViewNormal The report will be sent directly to the printer
acViewPreview The report will display in Print Preview

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.

Closing a Report

After using a report, you (or the user) can close it. To close a report, the user can click the system Close button Close. You can also press Ctrl + F4. 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 (Nice!).

 
 
 

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

You can rename a report if you judge this necessary. As mentioned for a table or query, you cannot rename a report if it is opened: you would receive an error.

To rename a report in the Navigation Pane, first click the Reports button that leads to its section. Once in the appropriate section, you can right-click the report and click Rename. This would put the name in edit mode, allowing you to type the new name and press Enter.

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.

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 to. If you are copying the report in the same database, you can omit this argument. The newname argument is the name that you want the new report to hold. The third argument must be acReport. The last argument is the name of the existing report.

Deleting a Report

If you find out that you don't need a particular report anymore, you can delete it from the database. As mentioned already, when in doubt, don't delete it.

To visually delete an object, in the Reports section of the Navigation Pane, right-click the report and click Delete. You would receive a warning before the report is actually deleted.

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

DoCmd.DeleteObject acReport, [objectname]

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.

 
 
   
 

Previous Copyright 2005-2013 FunctionX, Inc. Home