How to: Validate cell input

Reject a value that a user enters in a WklGrid, WklTreeList, or WklGantt cell if it breaks a rule, show the reason at the cell, and respond after a value has been saved.

Requires WKL Forms Object Library 1.31 or later.

Solution overview

Column rules cover what Access offers for each field: Required, MaxLength (by default, the field size in the record source), Pattern (a regular expression), MinValue and MaxValue, ValueList, and ValidationText for your own message. Set them on the designer's Columns page (in the section for the selected column) or in code.
What happens: a value that breaks a rule isn't written. As with an Access validation rule, the user can't leave the cell, but no message box appears: the cell shows a red border, the error icon, and the message as a tooltip, and the error bar below the records counts the errors („1 error“, and so on) and goes to the first one. Esc discards the input. ValidateMode determines when a cell is checked: when the user leaves it (the default), while typing as a marker, or, for the new-record row, only when the row is saved.
Your own rules, such as rules across several fields or a lookup in another table, go in ValidateCell: correct NewValue, reject the value with Cancel = True, and give the reason with SetError.
After the value is saved, AfterCellUpdate occurs; errors from the data source go to DataError, and the cell keeps the source's message.

Implementation

Rules of the columns, set once in design view or in Form_Load:

Private Sub Form_Load()
    With Me!Grid1.Object
        .Columns("Customer").Required = True          ' MaxLength 0: the field size applies
        .Columns("Code").Pattern = "[A-Z]{2,4}"
        .Columns("Code").ValidationText = "Two to four capital letters"
        .Columns("Quantity").MinValue = 1
        .Columns("Quantity").MaxValue = 1000
        .Columns("Status").ValueList = "new;active;blocked"
    End With
End Sub

A custom rule across two fields, with the reason shown at the cell:

Private Sub Grid1_ValidateCell(ByVal Key As Variant, ByVal Column As String, NewValue As Variant, Cancel As Integer)
    If Column = "EndDate" Then
        If Not IsNull(NewValue) And NewValue < Me!Grid1.Object.CellValue(Key, "StartDate") Then
            Cancel = True
            Me!Grid1.Object.SetError Key, "EndDate", "The end date is before the start date."
        Else
            Me!Grid1.Object.SetError Key, "EndDate", ""      ' an empty text removes the error
        End If
    End If
End Sub
 
Private Sub Grid1_AfterCellUpdate(ByVal Key As Variant, ByVal Column As String)
    Me!txtTotal = Me!Grid1.Object.SummaryValue("Quantity")
End Sub

Remarks

The new-record row checks every column before it's saved, including a required field the user never filled in; if a check fails, the row isn't saved, keeps the focus, and shows the errors at its cells.
Existing records are saved cell by cell, so their cells are checked when the user leaves them, in every ValidateMode.
Values written in code (CellValue = …) aren't checked and don't raise events; the rules apply to user input. UI Automation (ValuePattern.SetValue) is checked like typing and rejected with E_INVALIDARG.
Screen readers announce the message: the cell reports it as its status and is marked as invalid for its form, and the error bar is a live region.
ErrorCount, ErrorSummary, ErrorText, GoToFirstError, ClearErrors, and the ValidationChange event let your own buttons track the errors. For checks on an Access form, such as the grid's edit form, use `WklValidator`.
Editing is possible when AllowEditing is True (the default) and the column isn't ReadOnly; group rows are never editable.

How to: Validate cell input

Solution overview

Implementation

Remarks

Related topics