How to add a menu item to the default right click

Question

The goal is to create menus which can be utilized with certain controls on an MS Access form and to be able to right click on a that control, for example on a listbox and a relevant context specific menu popup with options, which if clicked, would trigger a predefined subroutine or function.

What is the best method to accomplish this programmatically?

I am using MS Access 2003 and would like to do this using VBA.

Answer 1

In order to replace the default shortcut menu with a menu that includes the default actions plus your custom actions, you have to create a custom shortcut menu that includes the default actions. There is no way to extend the default shortcut menu.

Shortcut menus in Access 2003 and before are a special kind of toolbar. You create them the same way (more or less) that you create a custom toolbar. The UI is kind of weird, though, as there's a special place where you create them.

To get started, right click the toolbar in your front-end Access MDB. Choose CUSTOMIZE. In the list of Toolbars, check off SHORTCUT MENUS. This will give you a list of all the built-in shortcut menus, except that they don't actually end up looking like that in real use. For instance, if right click on a form, you get this shortcut menu:

Form Design
Datasheet View
PivotTable View
PivotChart View
Filter By Form
Apply Filter/Sort
Remove Filter/Sort
Cut
Copy
Paste
Properties

Now, where is this menu on the shortcut menu? Well, this one happens to be the FORM VIEW TITLE BAR menu, even though it pops up any time you click anywhere other than on a control on the form. So, if that's the menu you want to alter, you could edit it by adding menu items to it (a drag-and-drop operation).

I think it's actually better (as I said above) to create a custom shortcut menu that replicates the built-in menu and add your enhancements because this allows you to retain the Access default shortcut menu while also having your customized version of it for use when you want it. In that case, you'd need to start a new shortcut menu, and here's where the UI is weird:

You click on the last choice on the shortcut menu, CUSTOM. You see it drops down a placeholder. You can't drag/drop to it. Instead, you have to click NEW in the main toolbar editing window and create a new shortcut toolbar (give it the name you want your custom shortcut menu to have). Your new toolbar now shows up in the list of toolbars. Highlight it and click PROPERTIES, and change the type to POPUP. This will give you an informative warning that this alteration changes it from a toolbar to a shortcut menu. You can then close your toolbar/shortcut menu's properties sheet, and now if you check off SHORTCUT MENUS again and look at the CUSTOM menu, you'll see your newly-created menu. Now you can drag and drop the menu items for the built-in menu to your new menu -- but don't drop them on the menu itself, but on the placeholder in the flyout from the > to the right of the menu name.

You can then drag and drop any options you want from any menus or toolbars to your custom menu.

I assume you know how to use the shortcut menu, as it's part of the properties sheet of all form objects.

UPDATE 2009/05/21: The official Access 2007 blog just posted an article on doing this programmatically in Access 2007. Because of the ribbon interface, there are going to be differences, but some things will be the same.

Answer 2

Try This

Sub Add2Menu()
  Set newItem = CommandBars("Form View Popup").Controls.Add(Type:=1)
  With newItem
    .BeginGroup = True
    .Caption = "Make Report"
    .FaceID = 0
    .OnAction = "qtrReport"
  End With
End Sub

As you can see it will add item in "Form View Popup" Command Bar and when this item is clicked it will load procedure qtrReport

And use this function to see all Commandbars in Access

Sub ListAllCommandBars()
For i = 1 To Application.CommandBars.Count
    Debug.Print Application.CommandBars(i).Name
Next
End Sub

Answer 3

First create an _MouseUp event to execute on the respective control looking to see if the right mouse button was clicked and if so, call the .ShowPopup method.

Of course this assumes the

Private Sub MyListControlName_MouseUp(ByVal Button As Integer, _
                                      ByVal Shift As Integer, _
                                      ByVal X As Long, ByVal Y As Long)

  ' Call the SetUpContextMenu function to ensure it is setup with most current context
  ' Note: This really only needs to be setup once for this example since nothing is 
  ' changed contextually here, but it could be further expanded to accomplish this
  SetUpContextMenu  
  ' See if the right mouse button was clicked
  If Button = acRightButton Then
    CommandBars("MyListControlContextMenu").ShowPopup
  End If
End Sub

Since at this point the Command Bar MyListControlContextMenu is undefined, I define the Menu in a separate module as follows:

Public Sub SetUpContextMenu()
  ' Note: This requires a reference to Microsoft Office Object Library
    Dim combo As CommandBarComboBox

    ' Since it may have been defined in the past, it should be deleted, 
    ' or if it has not been defined in the past, the error should be ignored

    On Error Resume Next 
    CommandBars("MyListControlContextMenu").Delete
    On Error GoTo 0

    ' Make this menu a popup menu 
    With CommandBars.Add(Name:="MyListControlContextMenu", Position:=msoBarPopup)

    ' Provide the user the ability to input text using the msoControlEdit type
    Set combo = .Controls.Add(Type:=msoControlEdit)
        combo.Caption = "Lookup Text:"           ' Add a label the user will see
        combo.OnAction = "getText"               ' Add the name of a function to call

    ' Provide the user the ability to click a menu option to execute a function    
    Set combo = .Controls.Add(Type:=msoControlButton)
        combo.BeginGroup = True                  ' Add a line to separate above group
        combo.Caption = "Lookup Details"         ' Add label the user will see
        combo.OnAction = "LookupDetailsFunction" ' Add the name of a function to call

    ' Provide the user the ability to click a menu option to execute a function        
    Set combo = .Controls.Add(Type:=msoControlButton)
        combo.Caption = "Delete Record"          ' Add a label the user will see
        combo.OnAction = "DeleteRecordFunction"  ' Add the name of the function to call

  End With

End Sub 

Since three function have been referenced, we can move on to define these as follows-

getText: Note, this option requires a reference to both the name of the Command Bar menu name as well as the name of the control caption.

Public Function getText() As String

   getText = CommandBars("MyListControlContextMenu").Controls("Lookup Text:").Text

   ' You could optionally do something with this text here, 
   ' such as pass it into another function ...
   MsgBox "You typed the following text into the menu: " & getText

End Function

LookupDetailsFunction: For this example, I will create a shell function and return the text "Hello World!".

Public Function LookupDetailsFunction() As String

   LookupDetailsFunction = "Hello World!"

   MsgBox LookupDetailsFunction, vbInformation, "Notice!"

End Function

DeleteRecordFunction: For this example, I will ensure the control is still valid by checking it against null, and if still valid, will execute a query to remove the record from a table.

Public Function DeleteRecordFunction() As String

   If Not IsNull(Forms!MyFormName.Controls("MyListControlName").Column(0)) Then
     Currentdb.Execute _
      "DELETE * FROM [MyTableName] " & _
      "WHERE MyKey = " & Forms!MyFormName.Controls("MyListControlName").Column(0) & ";"
     MsgBox "Record Deleted", vbInformation, "Notice!"
   End If

End Function

Note: For LookupDetailsFunction, DeleteRecordFunction and getText functions, these must be within a public scope to work correctly.

Finally, the last step is to test the menu. To do this, open the form, right click on the list control and select one of the options from the popup menu.

Optionally button.FaceID can be utilized to indicate a known office icon to associate with each instance of the menu popup control.

I found Pillai Shyam's work on creating a FaceID Browser Add-In to be very helpful.

References: Microsoft FaceID