Show a measurement series with its limit and trend

Build a form that shows one measuring point per record and, next to it, that point's wall thickness over time: a line with the minimum as a limit line, a dashed trend line, and the date when the trend reaches the minimum.

• Microsoft 365 Access or the Access Runtime, 32-bit or 64-bit, with the WKL Forms Object Library 1.60 or later installed and registered for the current Windows user (How to: Install the released package).

Paste this procedure into a standard module and run it once from the Immediate window (CreateChartSample). It creates two measuring points and their readings; point TML-1 loses 0.25 mm per year.

Public Sub CreateChartSample()
    Dim db As DAO.Database, i As Long
    On Error GoTo ErrorHandler
 
    Set db = CurrentDb
    db.Execute "CREATE TABLE tblPoint (ID LONG CONSTRAINT PK_Point PRIMARY KEY, Name TEXT(50), MinWall DOUBLE)"
    db.Execute "CREATE TABLE tblReading (ID COUNTER CONSTRAINT PK_Reading PRIMARY KEY, PointID LONG, ReadDate DATETIME, Wall DOUBLE)"
    db.Execute "INSERT INTO tblPoint VALUES (1, 'TML-1', 8.5)"
    db.Execute "INSERT INTO tblPoint VALUES (2, 'TML-2', 6)"
    For i = 0 To 7
        db.Execute "INSERT INTO tblReading (PointID, ReadDate, Wall) VALUES (1, #" & Format(DateSerial(2018 + i, 3, 1), "yyyy-mm-dd") & "#, " & Str(10 - 0.25 * i) & ")"
    Next
    For i = 0 To 2
        db.Execute "INSERT INTO tblReading (PointID, ReadDate, Wall) VALUES (2, #" & Format(DateSerial(2020 + i, 6, 1), "yyyy-mm-dd") & "#, " & Str(7 - 0.1 * i) & ")"
    Next
 
FinalHandler:
    Set db = Nothing
    Exit Sub
 
ErrorHandler:
    MsgBox Err.Description, vbExclamation
    Resume FinalHandler
End Sub

In Design view, create a form named frmPoint with the record source tblPoint, the text boxes Name and MinWall, and an unbound text box txtReached. Choose Form Design › Controls › ActiveX Controls, insert WKL Chart on the right, and name it Chart1. Save and close the form.

The chart's settings are saved with the form. Set them once in code in Design view (run BindChart from the Immediate window), or in the chart's designer (property sheet, Other tab, Custom row, [...], on the Data, Appearance, and Link pages).

Public Sub BindChart()
    Dim c As Object
    On Error GoTo ErrorHandler
 
    DoCmd.OpenForm "frmPoint", acDesign
    Set c = Forms("frmPoint").Controls("Chart1").Object
    c.RecordSource = "tblReading"
    c.XField = "ReadDate"             ' a date: time axis with calendar steps
    c.YFields = "Wall"
    c.LinkMasterFields = "ID"      ' follow the current record, as a subform does
    c.LinkChildFields = "PointID"
    c.Title = "Wall thickness"
    c.YFormat = "0.0"
    c.ShowTrend = True
    DoCmd.Close acForm, "frmPoint", acSaveYes
 
FinalHandler:
    Set c = Nothing
    Exit Sub
 
ErrorHandler:
    MsgBox Err.Description, vbExclamation
    Resume FinalHandler
End Sub

Open the frmPoint module and add:

Option Compare Database
Option Explicit
 
Private Sub Form_Current()
    On Error GoTo ErrorHandler
 
    With Me!Chart1.Object
        .ClearReferences
        .AddReference Me!MinWall, , "Minimum", wskBad
    End With
 
FinalHandler:
    Exit Sub
 
ErrorHandler:
    MsgBox Err.Description, vbExclamation
    Resume FinalHandler
End Sub
 
Private Sub Chart1_DataLoaded(ByVal Points As Long)
    Dim reached As Variant
    On Error GoTo ErrorHandler
 
    If Len(Me!Chart1.Object.LastError) > 0 Then
        Me!txtReached = Me!Chart1.Object.LastError
        GoTo FinalHandler
    End If
    reached = Me!Chart1.Object.TrendReaches(0, Me!MinWall)
    If IsNull(reached) Then
        Me!txtReached = "The trend doesn't reach the minimum"
    Else
        Me!txtReached = "Minimum reached on " & Format(reached, "mm/dd/yyyy")
    End If
 
FinalHandler:
    Exit Sub
 
ErrorHandler:
    Me!txtReached = Err.Description
    Resume FinalHandler
End Sub

Open frmPoint in Form view. The chart shows TML-1's eight readings dropping from 10.0 to 8.25, the red Minimum line at 8.5, and the dashed trend; txtReached shows a date in 2024. Move to the next record: the chart reads TML-2's three readings and draws its limit at 6.

The chart follows the current measuring point, always keeps the limit within the value axis, and reports the date when the trend reaches it. An invalid record source doesn't stop the form: the chart draws the error, and LastError contains its text.

• How to: Show limits and a trend in a chart: bands, multiple series, and monthly counts.
• WklChart overview