Events | Beginning Access 2002 VBA (Programmer to Programmer)

The examples above simply illustrate using expressions and functions on your reports, but it's not really a large amount of VBA. What you might not realize is that reports also respond to events, just like forms. So opening and closing a report generates events, and you can add code to the events just as you would with a form. Just as forms have form modules behind them, reports have report modules. There's no real difference to the way the code is created or used. The only difference is the number of objects and events that are available in reports .

You can easily see which events are available by looking at the Events tab in the Properties window for the report:

As you can see, the report has seven events. The page header and footer sections have two events, as shown below:

Group sections and the detail section have three events:

Let's look at all of these in a little more detail. We won't be building examples for most of these, but we will look at some code snippets for most events - we're covering them in case you want to use them.

Open

The Open event is generated when a report is opened, before any printing takes place. There are several uses for this:

On a report that takes a particularly long time to run, you could inform the user that they might have to wait a while, and offer them the option of not running it.
In reports with sensitive information, you could ask for a password before letting anyone run the report.
You could open a form to allow filtering of the report data. You'll see an example of this later.

The Open event has one argument, Cancel , which is something you can set. It allows you to cancel the opening of the report from within code, and thus the report isn't previewed or printed. For example:

   Private Sub Report_Open (Cancel As Integer)     Dim blnResultintResult As Integer     Dim strWarning    As String     strWarning = "This report may take some time." & _     " I suggest you make a coffee, or perhaps do a little shopping!" &_     " Or press Cancel to abort the operation."     intResult = MsgBox (strWarning, vbOKCancel, "Warning")     If intResult = vbCancel Then     Cancel = True     End If     End Sub

If you set the Cancel argument to True , then the report isn't opened.

Activate

The Activate event is generated when a report has been opened and becomes the active window. It will therefore occur after the Open event, and any time the report becomes the main window. You could use this to display a custom menu bar or toolbar:

   Private Sub Report_Activate()     DoCmd.ShowToolbar "AllOrders", acToolbarYes     End Sub

Deactivate

The Deactivate event is generated when a report stops being the active window, but before another Access window becomes active. This is an important point - it is not generated when another application becomes the active window, only when another Access window does. It can be used to reverse the actions of the Activate event. For example:

   Private Sub Report_Deactivate()     DoCmd.ShowToolbar "AllOrders", acToolbarNo     End Sub

Close

The Close event is generated when a report is closed and removed from the screen, and will occur after the Deactivate event. You could perhaps use this (or indeed the Open event) to log usage of reports, possibly for auditing purposes. Or if you've opened a filter form (which we'll be seeing shortly) during the Open event, you could close it here:

   Private Sub Report_Close()     DoCmd.Close acForm, "frmReportFilter", acSaveNo     End Sub

Error

The Error event is generated when an error occurs within the report. This includes database errors, but not VBA run-time errors. There are two arguments passed into this event:

DataErr , which is the error number
Response , which is an output parameter used to determine how the error is reported

Response can take one of two values:

acDataErrContinue , which tells Access that the error should be ignored. This would be useful if you just want to log the error, and then continue silently without the user being aware of any problems.
acDataErrDisplay , which tells Access to handle the error, so it shows the standard error details. This is the default value.

If you want to perform your own error logging, then some code like this would work:

   Private Sub Report_Error(DataErr As Integer, Response As Integer)     Response = acDataErrContinue     LogError Me, DataErr     End Sub

This sets the response to indicate that Access should do nothing to handle the error, and then it calls a function called LogError (not included) to log the details.

Format

The Format event is generated when Access knows what data it is going to put in a section, but before the data is formatted for previewing or printing. In previous versions of Access this tended to be used to display hidden fields. For example, at Rob and Dave's we try to flag up these items:

Those orders that were dispatched 6 or more days after the order date.
Unpaid orders that are 35 or more days old.

There are two ways to highlight these orders:

Try It Out-Formatting with the Format Event

Open up the Sales By Supplier By Month report in design mode.
Add a new label to the detail section. Call it lblOverdue and give it a Caption of Payment Overdue, and set the text alignment to 'center'. Place it over the Date Paid and Amount fields. It will only be visible for those orders where these fields are empty, so it will look OK.
Select the DateDispatched field and change the background style from Transparent to Normal .
Select the Detail bar and view the properties.
Click the builder button for the On Format event and choose code builder.

In the event procedure, add this code:

 If IsNull(DatePaid) And (Date - DateOrdered) > 34 Then   lblOverdue.Visible = True Else   lblOverdue.Visible = False End If If (DateDispatched - DateOrdered) > 5 Then   DateDispatched.BackColor = vbRed Else   DateDispatched.BackColor = vbWhite End If

Back in Access, switch the report into Preview mode:

Here you can see that the overdue warning only appears when the order is unpaid, and it is 35 days old; also, the dispatched date is highlighted if we took more than 5 days to dispatch the order. It shows up gray on the printed page, but it's red on the screen.

How It Works

This code was added to the Format event for the Detail section, so it gets run for every row that appears in the Detail section. Within this code, we can refer to fields and set their properties just as we would on a form. So, in the first piece of code, we check to see if the DatePaid is Null , meaning a payment hasn't been received. We also see if the difference between the current date (remember Date returns the current date) and the order date is greater than 34 . If both of these are true, then we make the Overdue label Visible . If not true, then the label is hidden.

   If IsNull(DatePaid) And (Date - DateOrdered) > 34 Then     lblOverdue.Visible = True     Else     lblOverdue.Visible = False     End If

In the second piece of code, we work out the difference between the order date and dispatch date (shown as Sent on the report). If this difference is greater than 5 days, we make the background of the dispatch date red (and set it to white if the order was dispatched on time).

   If (DateDispatched - DateOrdered) > 5 Then     DateDispatched.BackColor = vbRed     Else     DateDispatched.BackColor = vbWhite     End If

One thing to note about this is that the report preview will open slower than it did without this code. That's simply because it runs this code for every line.

Conditional Formatting

Conditional Formatting is a feature that allows you to apply formatting to a field depending upon certain conditions - so be careful that the conditions are what you really want them to be. Sounds very similar to what we've just done in code, doesn't it? However there are certain limitations and it's not as flexible as the Format event. For example, you can't apply conditional formatting to a label, and you can only set the formatting of an object, not its visibility. However, white text on a white background is pretty hard to see, so you can get the same results.

Let's add some conditional formatting to the report to see how this works in comparison to the code method.

Try It Out-Conditional Formatting

Switch the report back into design view.
Add a new textbox to the Detail section, and remove its associated label. Place this field to the very left of the Detail section, just to show it's different from our other overdue label. The name of the field isn't important; we've called it txtOverduePayment .
Set the Control Source property of the textbox to:
```
 ="Payment Overdue" 
```
Set the Font/Fore Color to White.
With the textbox selected, from the Format menu pick Conditional Formatting
Set up the condition so that it looks like this:

The important thing here is that the Font/Fore Color on this should be Black . So this field defaults to white text, and when the condition is true it becomes black. This emulates the visible/hidden idea we used earlier.
Press OK to close this dialog and switch the report into Preview mode:

Notice how the new overdue field follows the same rules as the old one. But notice how the Date Ordered field is being overwritten. That's because this new field is not transparent, but even if it were, the white text would still overwrite the field below it. This means you must make sure that this field (payment overdue) is behind all other fields. You can do this from the Format menu, by selecting Send To Back .
Select the DateOrdered field. We'll add the dispatched date formatting to this field just so you can see the difference between this method and the code method.
From the Format menu pick Conditional Formatting
Set up the condition so that it looks like this:

Here the back color is set to red if the condition is met.
Press OK to close this dialog and switch the form into Preview mode:

Of course, if we used this label instead of (and in the same place as) the VBA-formatted one, it would appear to behave similarly, as there wouldn't be any text for it to be masked by.

So there you have it. Two completely different ways to perform custom formatting on objects. Which is the best? It's really up to you. Use whichever method you feel more familiar with. The code method gives you more flexibility since you've got full control over the objects; the conditional formatting method is probably simpler, but here you've only got control of the formatting.

You might like to remove this new field and just stick with the coding method.

Cancel and FormatCount

When you were editing code for the format event you might have wondered about the arguments. The first, Cancel , you've seen before and works in the same way as the argument to the Open event. If you set this argument to True in the event procedure, then the section is not formatted. To be honest, I would never use this option.

FormatCount is a complex issue, and again one we've never used. But there might be conditions under which you need to use it. To understand this you must understand how formatting of report sections works. When Access formats a section it checks certain properties of the section, such as Force New Page , New Row or Col , or Keep Together . This last one is pretty important because if it is True , then the whole section should be kept together on a single page. This means that Access has to calculate how big the section will be, and then see if that fits on the remainder of the current page. So, how does it know how big the section is going to be? Simple, it formats the section. If it doesn't fit on the remainder of the page, a new page is started, and the section is formatted again. So FormatCount identifies how many times the formatting has been run.

This means that the code you put into the Format event could be run several times. Now if all you are doing is setting some formatting properties this doesn't matter too much, but this might be important if you are keeping totals from within code. For example, you might have declared a report-level variable, intTotalOrdersNDOT , and then be adding up some custom totals - perhaps keeping track of the number of orders that weren't delivered within the allocated time. If the format event were run twice, you'd end up counting some items twice. So you could do something like this:

   If FormatCount = 1 Then     If (DateDispatched - DateOrdered) > 5 Then     intTotalOrdersNDOT = intTotalOrdersNDOT + 1     End If     End If

This ensures that the totals are only calculated once.

Print

The Print event is generated after the section has been formatted, but before it is printed. You could use this to perform tasks that won't affect the layout of the report. In the above examples for the Format event, we set properties on some fields, but these were contained within the section, and didn't cause the section to shrink or expand, so they could easily have been coded in the Print event.

The thing to watch out for in the Print event is that it is only executed for sections that are printed. So if you open a report in preview mode and then flip to the last page, the Print event is only generated for sections on the first and last pages. That means you should never use the Print event for calculating totals, since the calculations might not get run for the middle pages.

Like the Format event, the Print event has Cancel and PrintCount arguments, which behave in a similar fashion.

Retreat

The Retreat event is run in conjunction with the Format event. Remember how we said that formatting could occur several times? If a section doesn't fit on a page once it's been formatted, Access Retreats back through the section, and then formats the section on a new page. The Retreat event is triggered between the two formats, and allows you to undo anything, such as totals, that you might have performed while formatting.

NoData

The NoData event, as its name implies, is generated when there is no data on the report. This could happen perhaps if the report was generated according to some user defined selections, and indicates that there is no data in the underlying query. You could use this to display a custom error message rather than just displaying a blank report. For example:

   Private Sub Report_NoData(Cancel As Integer)     MsgBox ("No records matched your selection. Please try again.")     Cancel = True     End Sub

The Cancel argument here behaves exactly like it does in the Open event, so setting it to True cancels the opening of the report. Setting it to False will open the report, but no records will be displayed.

The NoData event occurs after the Open event and before the Activate event.

Page

The Page event is triggered after a page is formatted, but before it is printed. You could use this to add graphics to a report as a whole, rather than just a section. For example, the following code draws a border around the whole report:

 Me.Line(0,0) - (Me.ScaleWidth, Me.ScaleHeight),,B

This type of graphic is far more difficult to achieve by drawing it on the report in design view.

When to Use the Different Events

As the Format , Print , and Retreat events are not used very often, deciding when to use which type of event can often be the hardest part of report design. Here are a few guidelines to help you:

Format

You should use this when your procedure could affect the layout of the page, for example, for making controls invisible. Access will actually lay out (format) the section after your procedure has run.

You could also use this in conjunction with a hidden section. If you need to perform some totaling for a section which is not visible, you can't use the Print event procedure, since this will never be generated. In this case, you have to use the Format event procedure.

Print

This should be used when your procedure does not modify the layout of the report, or when the procedure depends upon the page the records are printed on. It only affects sections that print, so if you only print the last page of a report, the Print event is not generated for the other pages. This is particularly important if you are using the event to calculate running totals.

Retreat

This is best used in conjunction with the Format event. For example, if you have a Format event procedure that is calculating totals, you may wish to undo some of them if you are backing up ( Retreating ) over previously formatted sections.

The FormatCount and PrintCount Properties

You should use these to ensure that actions within the format and print event procedures are only executed once. These properties increment each time a record is formatted or printed, including occasions where a record does not fit on a page.

You will probably calculate most of your totals by using the Sum command in the footer sections of the report, but the Format and Print event procedures provide a flexible way of adding totals which are not based on a grouping. However, do bear in mind the problems you can experience if you don't remember to check these.