Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

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:

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

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

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

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

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