How to create a custom shortcut menu command that identifies the calling form/report?

[beacon]

Hi everybody,

I'm using Access 2010, but the format for the database is .mdb because I'm not ready to fully convert it to Access 2010 and the .accdb format.

I've been reading up on how to create Shortcut Menus in Access 2010 using VBA and I'm pretty confident I understand how most of it works...meaning that I can create a custom shortcut menu, register it (for lack of a better term) so it displays in a form's/report's shortcut menu property dropdown, and have it display the commands.

However, I'm at a point now where I would like to create a custom sub/function and attach it to a command to be included on the shortcut menu for one or more forms/reports. I can create a custom sub/function and I know that I can attach it to a command button using the following:

cmbControl.OnAction = "=MySubFunction()"
 

What I don't know how to do is to setup the sub/function so that it knows which form/report is calling the sub/function so that the name of the form/report can be used in the sub/funtion.

For instance, one of the things I'd like to do is modify the "Email as Attachment" command button so that it will always send as PDF instead of prompting the user for a list of choices (like .xps, .xlsx, etc.). If I was writing this as a private sub/function for a specific form/report, I would use Me.Name, but that won't work if I create the sub/function in a module (only public subs/function can be used in a module, right??).

In case it's needed, here's the code I'm using to create my custom shortcut menu, which I got from the Access Blog article written by Edwin Blancovitch:

Option Compare Database
Option Explicit
 
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
 

Thanks,
beacon

[MikeTheBike]

Hi

One trick I have used in a 'universal' sub is this

Sub ActiveObjectClose(control As IRibbonControl)
    DoCmd.Close Application.CurrentObjectType, Application.CurrentObjectName, acSaveNo
    DoCmd.Restore
End Sub

This snipit is in a ribbon callback sub (runnning on 2k7 but an mdb format file), but it also works in 2k3 (without the ribbon bit!).

Don't know if it will help you but it just might as the active object should be the object subject to the right click (or its parent!?).

Obviously I haven't checked this as I run it from a menu (or, unfortunately, a Ribbon button).


MTB

[NeoPa]

Beacon:
What I don't know how to do is to setup the sub/function so that it knows which form/report is calling the sub/function so that the name of the form/report can be used in the sub/funtion.

You seem to be setting things up to be called from a command button on a menu. As such, determining which form or report they are called from doesn't make sense. They are called from the menu (and specifically the Command you've set up).

Beacon:
(only public subs/function can be used in a module, right??).

Only Public procedures can be called from outside of the module itself, if that's what you mean. Look up 'Scope' for a fuller understanding of this.

NB. Although standard modules are probably more appropriate for such things generally, I have no clear idea what you're trying to achieve, so I will mention, in case it helps, that Public procedures in Form/Report modules can also be accessed generally, but from outside themselves must be referred to with the full module name as prefix. EG :

Call Form_frmTest.PubProcIn_frmTest()

[beacon]

Thanks for the replies. Mike's suggestion actually led me to a solution, which I will provide below for anyone that wants to create a custom shortcut (right-click) menu in Access 2010 and wants to use a custom function attached to a command/control button. I may have caused confustion by referring to the button as a command button previously...after looking at the original example, I think it's called a control button since they are referenced as msoControlButton when adding the control.

Here's the CreateReportShortcutMenu and DeleteContextMenu code that is used to test for an existing shortcut menu of a certain name, delete it if it exists, and create the shortcut menu. The CreateReportShortcutMenu function has to be run once from the Immediate Window to register the shortcut menu so it will appear in the Shortcut menu dropdown in a form/report:

Option Compare Database
Option Explicit
 
Public 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"
 
        '*******************************************************
        '* I changed the .OnAction property below from my
        '* original post, so please note the change.
        '*******************************************************
 
        ' Add a blank command
        Set cmbControl = .Controls.Add(msoControlButton, 1)
        cmbControl.Caption = "Email As PDF"
        cmbControl.OnAction = "=SendEmailAsPDFAttachment()"
 
    End With
 
    Set cmbControl = Nothing
    Set cmbReportShortcutMenu = Nothing
 
End Sub
 
Public Sub DeleteContextMenu()
 
    On Error Resume Next
    CommandBars("ReportShortcutMenu").Delete
 
End Sub
 

I created the custom function SendEmailAsPDFAttachment and used Mike's suggestion to use the Application object to identify the current object, which in my case, is the name of the form/report where the control button is being pressed on the shortcut menu. As I may use this shortcut menu on multiple forms/reports, I need it to not be specific to any one form/report:

Public Function SendEmailAsPDFAttachment()
 
    Dim strSubject As String, strBody As String
 
    On Error GoTo HandleError
 
    strSubject = "Test Subject"
    strBody = "Test Body"
 
    DoCmd.SendObject acSendReport, Application.CurrentObjectName, acFormatPDF, , , , strSubject, strBody, True
 
    SendEmailAsPDFAttachment = True
 
HandleError:
    If Err.Number = 2501 Then
        'Do nothing
    Else
        MsgBox "Unexpected error: " & Err.Number & " - " & Err.Description
    End If
 
End Function
 

Thanks,
beacon