How to: Keep several values of a record as tokens

Use WklTokenBox on a form to show and edit the short list of values that belong to a record (a user's roles, a document's tags) as tokens with a remove button, with suggestions as the user types. In a list, show the same values as a read-only column in WklGrid or WklTreeList.

Requires WKL Forms Object Library 1.33 or later.

• A child table holds the values, one row per token, just as for a subform; for example, tblUserRole with the fields UserID and Role. Use a child table instead of a multivalued field: multivalued fields exist only in Access tables, not in SQL Server or over ODBC.
• On a form: insert WKL Token Box (an ActiveX control). On the designer's Data page, set the child table (RecordSource), its value field (ValueField), and the link to the form's record (LinkChildFields, LinkMasterFields), just as you would for a subform control.
• Follow the record: set the control's Control Source (ControlSource) to the form's key field. Access then notifies the box of every record change, and the box loads that record's tokens.
• Suggestions and rules: on the Suggestions and rules page, set a value list (SuggestionList) or a table, query, or SQL statement (SuggestionSource), restrict input to suggested values (AllowFreeText = off), and add a regular expression with its message (Pattern, ValidationText).
• Saving: nothing to do. Each token the user adds inserts its child row immediately, and each token removed deletes its row immediately; the form's record doesn't change.
• In a list: a column with Editor = wedTokens, bound to a field that joins the child values into one text (Separator, ; by default), shows the same chips as read-only.

Create the sample tables once (run the procedure from the Immediate window):

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

A form frmUser is bound to tblUser (key ID) and contains a token box tokRoles; the available roles are in tblRole:

' Once, in design view (or on the designer's pages); saved with the form.
With Forms!frmUser!tokRoles
    .ControlSource = "ID"
    .Object.RecordSource = "tblUserRole"
    .Object.ValueField = "Role"
    .Object.LinkChildFields = "UserID"
    .Object.LinkMasterFields = "ID"
    .Object.SuggestionSource = "SELECT Role FROM tblRole ORDER BY Role"
    .Object.AllowFreeText = False
    .Object.Caption = "Roles"
End With

' Form module of frmUser: ask before the last role goes.
Private Sub tokRoles_TokenRemoving(ByVal Value As String, Cancel As Integer)
    If Me!tokRoles.Object.Count = 1 Then
        Cancel = (MsgBox("Without a role, the user can't sign in. Remove it anyway?", _
                         vbYesNo + vbQuestion) = vbNo)
    End If
End Sub

To list users with their roles as a column, use a query that joins each user's roles into one text (for example, with STRING_AGG in a SQL Server view):

With Me!lstUsers.Object
    .RecordSource = "vwUsersWithRoles"
    .KeyField = "ID"
    .Columns.Add "Name", "User"
    .Columns.Add("Roles", "Roles").Editor = wedTokens
End With

• Typing: the user enters a value and confirms it with Enter, a one-character separator, or by picking a suggestion (Down opens the list, and Enter takes the highlighted entry); leaving the box with text in it also adds the value. The first Esc closes an open list, as in an Access combo box, and the second clears the input. Backspace in an empty input selects the last token, and pressing it again removes that token.
• Rejected values (a duplicate, a value that isn't among the suggestions, a value that doesn't match Pattern, or Cancel in TokenAdding) stay in the input; the reason appears below the box and in ErrorText. No message box opens.
• A new record that hasn't been saved yet has no key: the box shows no tokens, and its input stays read-only until the record is saved, just as a subform waits for its parent record.
• Not editable: with Locked = Yes on the control or Allow Edits = No on the form, the box shows the tokens without remove buttons and rejects input; screen readers don't find any remove buttons either.
• No Undo. Removing a token deletes its row, just as in a subform. If a removal needs confirmation, ask in TokenRemoving.
• Back-end errors (a violated constraint, a missing table) are passed to DataError; set Response = 0 to suppress the message. A failed read doesn't show any message: the box stays empty, and DataError is the only notification.
• Code writes with AddToken, RemoveToken, or Text without raising the user events TokenAdding … Change. If you change the child table without going through the box, call Requery.
• Several boxes on one form read the child table independently: a box shows a change written by another box after the next record change or Requery.
• 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. To show tokens per record, use the list column.

• API reference: WklTokenBox, WklColumn (Editor, Separator)
• How to: Link to a master record
• How to: Use a composite key