Keep the roles of a user as tokens

In this tutorial, you build a user form that shows the user's roles as tokens. Each role is a row in a child table, the available roles come from a lookup table, and any other value is rejected.

• Microsoft 365 Access or the Access Runtime, 32-bit or 64-bit, with the WKL Forms Object Library 1.33 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 (CreateTokenSample):

Public Sub CreateTokenSample()
    With CurrentDb
        .Execute "CREATE TABLE tblUser (ID LONG CONSTRAINT PK_User PRIMARY KEY, Name TEXT(50))"
        .Execute "CREATE TABLE tblRole (Role TEXT(30) CONSTRAINT PK_Role PRIMARY KEY)"
        .Execute "CREATE TABLE tblUserRole (UserID LONG NOT NULL, Role TEXT(30) NOT NULL, " & _
                 "CONSTRAINT PK_UserRole PRIMARY KEY (UserID, Role))"
        .Execute "INSERT INTO tblUser VALUES (1, 'Miller')"
        .Execute "INSERT INTO tblRole VALUES ('Reader')"
        .Execute "INSERT INTO tblRole VALUES ('Author')"
        .Execute "INSERT INTO tblRole VALUES ('Admin')"
        .Execute "INSERT INTO tblUserRole VALUES (1, 'Reader')"
    End With
End Sub

Create a single form named frmUser that's bound to tblUser, with the text boxes ID and Name.

Choose Form Design › Controls › ActiveX Controls › WKL Token Box, draw the control below Name (about 8 cm wide and 1 cm high), and name it tokRoles. On the Data tab of the property sheet, set its Control Source to ID so the box follows the current record.

Open the designer (property sheet, Other tab, Custom row, [...]). On the Data page, set Record source to tblUserRole, Value field to Role, Link child fields to UserID, and Link master fields (form) to ID. On the Suggestions and rules page, set Suggestion source to SELECT Role FROM tblRole ORDER BY Role and turn off Free entry. Choose OK and save the form.

Open the form. Miller has the token “Reader”. Type au in the box and press Enter: “Author” is added, and tblUserRole gets a second row right away. Type Guest and press Enter: the value is rejected, and the reason appears below the box. Click the remove button on “Reader”: its row is deleted.

• Each token is a row in tblUserRole, written as soon as the token is added or removed; the tblUser record itself doesn't change.
• Only roles from tblRole are accepted, and no role can be added twice.
• On a new user record, the box accepts input once the record has been saved.

• How to: Keep several values of a record as tokens: ask for confirmation before the last role is removed, and show the roles as a column in a user list.
• API reference: WklTokenBox.