How to: Fill a menu just before it opens

When a menu's entries depend on the current record (for example, „Suspend“ only for active customers, or the customer's most recent documents), fill the menu each time it opens.

Requires WKL Forms Object Library 1.26 or later.

WklButton raises DropDown just before its menu opens, whether by a click, a key, ShowMenu, or a screen reader. The event procedure clears the menu (ClearMenu) and adds the entries for the current record (AddMenuItem, AddMenuSeparator); MenuItemClick reports the user's choice. Changes made in Form view aren't saved with the form.

Private Sub btnActions_DropDown()
    Dim rs As DAO.Recordset
    With Me!btnActions.Object
        .ClearMenu
        .AddMenuItem "suspend", "&Suspend", "E72E", Enabled:=Me!Active, Reason:="This customer is already suspended."
        .AddMenuItem "mail", "Send &email", "E715", "Ctrl+M"
        .AddMenuItem "documents", "&Documents"
        Set rs = CurrentDb.OpenRecordset("SELECT TOP 5 DocumentID, Title FROM tblDocuments WHERE CustomerID = " & Me!CustomerID & " ORDER BY DocDate DESC")
        Do Until rs.EOF
            .AddMenuItem "doc" & rs!DocumentID, rs!Title, Parent:="documents"
            rs.MoveNext
        Loop
        rs.Close
    End With
End Sub
 
Private Sub btnActions_MenuItemClick(ByVal Key As String)
    If Key Like "doc*" Then
        DoCmd.OpenForm "frmDocument", WhereCondition:="DocumentID = " & Mid$(Key, 4)
    ElseIf Key = "suspend" Then
        Me!Active = False
    End If
End Sub

• An entry with entries below it („Documents“) opens its submenu and is never reported itself; if the submenu has no entries, the entry is shown without an arrow, and choosing it raises MenuItemClick.
• The shortcut text („Ctrl+M“) is for display only; handle the key in Form_KeyDown with KeyPreview.
• A disabled entry shows its reason as a tooltip when the pointer rests on it.
• If the menu is still empty after DropDown, it doesn't open.

• WklButton overview
• API reference: AddMenuItem, DropDown