How to create a custom shortcut menu command

[bounthong]

Dear friends,
I am using MS Access 2022 (office 365), I am having trouble with creating shortcut menu command for report. I tried to follow the same thing posted by Beacon (How to create a custom shortcut menu command that identifies the calling form/report?), I used MS Office 16.0 object library as reference. The code causes no error, but when on use right mouse click on the form/report that call the sub creating the custom short cut menu, it shows only the standard shortcut menu of ms access. The code posed as said above is here below. Please help to advise.

Sub CreateReportShortcutMenu()
 
    Dim cmbReportShortcutMenu As Office.CommandBar
    Dim cmbControl As Office.CommandBarControl
 
    'On Error Resume Next
 
    'Delete the command bar if it already exists
    DeleteContextMenu
 
    ' Create the shortcut menu.
    Set cmbReportShortcutMenu = CommandBars.Add("ReportShortcutMenu", msoBarPopup)
 
    With cmbReportShortcutMenu
 
        ' Add the Print command.
        Set cmbControl = .Controls.Add(msoControlButton, 2521)
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Quick Print"
 
        ' Add the One Page command.
        Set cmbControl = .Controls.Add(msoControlButton, 5)
 
        ' Add the Two Pages command.
        Set cmbControl = .Controls.Add(msoControlButton, 639)
 
        ' Add the Print command.
        Set cmbControl = .Controls.Add(msoControlButton, 15948)
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Select Pages"
 
        ' Add the Page Setup... command.
        Set cmbControl = .Controls.Add(msoControlButton, 247)
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Page Setup"
 
        ' Add the Mail Recipient (as Attachment)... command.
        Set cmbControl = .Controls.Add(msoControlButton, 2188)
        ' Start a new group.
        cmbControl.BeginGroup = True
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Email Report as an Attachment"
 
        ' Add the PDF or XPS command.
        Set cmbControl = .Controls.Add(msoControlButton, 12499)
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Save as PDF/XPS"
 
        ' Add the Close command.
        Set cmbControl = .Controls.Add(msoControlButton, 923)
        ' Start a new group.
        cmbControl.BeginGroup = True
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Close Report"
 
        '*******************************************************
        '* If I was going to use a custom sub/function,
        '* this an example of how I would at least start it
        '*******************************************************
 
        ' Add a blank command
        Set cmbControl = .Controls.Add(msoControlButton, 1)
        cmbControl.Caption = "Email As PDF"
        cmbControl.OnAction = "=MySubFunction()"
 
    End With
 
    Set cmbControl = Nothing
    Set cmbReportShortcutMenu = Nothing
 
End Sub
 
Sub DeleteContextMenu()
 
    On Error Resume Next
    CommandBars("ReportShortcutMenu").Delete
 
End Sub

[NeoPa]

I won't delete this straight off, in case the reason it's been done so poorly is because of language issues.

However, This is not a valid question. It's a request for someone to do your work for you. You show no signs even of having started to look at what might be going wrong, or even if the code is supposed to do what you want. There is also no clear explanation of exactly what that is. You have code; you have a requirement; clearly these don't match but nor is either explained. What's your requirement? What does the code do that doesn't match that?

[bounthong]

Hi NeoPa,

It is correct that my English is poor, anyway I will try to be more precise and compact this time.

1. Requirement: I want to have Custom Shortcut Menu for my report.
2. Current situation: I have used the code shown below here in this post (initially posted by Beacon, shown also in my initial post). I have tried by calling this CreateReportShortcutMenu (shown below) from by my report, but it does not work. Right mouse click on my report in report preview mode or report view mode, only standard shortcut menu of msaccess appears.

I need help for making the custom shortcut menu worked/appeared.

Hope my English is understandable this time.

Here is the code I use:

Sub CreateReportShortcutMenu()
 
    Dim cmbReportShortcutMenu As Office.CommandBar
    Dim cmbControl As Office.CommandBarControl
 
    'On Error Resume Next
 
    'Delete the command bar if it already exists
    DeleteContextMenu
 
    ' Create the shortcut menu.
    Set cmbReportShortcutMenu = CommandBars.Add("ReportShortcutMenu", msoBarPopup)
 
    With cmbReportShortcutMenu
 
        ' Add the Print command.
        Set cmbControl = .Controls.Add(msoControlButton, 2521)
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Quick Print"
 
        ' Add the One Page command.
        Set cmbControl = .Controls.Add(msoControlButton, 5)
 
        ' Add the Two Pages command.
        Set cmbControl = .Controls.Add(msoControlButton, 639)
 
        ' Add the Print command.
        Set cmbControl = .Controls.Add(msoControlButton, 15948)
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Select Pages"
 
        ' Add the Page Setup... command.
        Set cmbControl = .Controls.Add(msoControlButton, 247)
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Page Setup"
 
        ' Add the Mail Recipient (as Attachment)... command.
        Set cmbControl = .Controls.Add(msoControlButton, 2188)
        ' Start a new group.
        cmbControl.BeginGroup = True
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Email Report as an Attachment"
 
        ' Add the PDF or XPS command.
        Set cmbControl = .Controls.Add(msoControlButton, 12499)
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Save as PDF/XPS"
 
        ' Add the Close command.
        Set cmbControl = .Controls.Add(msoControlButton, 923)
        ' Start a new group.
        cmbControl.BeginGroup = True
        ' Change the caption displayed for the control.
        cmbControl.Caption = "Close Report"
 
        '*******************************************************
        '* If I was going to use a custom sub/function,
        '* this an example of how I would at least start it
        '*******************************************************
 
        ' Add a blank command
        Set cmbControl = .Controls.Add(msoControlButton, 1)
        cmbControl.Caption = "Email As PDF"
        cmbControl.OnAction = "=MySubFunction()"
 
    End With
 
    Set cmbControl = Nothing
    Set cmbReportShortcutMenu = Nothing
 
End Sub
 
Sub DeleteContextMenu()
 
    On Error Resume Next
    CommandBars("ReportShortcutMenu").Delete
 
End Sub

[MikeTheBike]

Hi All

I have never used 365 so this may be irrelevant, but I suspect not.

Have you set the Shortcut Menu Bar property for the Form or Control in the properties Other Tab.

The code creating the shortcuts will need running ij the session before they appear inthe list for selection.

HTH

MTB

[NeoPa]

Hi bounthong.

That is somewhat better. Clearly there is little understanding of what the code does so that would be harder to explain.

By the way - if you have a link to the original thread that MTB was involved in you may post it in here so others may view it in case that can help. While hijacks are not allowed, links to other threads certainly are.

@MTB.

Not sure I understand you correctly. Are you saying the code must be run before the db designer can design it into their Report object? That seems awkward at best.

[bounthong]

Hi NeoPa,

I have tried different ways, including calling the sub that creating short cut menu in form_load event. As said, there was no error message from the system, it looks like everything is correct. But when the the form is open, right mouse click shows pop-up with standard short cut menu of ms access but not the expected one that should have been created by the sub mentioned.

Anyway, I have resolved my problem already with another method. I disabled all menus and short cut menu, and created custom ribbons for my purpose instead.

Thanks.

[MikeTheBike]

Hi NeoPa

Yes, in my experience since the days of office 97 to before ribbons were forced on us in 2007, Toolbars created in code don't seem to exist until the code creating them has run, which makes sense. Although toolbars so created will now appear on an Addin tab. I now define my own ribbons not toolbars, but the short cut menus still work as the y did pre-ribbons. It is inconvenient having to run the creating code before they appear in the design drop list, but if you know the designated name/title of the shortcut to be created you can type it in (I believe!).

MTB

[NeoPa]

Hi Mike.

I'm afraid I still take advantage of the old CommandBars and CommandBarButtons which, as you say, still work and can be found within the AddIn Toolbar^H^H^H^H^H^H^HRibbon.