How to: Show a count on a button

Show how many items are waiting behind an Access button („3“ open sessions on Sessions, a red dot on Notices), the way Outlook and Teams mark unread items, without changing the button and without adding a second click target.

Requires WKL Forms Object Library 1.35 or later.

• WklBadge is a small ActiveX control. Put it next to the button, and set Target to the button's name and Corner to the corner. In Form view, it places itself on that corner and follows the button when code moves, resizes, or hides it.
• The value: set Count in code, or bind the badge through its Control Source to a field or an expression such as =DCount(...). Zero, Null, and an empty value hide the badge; above Max, it shows „99+“. Dot shows a mark without a number.
• Clicks and keyboard: a click on the badge reaches the button below it, and the badge isn't a tab stop. Screen readers announce „Sessions: 3“ with the full count.

The table for the example, created once (run the procedure from the Immediate window):

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

A form frmStart with the button cmdSessions and a badge badSessions drawn near its upper-right corner. You set the badge's properties once, in the property sheet (Other tab) or in code in Design view; for clarity, this example sets them in code in Form_Load:

Option Compare Database
Option Explicit
 
Private Sub Form_Load()
    With Me!badSessions.Object
        .Target = "cmdSessions"
        .Corner = wbcTopRight
        .AccessibleText = "Open sessions"
    End With
    UpdateCount
End Sub
 
Private Sub Form_Timer()
    UpdateCount        ' TimerInterval = 30000: every 30 seconds
End Sub
 
Private Sub UpdateCount()
    Me!badSessions.Object.Count = DCount("*", "tblSessions", "IsOpen = True")
End Sub

To bind the badge instead of using code, set its Control Source to the expression; Access calculates it with the form's current record:

' In design view, or in the property sheet: Control Source of badSessions
Me!badSessions.ControlSource = "=DCount(""*"",""tblSessions"",""IsOpen = True AND CustomerID = "" & [CustomerID])"

A dot on a label, in the warning color:

With Me!badNotices.Object
    .Target = "lblNotices"
    .Dot = True
    .Kind = wskWarn
End With

• In Design view, the badge stays where you drew it; it moves onto the target only in Form view. Draw it close to its final position so the design looks like the form.
• The target must be a control without its own window: a button, label, image, text box, or a WKL control. Access draws subforms over every ActiveX control, so a badge on a subform stays hidden; put it beside the subform instead.
• The badge doesn't work in the detail section of a continuous form, but it works in the form's header or footer. For a count per record, use a WklGrid column.
• A badge with Clickable = True takes clicks itself and raises Click; it's then a control in its own right, and its TabStop setting stays as Access set it.

• API reference: WklBadge: members, corners, and value rules.
• How to: Show counts on navigation entries: the same badge on WklNavBar entries.