Report Open Execute a Query Before Report Displays

The On Open event procedure works just like the On Open event procedure of forms, in that it is triggered when the form is opened. The functionality that can be placed within this event procedure is unlimited, but it is important to keep in mind that the code will trigger only once. If there is a repetitive task that needs to execute throughout the report, then the code should be moved to a different event procedure.

There are many things that may need to happen when a report is opened, but one task could be the preparation of data, just prior to printing. It is not uncommon in Access to base a report on a table that is used specifically for the report. There could be some complex calculations or last minute processing that need to be done before the report can print correctly.

For example, suppose that you need to execute a Delete Query, an Append Query, and an Update Query prior to printing a report. The Delete Query would be used to empty any previous data from the report table, the Append Query would retrieve the new base data, and the Update Query would finish the data population effort.

So, when the form is opened, completing the described operation is as simple as issuing the necessary OpenQuery statements. The following example demonstrates this technique:

Private Sub Report_Open(Cancel As Integer) 'Show a message in the Status Bar

DoCmd.Echo True, "Preparing Report Data...Please Wait"

'Turn off the Warning Messages DoCmd.SetWarnings False

'Populate the report table DoCmd.OpenQuery "qryInvoices_0Empty" DoCmd.OpenQuery "qryInvoices_1AddData" DoCmd.OpenQuery "qryInvoices_2UpdateTotals"

'Reset the Warning Messages DoCmd.SetWarnings True

End Sub

In the above example, the code starts by showing a message in the status bar, by using the Echo statement. (Note that if the first parameter is set to False, then the screen will not refresh.) Next, the standard Access error messages are suppressed by issuing the SetWarnings statement with the parameter of False. From there, the queries to empty, add, and update the data are executed. (Notice the naming conventions used, which keep the queries in a logical order.) At the end of the procedure, the standard warning messages are turned on again.

This technique is quite useful in ensuring that the data is always up to date when the report executes, but also be aware that it will add significant processing time to the report generation time, especially in Preview mode.

0 0

Post a comment