How to: Print gauges and charts in a report

Print one gauge, KPI card, or chart per record in an Access report (for example, the wall thickness of every measuring point with its limit and trend) so that the printout stays legible on a black-and-white or grayscale printer.

Requires WKL Forms Object Library 1.62 or later.

• A bound WklGauge or WklKpiCard in the detail section shows each record's value: set its Control Source to the field, just as for a text box.
• A WklChart in the detail section reads each record's data on its own, through LinkMasterFields and LinkChildFields. Access passes a report only the fields that are bound to a control, so put the master field (for example, ID) in a text box in the detail section; the text box can be invisible.
• Limits per record: limit lines and bands aren't saved with the chart. Set them in the detail section's Format event, where the record's fields are available; call Requery there if the event needs the record's data, for example for TrendReaches.
• The Format event as an expression: set the detail section's event property to =FormatPoint() and put the function in a standard module. The report then doesn't need a module of its own. A report that has both an ActiveX control and its own module can fail in an ACCDE with error 459, “Object or class does not support the set of events” (an Access defect; the ACCDB works); see the troubleshooting page below for the cause and the fix.
• PrintStyle: with the default wpsAutomatic, the gauge and the chart add patterns when they're printed or shown in Print Preview: hatched ranges and bands, dashed series, and limit lines with different strokes. On a grayscale printout, “bad”, “warn”, and “ok” remain distinguishable.
• Background: in a report, a gauge with the default BackColor and a chart with a light color scheme don't have a background of their own: the section's color, including the alternate row color, shows through.

A report rptPoints based on the table tblPoint (ID, Name, MinWall) has the text boxes txtId (Control Source ID, Visible No), txtName, and txtReached in the detail section, a WKL Gauge gauWall with the Control Source Wall, and a WKL Chart chtWall bound to the readings in Design view: RecordSource = tblReading, XField = ReadDate, YFields = Wall, LinkMasterFields = ID, LinkChildFields = PointID, ShowTrend = True. The text box txtMinWall (Control Source MinWall) holds the record's limit.

The detail section's On Format event property is =FormatPoint(); the report doesn't have a module of its own. In a standard module:

Option Compare Database
Option Explicit
 
Public Function FormatPoint() As Variant
    Dim r As Report, chart As Object, reached As Variant
    On Error GoTo ErrorHandler
 
    Set r = Reports("rptPoints")
    Set chart = r!chtWall.Object
    ' The data of this record before the chart is drawn: needed for TrendReaches below.
    chart.Requery
    chart.ClearReferences
    chart.AddReference r!txtMinWall, , "Minimum", wskBad
    reached = chart.TrendReaches(0, r!txtMinWall)
    r!txtReached = IIf(IsNull(reached), "The trend doesn't reach the minimum", "Minimum reached " & Format(reached, "mm/yyyy"))
 
FinalHandler:
    Set chart = Nothing
    Set r = Nothing
    Exit Function
 
ErrorHandler:
    ' A repeating event: write the error into the record's line instead of showing a dialog for every record.
    Reports("rptPoints")!txtReached = Err.Description
    Resume FinalHandler
End Function

To keep the colors on paper, set PrintStyle to wpsColor (in the property sheet, or on the designer's PrintStyle card). To show the patterns on screen as well, for users who can't rely on color, set wpsPatterns.

• Supported output: Print Preview and a PDF created with DoCmd.OutputTo, with one image, including its limits and trend, per record. Printing directly to a printer isn't a supported output; a printer driver that doesn't report alpha blending gets the controls with their own background.
• A master field without a control reads as empty, and the chart then shows “No data”.
• No events in a report: WKL controls don't raise events (DataLoaded, RangeChange, …) in a report; use Requery in the Format event as shown above.
• In an ACCDE, the same report prints too, including with the Format event as an expression.
• WklKpiCard keeps its card look: by design, it's a tile with a border. Its status is also shown as text, so it doesn't need patterns.

• WklChart overview
• WklGauge overview
• How to: Show limits and a trend in a chart
• A report stops in the ACCDE with error 459