Right click menu - type mismatch error Access 2010 (1 Viewer)

[essaytee]

I've been researching how to implement the Right-Click menu in Access 2010. I came across an example from StackOverflow that partially works. I'm getting a Type Mismatch error on the following line:

Set combo = .Controls.Add(Type:=msoControlButton)

When checking the debug session the value of msoControlButton = 1. I've checked my references and Microsoft Office 14.0 Object Library is checked and selected.

The line prior to the error occurring,

 Set combo = .Controls.Add(Type:=msoControlEdit)

works.

The full function is here:

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

Any ideas please?

Steve.

 

[Orthodox Dave]

I don't have experience of this, but looked it over and did some googling.

In your travels, did you come across this link:
https://msdn.microsoft.com/en-us/library/ee336038(v=office.12).aspx

If not, it must be worth a read as it's Microsoft from the horse's mouth.

 

[essaytee]

I don't have experience of this, but looked it over and did some googling.

In your travels, did you come across this link:
https://msdn.microsoft.com/en-us/library/ee336038(v=office.12).aspx

If not, it must be worth a read as it's Microsoft from the horse's mouth.

Thanks Orthodox Dave, I read that, and a heap of other sites and I finally got a working version. I tried modifying bits and pieces in the code I originally posted but if it wasn't one thing it was another; damn frustrating.

Anyway, for the sake of completeness here is code that works for Access 2010 (with lots of help from this site):

Sub CommandMenuTest()

    ' Note: This requires a reference to Microsoft Office Object Library
    Dim combo As CommandBarComboBox
    
    Dim cbr As CommandBar
    Dim cbrButton As CommandBarButton
    Dim cbrEdit As CommandBarControl
    
    ' 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("MyRightClick").Delete
    On Error GoTo 0
    
    ' Make this menu a popup menu
    Set cbr = CommandBars.Add("MyRightClick", msoBarPopup, False, False)
     
    Set cbrEdit = cbr.Controls.Add(msoControlEdit)
    cbrEdit.Caption = "Lookup Text:"
    cbrEdit.OnAction = "getText"
    
    Set cbrButton = cbr.Controls.Add(msoControlButton, , , , True)
    cbrButton.BeginGroup = True
    cbrButton.Caption = "Lookup Details:"
    cbrButton.OnAction = "LookupDetailsFunction"
    
    Set cbrButton = cbr.Controls.Add(msoControlButton, , , , True)
    cbrButton.Caption = "Delete Record"
    cbrButton.OnAction = "DeleteRecordFunction"

End Sub

It does work except for a couple of side-effects.

1. If run from the OnMouseUp event, all three items popup, all good so far. The problem is that after this event is fired, the default Access right click menu also fires.

2. Running directly from the ShortCut Menu Bar of a control, only two items popup, the Edit box doesn't.

I haven't investigated or looked into the above two problems, but I'm sure they're fixable.