How to: Check an Access form with WklValidator

Check the text boxes of an Access form (a list's edit form, a dialog box, or any bound form) against rules per field and per record, show errors next to the fields and in a summary, and prevent a record that breaks a rule from being saved.

Requires WKL Forms Object Library 1.31 or later.

• One control on the form. Insert WKL Validator (an ActiveX control) where the summary should appear, for example below the fields. It doesn't draw anything while there are no errors.
• An empty module. Set the form's Has Module property to Yes. Access passes a form's events to a control only when the form has a module; an empty one is enough. The validator sets the event properties it needs to [Event Procedure] on its own.
• Rules on the designer's Field rules and Record rules pages, or as JSON in Rules. Per field: required, maxLength, pattern, min, max, valueList, and text, optionally with when (the rule applies only while another field has one of the given values). Per record: exactlyOne, atLeastOne, and atMostOne across several fields.
• What the user sees: leaving a field that breaks a rule keeps the focus in it; the field gets a red border and shows the message as its tooltip; the validator shows “2 errors – …”, and clicking it goes to the first error. Saving a record that breaks a rule is rejected, including the save Access performs when the focus moves from a docked edit form back to its list. No message boxes appear.

An edit form frmIdentity (bound to tblIdentity, with an empty module) is docked next to a list. The login must consist of lowercase letters and is required for the kind “Login”; an identity must name exactly one of a person, a Windows account, or a key:

' Once, in design view (or on the designer's pages); the rules are saved with the form.
Forms!frmIdentity!Validator1.Object.Rules = _
    "{""fields"":[" & _
    "{""field"":""Login"",""pattern"":""[a-z]+"",""text"":""Login: lowercase letters only""}," & _
    "{""field"":""Login"",""required"":true,""when"":{""field"":""Kind"",""equals"":""Login""}}]," & _
    """records"":[{""exactlyOne"":[""PersonId"",""Account"",""KeyId""],""text"":""Specify exactly one source""}]}"

A custom Save button can report a rejection from the data service in the summary, too:

Private Sub cmdSave_Click()
    On Error GoTo Rejected
    If Not Me!Validator1.Object.Validate Then Exit Sub   ' shows what is missing
    Me.Dirty = False
    Exit Sub
Rejected:
    Me!Validator1.Object.SetError "", Err.Description
End Sub

Common patterns:

In JSON, every backslash is written twice (\\d).

• Without a module, the validator doesn't check anything on its own. It says so in its rectangle (“Validation inactive …”), and IsConnected is False; calling Validate from code still works. See The validator checks nothing.
• How fields are named. field is a control's name or the field a control displays (its Control Source; calculated controls never count); the same applies to when.field and to the fields in record rules. A field without a control is read from the record and checked, but not marked. Messages refer to a field by the caption of the control's attached label; a label that's merely placed next to it doesn't count.
• Patterns always match the whole text (no ^/$ needed), are .NET regular expressions (\\p{L} for any letter in JSON), check only values the user enters, and count as not matched after a quarter of a second.
• Event properties that contain a macro or an expression are left unchanged; that event then goes to the macro, not to the validator. Use [Event Procedure] and call the macro from the procedure.
• Data source errors on save (a table's validation rule, or a data service that rejects the record through its ODBC driver) appear in the summary instead of in an Access message box (HandleDataErrors, on by default), together with the driver's reason, whenever Access saves during a user action. A save that your own code starts (Me.Dirty = False, or moving the focus in code, such as area!grdMain.SetFocus) reports its error to that code instead, as in every Access form (for example, error 3316 for a table's validation rule or 3155 for an ODBC driver that rejects the record); pass the text to SetError as shown above. See A refused save does not show in the validator's summary.
• A new record, a saved record, and Undo clear the errors; ClearErrors restores the borders and tooltips at any time.
• Checks you can't express as a rule go in the ValidateRecord event (Message, Cancel); unlike the rules, this requires code in the module.
• Built in code: insert the validator as a control block, not with CreateControl; see How to: Put WKL controls into forms by code.
• The validator works on Access forms; on Excel and Word UserForms, there's no form for it to check.

• API reference: WklValidator, enumerations (WklValidateMode)
• How to: Validate cell input: the same rules for the cells of a list
• How to: Edit records in an Access form
• How to: Put WKL controls into forms by code
• The validator checks nothing
• A refused save does not show in the validator's summary