Switch an account on and off

In this tutorial, you add a switch for the field Active to a user form. When the user flips the switch, the record is saved immediately; on a locked form, the switch doesn't respond.

• Microsoft 365 Access or the Access Runtime, 32-bit or 64-bit, with the WKL Forms Object Library 1.32 or later installed and registered for the current Windows user (How to: Install the released package).
• A new or existing database in a trusted location.
• Every WKL control opens its designer the same way: in Design view, open the property sheet (F4), go to the Other tab, and click [...] in the Custom row (Opening the designer).

Paste this procedure into a standard module and run it once from the Immediate window (CreateToggleSample):

Public Sub CreateToggleSample()
    With CurrentDb
        .Execute "CREATE TABLE tblUser (ID LONG CONSTRAINT PK_User PRIMARY KEY, Name TEXT(50), Active YESNO)"
        .Execute "INSERT INTO tblUser VALUES (1, 'Miller', True)"
        .Execute "INSERT INTO tblUser VALUES (2, 'Smith', False)"
    End With
End Sub

Create a single form named frmUser that's bound to tblUser, with a text box Name. Choose Form Design › Controls › ActiveX Controls › WKL Toggle Switch, draw the control below the text box (about 5 cm wide and 0.6 cm high), and name it tglActive. On the Data tab of the property sheet, set its Control Source to Active.

Open the designer (property sheet, Other tab, Custom row, [...]). On the Switch page, set Caption to Account, Text when on to Active, and Text when off to Locked; the preview shows both states. Choose OK.

Add this to the form module:

Private Sub tglActive_Change()
    Me.Dirty = False        ' the switch takes effect at once, as in the Windows settings
End Sub

Open the form in Form view. Miller shows “Account (on) Active”. Click the switch: it now shows “Locked”, and the record is saved (the record selector doesn't show a pencil). Move to Smith and press Space while the switch has the focus: it turns on. Set the form's Allow Edits property to No and try again: the switch doesn't change.

• A click or Space flips the switch and saves the record through Change.
• tblUser.Active holds the new value right after the switch is flipped.
• On a form that doesn't allow edits, or with Locked = Yes on the switch, nothing changes.

• How to: Use a toggle switch for a Yes/No field: use the switch as a list column.
• API reference: WklToggleSwitch.