How to: Use an Access shortcut menu bar in a grid

Put your application's commands, such as “Mark as done” or “Print packing slip,” on the right-click menu of a WklGrid, WklTreeList, or WklGantt, without a form module, the same way an Access form gets them from its Shortcut Menu Bar property.

Requires WKL Forms Object Library 1.28 or later.

Access forms take their shortcut menu from the ShortcutMenuBar property: the name of a pop-up command bar whose entries call a function (=Function()) or a macro. Access offers that property only for its own controls. The WKL controls have a property with the same name: the entries of the named bar appear in the control's shortcut menu, after the built-in entries by default (MenuPlacement). When the user chooses an entry, the control runs the entry's action just as Office does. The function finds the control through Screen.ActiveControl, and the record the menu was opened on through ContextKey (ContextColumn for the column, ContextView for a detail view).

None of this requires the form's module: a standard module creates the bar at startup, and the property is set in Design view.

1. In a standard module, create the bar when the application starts, and call the function from the AutoExec macro (the RunCode action, CreateOrderMenu()):

   Public Function CreateOrderMenu()
       On Error Resume Next
       CommandBars("mnuOrders").Delete
       On Error GoTo 0
       With CommandBars.Add("mnuOrders", 5, False, True)   ' 5 = msoBarPopup, temporary
           With .Controls.Add(1)                             ' 1 = msoControlButton
               .Caption = "Mark as &done"
               .OnAction = "=MarkOrderDone()"
           End With
           With .Controls.Add(1)
               .Caption = "&Print packing slip"
               .OnAction = "=PrintDeliveryNote()"
               .BeginGroup = True
           End With
       End With
   End Function

2. Write the functions the entries call, in the same standard module:

   Public Function MarkOrderDone()
       Dim grid As Object
       Set grid = Screen.ActiveControl.Object
       If IsNull(grid.ContextKey) Then Exit Function      ' opened on a group row
       CurrentDb.Execute "UPDATE tblOrders SET Done = True WHERE OrderID = " & grid.ContextKey, dbFailOnError
       grid.Requery
   End Function
    
   Public Function PrintDeliveryNote()
       DoCmd.OpenReport "rptPackingSlip", acViewPreview, , "OrderID = " & Screen.ActiveControl.Object.ContextKey
   End Function

3. Open the form in Design view, select the grid, and type mnuOrders in ShortcutMenuBar (property sheet, Other tab, or the Context menu section on the designer's Behavior page). Use MenuPlacement to choose whether the entries appear after the built-in ones (the default), before them, on their own, or not at all.

• The bar is read each time the menu opens. If you change an entry in code, for example CommandBars("mnuOrders").Controls(2).Enabled = False, the next menu reflects the change.
• The menu shows the bar's visible buttons and submenus with their caption, ShortcutText, enabled state, check mark (State), and separator (BeginGroup). Combo boxes, edit boxes, and pictures on a bar aren't shown.
• Detail views in a WklGrid show the same entries. ContextKey then holds the key of the detail record, and ContextView holds the name of the detail view; read both from the master grid (Screen.ActiveControl is the master).
• An entry whose function doesn't exist shows the Access message that the expression contains the name of a function Access can't find, just as for an entry on an Access bar; check the name in OnAction. An error the function doesn't handle shows the VBA run-time error dialog box, so give the function an error handler.
• The entries' captions are your own text; the built-in entries follow the components' language.
• With a form module, you can adjust the menu for each record in BeforeContextMenu, or add items with AddMenuItem instead of using a bar (How to: Add items to the shortcut menu).

• API reference: ShortcutMenuBar, MenuPlacement, ContextKey, BeforeContextMenu
• How to: Run the grid's commands from your own buttons
• WklGrid overview