Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

Recipe 3.6 Create a Simple Bar Graph on a Report

3.6.1 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?

3.6.2 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.

3.6.3 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. When it prints that row, the rectangle has a width proportional to the value in that numeric field.

In the sample report, the maximum width of the rectangle is four inches. If a student has a score of 100%, you want the printed bar to be 4 inches wide. Therefore, the expression:

Me.txtScore/100 * 4

evaluates to the number of inches wide that you'd like the bar to be. To set the width of the bar from the Format event, however, you'll need to specify the width in twips, not inches, because that's what Access expects. There are 20 twips in a point and 72 points in an inch, so there are 1,440 twips in an inch. To convert the number of inches to twips, multiply the calculated value by 1,440. The final expression in the sample report is:

(Me.txtScore/100) * (1440 * 4)

This expression will evaluate to be the width of the bar in twips, which is exactly what you need. If your report needs a scaling factor other than 100 or a maximum width other than 4, you'll need to adjust the expression accordingly.

Though the method presented in this solution will work only for the simplest of cases, when it does work it does a great job. It's quick, it's simple, and it produces nice output. To achieve the effect you want, experiment with different shadings, border colors, and gaps between the rows.

[ Team LiB ]