Put a count on a button

In this tutorial, you put the number of open sessions on a button of a startup form, first in code and then bound to an expression, and you check that the button still responds to a click on the count.

• Microsoft 365 Access or the Access Runtime, 32-bit or 64-bit, with the WKL Forms Object Library 1.35 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, in the property sheet (F4), on the Other tab, in the Custom row, [...] (Opening the designer).

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

Public Sub CreateBadgeSample()
    With CurrentDb
        .Execute "CREATE TABLE tblSessions (ID LONG CONSTRAINT PK_Sessions PRIMARY KEY, CustomerID LONG, IsOpen YESNO)"
        .Execute "INSERT INTO tblSessions VALUES (1, 1, True)"
        .Execute "INSERT INTO tblSessions VALUES (2, 1, True)"
        .Execute "INSERT INTO tblSessions VALUES (3, 2, False)"
    End With
End Sub

Create a blank form frmStart in Design view. Add a command button, cancel the wizard, and name it cmdSessions with the caption &Sessions. Give it an event procedure for its Click event:

Private Sub cmdSessions_Click()
    MsgBox "Open sessions"
End Sub

Choose Form Design › Controls › ActiveX Controls › WKL Badge and draw it near the upper-right corner of the button, about the size of a small square. Name it badSessions (property sheet, Other tab, Name).

Open the designer: property sheet, Other tab, Custom row, [...]. On the Target page, choose cmdSessions as the target and Top right as the corner, type Open sessions under What is counted, and select Align to target. Choose OK: the badge jumps onto the corner of the button.

Add this code to the form module and switch to Form view:

Private Sub Form_Load()
    Me!badSessions.Object.Count = DCount("*", "tblSessions", "IsOpen = True")
End Sub

The button shows a red “2” on its corner. Click the “2”: the button's message box appears, because the click passes through the badge to the button.

Remove Form_Load, and set the badge's Control Source (property sheet, Data tab) to:

=DCount("*","tblSessions","IsOpen = True")

In the property sheet, Access separates the arguments with the Windows list separator: , with English (United States) regional settings, ; with German or Slovenian ones, for example. Switch to Form view: the count now comes from the expression, which Access calculates just as it does for a calculated text box.

• In Form view, the button shows “2” on its upper-right corner; in Design view, the badge stays where you drew it.
• A click on the count runs cmdSessions_Click; the Tab key never stops on the badge.
• With no open sessions (all IsOpen = No), the badge is hidden when the form opens.

• How to: Show a count on a button: a dot on a label, and colors per record from KindField.
• API reference: WklBadge: all properties and the value rules.