By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

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

beacon
100+
P: 579
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:
Expand|Select|Wrap|Line Numbers
  1. cmbControl.OnAction = "=MySubFunction()"
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Sub CreateReportShortcutMenu()
  5.  
  6.     Dim cmbReportShortcutMenu As Office.CommandBar
  7.     Dim cmbControl As Office.CommandBarControl
  8.  
  9.     'On Error Resume Next
  10.  
  11.     'Delete the command bar if it already exists
  12.     DeleteContextMenu
  13.  
  14.     ' Create the shortcut menu.
  15.     Set cmbReportShortcutMenu = CommandBars.Add("ReportShortcutMenu", msoBarPopup)
  16.  
  17.     With cmbReportShortcutMenu
  18.  
  19.         ' Add the Print command.
  20.         Set cmbControl = .Controls.Add(msoControlButton, 2521)
  21.         ' Change the caption displayed for the control.
  22.         cmbControl.Caption = "Quick Print"
  23.  
  24.         ' Add the One Page command.
  25.         Set cmbControl = .Controls.Add(msoControlButton, 5)
  26.  
  27.         ' Add the Two Pages command.
  28.         Set cmbControl = .Controls.Add(msoControlButton, 639)
  29.  
  30.         ' Add the Print command.
  31.         Set cmbControl = .Controls.Add(msoControlButton, 15948)
  32.         ' Change the caption displayed for the control.
  33.         cmbControl.Caption = "Select Pages"
  34.  
  35.         ' Add the Page Setup... command.
  36.         Set cmbControl = .Controls.Add(msoControlButton, 247)
  37.         ' Change the caption displayed for the control.
  38.         cmbControl.Caption = "Page Setup"
  39.  
  40.         ' Add the Mail Recipient (as Attachment)... command.
  41.         Set cmbControl = .Controls.Add(msoControlButton, 2188)
  42.         ' Start a new group.
  43.         cmbControl.BeginGroup = True
  44.         ' Change the caption displayed for the control.
  45.         cmbControl.Caption = "Email Report as an Attachment"
  46.  
  47.         ' Add the PDF or XPS command.
  48.         Set cmbControl = .Controls.Add(msoControlButton, 12499)
  49.         ' Change the caption displayed for the control.
  50.         cmbControl.Caption = "Save as PDF/XPS"
  51.  
  52.         ' Add the Close command.
  53.         Set cmbControl = .Controls.Add(msoControlButton, 923)
  54.         ' Start a new group.
  55.         cmbControl.BeginGroup = True
  56.         ' Change the caption displayed for the control.
  57.         cmbControl.Caption = "Close Report"
  58.  
  59.         '*******************************************************
  60.         '* If I was going to use a custom sub/function,
  61.         '* this an example of how I would at least start it
  62.         '*******************************************************
  63.  
  64.         ' Add a blank command
  65.         Set cmbControl = .Controls.Add(msoControlButton, 1)
  66.         cmbControl.Caption = "Email As PDF"
  67.         cmbControl.OnAction = "=MySubFunction()"
  68.  
  69.     End With
  70.  
  71.     Set cmbControl = Nothing
  72.     Set cmbReportShortcutMenu = Nothing
  73.  
  74. End Sub
  75.  
  76. Sub DeleteContextMenu()
  77.  
  78.     On Error Resume Next
  79.     CommandBars("ReportShortcutMenu").Delete
  80.  
  81. End Sub
  82.  
Thanks,
beacon
Apr 24 '12 #1

✓ answered by MikeTheBike

Hi

One trick I have used in a 'universal' sub is this
Expand|Select|Wrap|Line Numbers
  1. Sub ActiveObjectClose(control As IRibbonControl)
  2.     DoCmd.Close Application.CurrentObjectType, Application.CurrentObjectName, acSaveNo
  3.     DoCmd.Restore
  4. 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

Share this Question
Share on Google+
3 Replies


Expert 100+
P: 634
Hi

One trick I have used in a 'universal' sub is this
Expand|Select|Wrap|Line Numbers
  1. Sub ActiveObjectClose(control As IRibbonControl)
  2.     DoCmd.Close Application.CurrentObjectType, Application.CurrentObjectName, acSaveNo
  3.     DoCmd.Restore
  4. 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
Apr 25 '12 #2

NeoPa
Expert Mod 15k+
P: 31,492
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 :
Expand|Select|Wrap|Line Numbers
  1. Call Form_frmTest.PubProcIn_frmTest()
Apr 25 '12 #3

beacon
100+
P: 579
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Sub CreateReportShortcutMenu()
  5.  
  6.     Dim cmbReportShortcutMenu As Office.CommandBar
  7.     Dim cmbControl As Office.CommandBarControl
  8.  
  9.     'On Error Resume Next
  10.  
  11.     'Delete the command bar if it already exists
  12.     DeleteContextMenu
  13.  
  14.     ' Create the shortcut menu.
  15.     Set cmbReportShortcutMenu = CommandBars.Add("ReportShortcutMenu", msoBarPopup)
  16.  
  17.     With cmbReportShortcutMenu
  18.  
  19.         ' Add the Print command.
  20.         Set cmbControl = .Controls.Add(msoControlButton, 2521)
  21.         ' Change the caption displayed for the control.
  22.         cmbControl.Caption = "Quick Print"
  23.  
  24.         ' Add the One Page command.
  25.         Set cmbControl = .Controls.Add(msoControlButton, 5)
  26.  
  27.         ' Add the Two Pages command.
  28.         Set cmbControl = .Controls.Add(msoControlButton, 639)
  29.  
  30.         ' Add the Print command.
  31.         Set cmbControl = .Controls.Add(msoControlButton, 15948)
  32.         ' Change the caption displayed for the control.
  33.         cmbControl.Caption = "Select Pages"
  34.  
  35.         ' Add the Page Setup... command.
  36.         Set cmbControl = .Controls.Add(msoControlButton, 247)
  37.         ' Change the caption displayed for the control.
  38.         cmbControl.Caption = "Page Setup"
  39.  
  40.         ' Add the Mail Recipient (as Attachment)... command.
  41.         Set cmbControl = .Controls.Add(msoControlButton, 2188)
  42.         ' Start a new group.
  43.         cmbControl.BeginGroup = True
  44.         ' Change the caption displayed for the control.
  45.         cmbControl.Caption = "Email Report as an Attachment"
  46.  
  47.         ' Add the PDF or XPS command.
  48.         Set cmbControl = .Controls.Add(msoControlButton, 12499)
  49.         ' Change the caption displayed for the control.
  50.         cmbControl.Caption = "Save as PDF/XPS"
  51.  
  52.         ' Add the Close command.
  53.         Set cmbControl = .Controls.Add(msoControlButton, 923)
  54.         ' Start a new group.
  55.         cmbControl.BeginGroup = True
  56.         ' Change the caption displayed for the control.
  57.         cmbControl.Caption = "Close Report"
  58.  
  59.         '*******************************************************
  60.         '* I changed the .OnAction property below from my
  61.         '* original post, so please note the change.
  62.         '*******************************************************
  63.  
  64.         ' Add a blank command
  65.         Set cmbControl = .Controls.Add(msoControlButton, 1)
  66.         cmbControl.Caption = "Email As PDF"
  67.         cmbControl.OnAction = "=SendEmailAsPDFAttachment()"
  68.  
  69.     End With
  70.  
  71.     Set cmbControl = Nothing
  72.     Set cmbReportShortcutMenu = Nothing
  73.  
  74. End Sub
  75.  
  76. Public Sub DeleteContextMenu()
  77.  
  78.     On Error Resume Next
  79.     CommandBars("ReportShortcutMenu").Delete
  80.  
  81. End Sub
  82.  
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function SendEmailAsPDFAttachment()
  2.  
  3.     Dim strSubject As String, strBody As String
  4.  
  5.     On Error GoTo HandleError
  6.  
  7.     strSubject = "Test Subject"
  8.     strBody = "Test Body"
  9.  
  10.     DoCmd.SendObject acSendReport, Application.CurrentObjectName, acFormatPDF, , , , strSubject, strBody, True
  11.  
  12.     SendEmailAsPDFAttachment = True
  13.  
  14. HandleError:
  15.     If Err.Number = 2501 Then
  16.         'Do nothing
  17.     Else
  18.         MsgBox "Unexpected error: " & Err.Number & " - " & Err.Description
  19.     End If
  20.  
  21. End Function
  22.  
Thanks,
beacon
Apr 26 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.