How to: Use a toggle switch for a Yes/No field

Show a Yes/No field as a switch that takes effect immediately, like the switches in Windows Settings: on a form with WklToggleSwitch, or in a list as a column in WklGrid or WklTreeList.

Requires WKL Forms Object Library 1.32 or later.

Solution overview

On a form: insert WKL Toggle Switch (an ActiveX control) and set its Control Source (ControlSource) to the Yes/No field, just as for a check box. Set Caption, OnText, and OffText on the designer's Switch page or in the property sheet.
Immediate effect: flipping the switch changes the record like any other edit; save it in the Change event with Me.Dirty = False.
Not editable: with Locked (Locked) = Yes on the control or Allow Edits (AllowEdits) = No on the form, the switch doesn't change.
In a list: a column with Editor = wedToggle draws the same switch. The column's OnText and OffText add a state text (empty by default, because a list has little room). A click or Space flips the switch, and it's saved like any other cell edit in the list.

Implementation

A form frmUser is bound to tblUser and contains a switch tglActive bound to the field Active:

' Once, in design view (or on the designer's pages); saved with the form.
With Forms!frmUser!tglActive
    .ControlSource = "Active"
    .Object.Caption = "Account"
    .Object.OnText = "Active"
    .Object.OffText = "Locked"
End With

' Form module of frmUser: every switch is saved at once.
Private Sub tglActive_Change()
    Me.Dirty = False
End Sub

A list of users with the same switch as a column:

With Me!lstUser.Object
    .RecordSource = "tblUser"
    .KeyField = "UserID"
    With .Columns.Add("Active", "Account")
        .Editor = wedToggle
        .OnText = "Active"
        .OffText = "Locked"
    End With
End With

Remarks

Don't rely on the field until the record is saved. Access marks the record as dirty when the user flips the switch, and writes the switch's value to the field no later than when the record is saved. In Change, and until the record is saved, the field (Me!Active) may still return the old value, or it may already return the new one. In Change, read the new state from the control (Me!tglActive), or save the record first.
Undo (Esc, Me.Undo) returns the switch to its previous position along with the record.
Setting Value in code only changes what the switch displays and doesn't write anything; change the field instead (Me!Active = True).
Null (a field without a value, such as a SQL Server bit column that allows Null) shows a gray switch with the knob in the middle and no state text; the first click turns it on.
Screen readers announce a button with the Toggle pattern: the caption (or the control name) and the state: on, off, or indeterminate.
On Excel and Word UserForms, the switch works unbound: set Value in code and read it in Change.
Not in the detail section of a continuous form: Access creates only one instance of an ActiveX control there, and it shows only the current record; with a Control Source in the detail section, Access doesn't finish opening the form. To show a switch per record, use the list column.

How to: Use a toggle switch for a Yes/No field

Solution overview

Implementation

Remarks

Related topics