Automating Microsoft Access with VBA

 < Day Day Up > 

One of the ways you can lead users through the work process is to open objects for them as the objects are needed and then close them when the users are done with them. Of course, most users can be taught to open and close objects for themselves, but automating the process goes a long way toward keeping users on the right road.

Opening a Report

In the case study found in Chapter 8, "Understanding Objects," you converted the macro that opens BillingReport to an event procedure using the following code:

Private Sub cmdOpenReport_Click() On Error GoTo HandleErr DoCmd.OpenReport "BillingReport", acViewPreview ExitHere: Exit Sub HandleErr: MsgBox "Error " & Err.Number & ": " & _ Err.Description & " in cmdBillingReport_Click" Resume ExitHere End Sub

For now, you're interested in the OpenReport method. It's very similar to the OpenForm method you learned about in Chapter 10, "Working with Forms." Both are methods of the DoCmd object. To open a report, use the OpenReport method in the following form:

DoCmd.OpenReport reportname [, view] [, filtername] [, wherecondition] [, windowmode] [, openargs]

where reportname is a string expression that identifies the report you're opening by name. Table 14.1 lists the optional arguments. Tables 14.2 and 14.3 give additional details about the syntax of OpenReport.

Table 14.1. OpenReport Optional Arguments

Argument

Data Type

Explanation

view

Constant

One of the intrinsic constants listed in Table 14.2; determines the report's view.

filtername

Variant

A string expression that equals the valid name of a fixed query.

wherecondition

Variant

A string expression that equals a valid SQL statement WHERE clause, without the WHERE keyword.

windowmode

Constant

One of the intrinsic constants listed in Table 14.3; determines the report's mode.

openargs

Variant

A string expression or value that's passed to the report's OpenArgs property.

Table 14.2. View Argument Constants

Constant

Integer Value

Explanation

acViewNormal

0

Prints the report.

acViewDesign

1

Opens the report in Design view.

acViewPreview

2

Opens the report in Print Preview.

Table 14.3. Windowmode Argument Constants

Constant

Integer Value

Explanation

7acWindowNormal

0

Relies on the report's properties.

acHidden

1

Opens, but hides, the report.

acIcon

2

Opens, but minimizes, the report in the Windows taskbar.

acDialog

3

Opens the report as a dialog box when the Modal and PopUp properties are set to Yes.

The OpenReport method that you added to the application in Chapter 8

DoCmd.OpenReport "BillingReport", acViewPreview

opens the BillingReport report in Print Preview mode. Although there are several arguments, this particular statement needs only two to get the job done.

The report itself is based on the BillingReportSource query, which grabs the values you choose in the form. You can pass the data source in the filtername argument, or you can build a WHERE clause and use the wherecondition argument to limit the resulting report. There are often many ways to accomplish the same task. Normally, you'll find one that accommodates the task a bit better than any other.

Closing a Report

To close a report, use the DoCmd object's Close method in the form

DoCmd.Close [objecttype] [, objectname] [, save]

where all the arguments are optional. When all are omitted, VBA closes the object that has the focus. When you're explicitly referencing the report by name, use the objectname argument to identify the report and identify the object by type using the acReport intrinsic constants. The save argument has three constants:

  • acSavePrompt The integer value is 0 for this constant. It displays the Save prompt so the users can decide.

  • acSaveYes This constant saves changes to the report and equals the integer value of 1.

  • acSaveNo This default setting doesn't save changes and equals the integer value of 2.

     < Day Day Up > 
    Related

    Категории