Access custom right click filter menus

Question

I want to create custom right click filter menus in Access. I got code that does that, it's below

here's the problem. obviously, a field can be text or numbers. the default Access menu deals with that by creating a group Number Filters or Text Filters. But my filter doesn't have those groups, and, more importantly, doesn't look at the field type and doesn't hide irrelevant menus like the native one does. In the native one, it seems that they look at the field type, and, based on that, show TEXT FILTERS or NUMBER FILTERS

how do i do that without doing horrible things like program OnClick of every control and reload the menu based on the field type? like, is there a way to mimic what Access does? Hide irrelevant menus or show a different group based on field type

    Public Sub sbFormsShortcutMenu()

Dim cmbRightClick As Office.CommandBar
Dim cmbControl As Office.CommandBarControl

On Error Resume Next

CommandBars("MainRightClick").Delete
Set cmbRightClick = CommandBars.Add("MainRightClick", msoBarPopup, False, True) 'NEW COMMANDBAR
 
With cmbRightClick
    
        Set cmbControl = .Controls.Add(msoControlButton, 21, , , True) ' Cut
        Set cmbControl = .Controls.Add(msoControlButton, 19, , , True) ' Copy
        Set cmbControl = .Controls.Add(msoControlButton, 22, , , True) ' Paste 
        Set cmbControl = .Controls.Add(msoControlButton, 210, , , True) 'Sort AZ 10068
        cmbControl.BeginGroup = True
        Set cmbControl = .Controls.Add(msoControlButton, 211, , , True) 'Sort ZA 10068
        Set cmbControl = .Controls.Add(msoControlButton, 10068, , , True) 'FilterEqualsSelection 10068
        cmbControl.BeginGroup = True
        Set cmbControl = .Controls.Add(msoControlButton, 10071, , , True) 'FilterNotEqualsSelection 10071
        Set cmbControl = .Controls.Add(msoControlButton, 10090, , , True) 'FilterBeginsWithSelection 10090
        Set cmbControl = .Controls.Add(msoControlButton, 12265, , , True) 'FilterDoesNotBeginsWithSelection 12265
        Set cmbControl = .Controls.Add(msoControlButton, 10076, , , True) 'FilterContainsSelection 10076
        Set cmbControl = .Controls.Add(msoControlButton, 10089, , , True) 'FilterDoesNotContainSelection 10089
        Set cmbControl = .Controls.Add(msoControlButton, 10091, , , True) 'FilterEndsWithSelection 10091
        Set cmbControl = .Controls.Add(msoControlButton, 12266, , , True) 'FilterDoesNotEndWithSelection 12266 
        Set cmbControl = .Controls.Add(msoControlButton, 10095, , , True) 'FilterSmallerThanSelection 10095
        Set cmbControl = .Controls.Add(msoControlButton, 10094, , , True) 'FilterLargerThanSelection 10094
        Set cmbControl = .Controls.Add(msoControlButton, 10062, , , True) 'FilterBetween 10062 
        Set cmbControl = .Controls.Add(msoControlButton, 640, , , True) 'FilterBySelection 640
        Set cmbControl = .Controls.Add(msoControlButton, 3017, , , True) 'FilterExcludingSelection 3017

End With

    Set cmbControl = Nothing
    Set cmbRightClick = Nothing
        
End Sub

Answer 1

Check the following code

Public Function sbFormsShortcutMenu()  'Make it function not sub

    Dim cmbRightClick As Office.CommandBar
    Dim cmbControl As Office.CommandBarControl

    On Error Resume Next

    CommandBars("MainRightClick").Delete
    Set cmbRightClick = CommandBars.Add("MainRightClick", msoBarPopup, False, True) 'NEW COMMANDBAR

    With cmbRightClick

        Set cmbControl = .Controls.Add(msoControlButton, 21, , , True) ' Cut
        Set cmbControl = .Controls.Add(msoControlButton, 19, , , True) ' Copy
        Set cmbControl = .Controls.Add(msoControlButton, 22, , , True) ' Paste'
        Set cmbControl = .Controls.Add(msoControlButton, 210, , , True) 'Sort AZ 10068
        cmbControl.BeginGroup = True
        Set cmbControl = .Controls.Add(msoControlButton, 211, , , True) 'Sort ZA 10068
        Set cmbControl = .Controls.Add(msoControlButton, 10068, , , True) 'FilterEqualsSelection 10068
        cmbControl.BeginGroup = True
        Set cmbControl = .Controls.Add(msoControlButton, 10071, , , True) 'FilterNotEqualsSelection 10071
        If IsNumeric(Screen.ActiveForm.ActiveControl) then  'Check if numeric add numeric options and if not add text options
            Set cmbControl = .Controls.Add(msoControlButton, 10095, , , True) 'FilterSmallerThanSelection 10095
            Set cmbControl = .Controls.Add(msoControlButton, 10094, , , True) 'FilterLargerThanSelection 10094
            Set cmbControl = .Controls.Add(msoControlButton, 10062, , , True) 'FilterBetween 10062
        Else
            Set cmbControl = .Controls.Add(msoControlButton, 10076, , , True) 'FilterContainsSelection 10076
            Set cmbControl = .Controls.Add(msoControlButton, 10089, , , True) 'FilterDoesNotContainSelection 10089                
        End If
        Set cmbControl = .Controls.Add(msoControlButton, 640, , , True) 'FilterBySelection 640
        Set cmbControl = .Controls.Add(msoControlButton, 3017, , , True) 'FilterExcludingSelection 3017

    End With

    Set cmbControl = Nothing
    Set cmbRightClick = Nothing
    
End Function

Then use =sbFormsShortcutMenu() in each "On Mouse Down" event of your controls on the form. This would make the solution general for any form and any control on it for Numeric and Text type controls. Of course you could extend it more to check for Date type controls as well and update the menu accordingly :)

Edit: It is not working correctly if the form is used as subform.