Spice up Your Access Reports

Access 2007,2003,2002,2000,97

One nice feature of Access Reports is the availability of separate format properties for the Page Header/Footer, Report Header/Footer and the Detail section. If you add Groupings to the report, Group Header/Footers also become available. Each level has it’s own properties and you can format the sections to be different from the others. But what about individual records within the detail section?

As you are probably aware, the detail section repeats itself for every record in the report using whatever property settings you have specified in the report design view. However, there may be times when you wish to vary the format on a record-by-record basis based on the specific values in that record. This can be done with just a little bit of code, either as a macro instruction or a VBA expression.

For example, imagine a report that shows all upcoming campus events, their maximum capacity, and their current number of reservations. You would like to highlight the events that have reached their maximum capacity by having the event name print in red instead of black. You can accomplish this through OnFormat property of the detail section.

“A Format event occurs for each section in a report, but before Microsoft Access formats the section for previewing or printing.”

Using our example above, I’ve created a Report named Report1. This report has three fields in the detail section. These fields are txtMaximum, txtReservation, and txtEvent.

1.Open Report1 in design view.
2.Right Click on the Detail section and select Properties.
3.In the Report Detail properties box, select the Events tab.
4.In the "On Format" event of the events tab, click on the ellipsis (...) button to the right and choose Macro Builder. Then click OK. When prompted, name the macro, FormatMacro.
5.Make sure you have a column named “Condition”. If you do not see this, click on the View Menu and then click on Conditions.
6.In the first row, enter a Condition “[txtReservation]>=[txtMaximum]” and an Action of SetValue. SetValue has two Arguments; Item and Expression. Set the Item Argument to [Reports]![Report1]![txtEvent].[ForeColor] and set the Expression Argument to 125. This will change the font color to Red if the condition is true. Unfortunately you do need to know the number of the color are you are looking to use. On tip for figuring out the number is to set the color from the report design view using the color pallet on the toolbar. Then check the properties of the control to see what number it was set too. After a while you will begin to remember the colors.


7.In the second row enter an ellipsis (…) in the condition line and enter StopMacro in the Action column. This tells the macro to exit the macro if, in fact, the first condition is true.
8.In the third row leave the condition blank and enter SetValue in the action column. Set the Item Argument to [Reports]![Report1]![txtEvent].[ForeColor] and set the Expression Argument to 0. This will change the font color to black if the first condition is not met.
9.Save your Macro and run your report. The Event name should be red if the reservations numbers are equal to or greater than the maximum.
This can also be done using a Visual Basic expression. I prefer using the VBA code because it allows greater control and more options. If you choose to use an expression instead of a macro, you will need write an IF statement. The IF statement is a very powerful tool and if you are just getting started, using it will really make you feel like a programmer.The IF statement syntax is IF (something) THEN (do something) ELSE (do something else) END IF.

Follow steps 1-4 the same as above, but in step five click on Code Builder instead of Macro Builder. Once the code window opens type the following IF statement.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [txtReservation]>=[txtMaximum] then
Me.txtEvent.ForeColor = vbRed
Else
Me.txtEvent.ForeColor= vbBlack
End if
End Sub

There are a few advantages to using the VBA code instead of macros. One is that you can use vbRed and vbBlack instead of having to figure out the color number. As you get more comfortable with the IF statement and VBA, you will be able to branch off and use ELSE IF and/or Select CASE to utilize multiple conditions and colors.

Good Luck!