Access Cookbook - Ken Getz [65]
Though simple, this method does have a few limitations:
The page-range indicator must go in the page footer. If you attempt to place it in the page header, the data it prints will always be off by a page in one direction or the other, depending on how you're viewing the report.
For this method to work, you must include the page header section on every page. (The PageHeader property for the report must be set to All Pages.) Because you must fill in the hidden text box once for each page, the only place you can do that is in the page header.
TIP
It's interesting to note that within an expression you place within the Properties window, you must surround field names and control references with brackets ([ ]). Within VBA code, the brackets are optional, and you generally don't need to use them unless the field or control name isn't a valid VBA identifier (if it includes spaces in its name, for example).
3.6. Create a Simple Bar Graph on a Report
Problem
You need to create a simple bar graph on a report. Microsoft Graph or the Office Web Components would probably work, but you're hoping for a simpler native Access solution. You need a bar for each row showing the relative score for each student. Can't you do this with the standard Access controls?
Solution
You can place a rectangle control in the detail section of your report and set its width during the Format event that occurs as Access lays out each row of data. This solution shows how you can create a simple bar graph, setting the width of the rectangle control to be based on a numeric value in your data.
Open and run the report rptGraph in 03-06.MDB (see Figure 3-12). This report shows a list of students and their scores, along with a bar whose width represents the value of the score.
Figure 3-12. The sample report, rptGraph
To create a bar graph like this one in your own applications, follow these steps:
Create your report, including the text data you'd like to show for each row. The sample report shows the Name and Score fields from tblScores, using controls named txtName and txtScore.
Add a rectangle control from the report toolbox and place it next to the data in the detail section. In the sample report, the rectangle's control name is rctBar. The control's width isn't important, because you'll be adjusting that programmatically (the example report sets the width of the rectangle to be the maximum width for the report, four inches). For appearance purposes, you'll probably want to set its height to be the same as the height of the text boxes you've already placed on the report. Figure 3-13 shows the report in design view.
Figure 3-13. rptGraph in design view
If you want, you can place vertical lines at regular intervals along the maximum length of the bar. In the sample report, the vertical lines are placed at the 25%, 50%, and 75% locations. You can place these lines wherever you like; if they're the same height as the detail section, they'll appear as continuous lines on the printed report. If you've used group headers and/or footers in your report, you'll need to place the vertical lines in those sections as well to make them appear continuous.
To set the width of the rectangle for each row, create the following event procedure in the OnFormat event property of the report's detail section:
Private Sub Detail1_Format(Cancel As Integer, FormatCount As Integer)
Me.rctBar.Width = (Me.txtScore / 100) * (1440 * 4)
End Sub
This event procedure tells Access to run your new macro each time it formats a row of data. Figure 3-13 shows the properties sheet for the detail section.
Save and run the report. It should look like the report shown in Figure 3-12.
Discussion
As Access lays out the report and prepares to print it, it formats each row of data for presentation. As it does this, it runs the VBA code attached to the OnFormat event property. In this case, for each row of data, you've told Access to set the width of the rectangle control based on the value in a numeric field.