Access custom right-click menus-CodePudding

I have written an app that makes extensive use of custom right-click menus on an Access form. The code works great and the user loves it, but lately I am having trouble making it work properly.

In earlier versions of Access it worked well, but newer version seem much more limited in how many items can be put in such menus. The documentation is silent on the matter, and nobody in any newsgroup has had any useful ideas, but I regularly get random error about stack space, out of memory, and general lockups when populating the menus. Doing a C&R used to help, but now even that is not enough, and some menus I can no longer populate at all.

I tried building an app that just built menus until it crashed, to get some idea of what the limits may be, and I am well below what that indicated, but the experimental app had nothing else, while the real app has a great deal else.

Is there any information on how much stuff can be put into these menus, and what the menus share space with? There may be something I can do another way to make more room for the menus. I tried moving all code out of the form, leaving only event stubs that called routines in standard code modules, but that did not help.

And how are they stored/activated? The app is MUCH slower to load when it has these menus, even though no code is running on start-up.

********** Edited to add this:

I use VBA to create a menu, like this:

Application.CommandBars.Add "RCStat", msoBarPopup, False, False

then add it to a control. like this:

Application.CommandBars.cboStat.ShortcutMenuBar = "RCStat"

I add controls (only popups and buttons) like this:

Application.CommandBars.Controls.Add(type:=msoControlPopup)
Application.CommandBars.Controls.Add(type:=msoControlButton, Parameter:="StatKod = 77")

It runs perfectly and the menu items work exactly as expected, except that it bombs after adding some number of controls. It doesn't seem to matter where I add them, just the total number of added controls hits some undefined threshold, and the app crashes.

I got the original code from Getz, Litwin and Gilbert, 2000 edition. Back then, it worked great. But as the versions advance and the app accumulates data, it is becoming less and less functional. However, there are only around 10,000 records, and the app itself is less than 100MB - nowhere close to any of Access's upper size limits.

CodePudding user response：

To be honest, I have never edited a menu. At some stage I had to create a dynamic menu to filter form data based on a recordset and I would always delete and re-create the menu.

See below a simplified version of the method I was using without any issues for quite sometime.

Private Const MENU_NAME As String = "CustomContextMenu"

The control which raises the event:

Private Sub ControlName_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If Button = acRightButton Then
        If Not SetCustomContextMenu() Then Exit Sub
        CommandBars(MENU_NAME).ShowPopup
        DoCmd.CancelEvent
    End If
End Sub

The function that creates the menu:

Public Function SetCustomContextMenu() As Boolean
    
    'Delete previous menu
    On Error Resume Next
    CommandBars(MENU_NAME).Delete
    On Error GoTo Trap

    'Dummy values
    Dim arr As Variant
        arr = Array("One", "Two", "Three")

    'Requires a reference to Microsoft Office Object Library
    Dim combo As CommandBarControl
    
    'Make this menu a pop-up menu
    With CommandBars.Add(Name:=MENU_NAME, Position:=msoBarPopup)
        
        'header
        Set combo = .Controls.Add(Type:=msoControlButton)
            combo.BeginGroup = True     'Add a line
            combo.FaceId = 601
            combo.Caption = "Dummy Values"
            combo.Enabled = False
        Set combo = Nothing
        
        Dim idx As Integer
        For idx = LBound(arr) To UBound(arr)
            Set combo = .Controls.Add(Type:=msoControlButton)
            combo.Caption = arr(idx)
            combo.OnAction = "=DummyMenuMethod('" & arr(idx) & "')"
            Set combo = Nothing
        Next idx
    End With
    
    'all good
    SetCustomContextMenu = True
    
Leave:
    On Error GoTo 0
Exit Function

Trap:
    MsgBox Err.Description, vbCritical, " SetCustomContextMenu()"
    Resume Leave
End Function

The function the menu calls:

Public Function DummyMenuMethod(ByVal value_ As String)
    MsgBox "You clicked option: " & value_
End Function

The output:

CodePudding user response：

We have a commercial product, Total Access Components, that includes as one of its 30 components a right click popup menu that can include icons and font styles.

Here's the info for the popup menu control: https://fmsinc.com/MicrosoftAccess/controls/components/popup-menu/

There's a free trial if you want to try it.