Featured Database Articles
Posted Nov 21, 2003
Access Report Tricks
By Danny Lesandrini
I have said it before, and I will say it again; Access is the best reporting tool on the market. Anyone who thinks otherwise just has not experienced the power of writing reports in Microsoft Access. Power? Yes, and plenty of it. Follow along as I demonstrate just a few examples of things you can do with Access reports.
Simple, but Powerful
Standardize Report Captions
The Report Caption is the text that shows up in the blue title bar of the window. There is a property in the report designer that allows you to set this value, but all too often, I forget to do it. Therefore, it usually reads something like "Download_RPT_new_10-22-03," which is pretty ugly. You can standardize report captions using some simple code. Here's what you do:
Add a label to your report named, for example, lblTitle
Add this code to the report's module:
Private Sub Report_Open(Cancel As Integer)
' Me refers to the container, the report itself.
Me.Caption = Me.lblTitle.Caption
Standardize Report Headers
Avoid having to modify dozens of reports when header information changes. Create a simple report with all the header information and drag-n-drop it in the Report Header of each of your reports. That way, if something changes, you only have to edit one report object, the header subform, and the changes propagate to all your reports.
There is, however, a caveat: You MUST place your header controls in the subform's Report Header section. Since the subreport contains no data, the Page Header will never display, so controls there are ignored.
Standardize Report Footers
Hey, footers have to be as easy as headers, right? Well, yes, they are, so long as you do not insert a Page X of Y control into it. To use paging with subforms, you need to understand how Access handles report pages. Let's begin by placing this code in the text box's ControlSource property:
="Page " & [Page] & " of " & [Pages]
The problem is, again the subform has no pages, so it will always display 0 of 0. What you really want is to reference the page count of the report that contains the subform, its "Parent." So, add the word "Parent" to the code, like this:
="Page " & [Parent].[Page] & " of " & [Parent].[Pages]
If you try this out, you'll see there is still a problem. Now the control displays 1 of 0. The subform does not know how many pages there are in the report because the Parent form doesn't know. In fact, Access reports never know how many pages they have, until you force them to calculate it. How do you do that? You insert a control that calls the [Pages] method, as we did in the subform.
In other words, the [Pages] method forces the report to secretly format itself in hidden mode to calculate the total number of pages. Then, it returns to the beginning with this value safely stored in the [Pages] property and displays it as requested. So, in order to get the [Parent].[Pages] method to work in the subform, there must be one placed on the parent form. I know, it rather defeats the purpose, but that is how it works.
Draw a Box Around the Page
Recently I was asked to draw a box around the entire page of a report. I must admit, I had to search Google for the code, but it is quite simple. Since you want to modify the page, it makes sense to put this code in the Report_Page() event.
Private Sub Report_Page()
On Error Resume Next
' Set Thickness and Border Style
Me.DrawWidth = 6 ' larger number, thicker line
Me.DrawStyle = 0 ' 0 to 6 = solid to invisible
' object.Line (x1, y1) - (x2,y2), color, [Box=B]
Me.Line (0, 0)-(Me.ScaleWidth, Me.ScaleHeight), vbGreen, B
Toggling Row or Control Backcolors
Another frequent request by users is to have report rows alternate backcolor, so they are easier to read. There are two ways to do this: change the detail section color or change the background color for each individual control in the row. Changing the detail secion color is less processing, but it is also easy to manipulate controls as a group. Consider this code, which demonstrates both methods:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error Resume Next
Const cYellow As Long = 10092543
Const cwhite As Long = 16777215
Const cPurple As Long = 16751052
Dim ctl As Control
Dim sec As Section
Set sec = Me.Section("Detail")
If sec.BackColor = cwhite Then
sec.BackColor = cYellow
sec.BackColor = cwhite
For Each ctl In sec.Controls
If ctl.BackColor = cYellow Then
ctl.BackColor = cwhite
ctl.BackColor = cYellow
Play with the colors a little, using Purple for the controls instead of Yellow and you'll see what I mean. It creates some very ugly reports, if you are not careful, but it is very powerful for those with a creative, right-brained programmer's eye. Click the link below to see the output for the above report tricks.
Report Trick Example Report (Requires the Snapshot Viewer)
MS Access Archives