Adding items to general context menu (not on a form!) (1 Viewer)

Blueskies · Mar 13, 2019

Hi there!

I don't imagine this is do-able, but just in case.....

Is it in any way possible to add items to the context (right click) menu outside of a form, just in Access generally?

The idea is, if I right click a table, I get a custom menu from which I can select to run a certain function on this table.

Anyone done anything like this at all????

Ranman256 · Mar 13, 2019

its easier to just build a menu. (buttons on a form)
you could enable buttons based on user ID.

essaytee · Mar 14, 2019

For starters, if you run the following it should display all the names of the menus. What might be of interest to you is named, "Table Datasheet". When I ran this code snippet it also revealed all my user-created right-click menu names.

Code:

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

I don't know if the system created menus are read-only or not, but assuming they're not, I assume this functionality would be for yourself. I can't understand allowing general users direct access to the table.

essaytee · Mar 14, 2019

This has certainly piqued my interest. Following on from my last post the following should assist.

Code:

Public Sub ListMenuCaptions()
                
    Dim i, k As Integer
    Dim strBarName As String
    Dim cmbRC As CommandBar
    
    For i = 1 To Application.CommandBars.Count
        strBarName = Application.CommandBars(i).Name
        Debug.Print strBarName
        Set cmbRC = CommandBars(strBarName)
        For k = 1 To cmbRC.Controls.Count
            Debug.Print "   " & cmbRC.Controls.Item(k).Caption
        Next
    Next

End Sub

essaytee · Mar 14, 2019

The menu of concern for you is "Table Design Datasheet Cell".

Code:

Public Sub ListMenuSubCaptions(pStrBar As String)
    Dim k As Integer
    Dim cmbRC As CommandBar
    
    Debug.Print pStrBar
    
    Set cmbRC = CommandBars(pStrBar)
    For k = 1 To cmbRC.Controls.Count
        Debug.Print "   " & cmbRC.Controls.Item(k).Caption
    Next
End Sub

When I run the above "ListMenuSubCaptions("Table Design Datasheet Cell") I get the following results.

Code:

Table Design Datasheet Cell
   Cu&t
   &Copy
   &Paste
   &Sort A to Z
   S&ort Z to A
   C&lear filter from Rego
   Text &Filters
   &Equals "1AD-6MU (m) 8AV"
   Does &Not Equal "1AD-6MU (m) 8AV"
   
   
   Con&tains "1AD-6MU (m) 8AV"
   &Does Not Contain "1AD-6MU (m) 8AV"
   
   
   ????
   ????
   
   
   Is S&elected
   Is &Not Selected
   Recon&vert
   Han&gul Hanja Conversion...
   Insert Ob&ject...
   Edit List &Items...
   &Object
   &Hyperlink
   S&how column history...
   &Manage Attachments...

Hope all this helps. I haven't tested adding any items to in-built controls but can certainly assist if need be.

Blueskies · Mar 14, 2019

Wow! this is more response than I expected.

I'll run through your suggestions and see what happens.

Thanks!

EDIT: I think it's 'Navigation Pane List Pop-up' I need to look at. To illustrate, it's this one:

It will only be for my own use - I would like to run a function which refers to the selected table.

Blueskies · Mar 14, 2019

OK, got this to work - it adds another cut command to the menu:

Code:

Dim cmbRC As CommandBar

Set cmbRC = CommandBars("Navigation Pane List Pop-up")
     
Dim newButton As CommandBarButton
     
Set newButton = cmbRC.Controls.add(msoControlButton, CommandBars("Edit").Controls("Cut").Id)

and this removes it again (loop helped me identify which one I needed to delete):

Code:

Dim cmbRC As CommandBar
Set cmbRC = CommandBars("Navigation Pane List Pop-up")
Dim ctl As CommandBarControl
Dim myControls As CommandBarControls

Set myControls = cmbRC.Controls

For Each ctl In myControls
Debug.Print ctl.Id & " : " & ctl.Index

Next ctl


CommandBars("Navigation Pane List Pop-up").Controls(25).Delete

Next task is - how do I run a custom function instead of just adding another 'Cut'?

essaytee · Mar 14, 2019

I've copied a snippet from a right-click menu (rcm) that I use in one of my apps, as follows:

Code:

    Set cmbOpenForm = cmbRC.Controls.Add(msoControlButton)
    cmbOpenForm.BeginGroup = True
    cmbOpenForm.Caption = "Clear Filter (show all records)"
    [COLOR="Blue"]cmbOpenForm.OnAction = "=frm_Address_Filter_rcm('Clear')"[/COLOR]

    Set cmbOpenForm = cmbRC.Controls.Add(msoControlButton)
    cmbOpenForm.Caption = "Hide"
    [COLOR="Blue"]cmbOpenForm.OnAction = "=frm_Address_Filter_rcm('Hide')"[/COLOR]

The lines in blue (.OnAction) are the relevant calls. They both call a public (global) function and pass in string arguments ("Clear" and "Hide"). That public function is listed below:

Code:

Public Function frm_Address_Filter_rcm(pStrWhat As String)
On Error GoTo Error_In_Code

    Dim strMsg As String
    Dim f As Form
   
    Set f = Screen.ActiveForm
        
    Select Case pStrWhat
        Case "Clear"
            Call f.cmd_Clear_Click
            
        Case "Hide"
            Call f.cmd_Hide_Click

    End Select
    
    Set f = Nothing
        
Exit_Code:
    Exit Function
    
Error_In_Code:
    strMsg = "frm_Address_Filter_rcm() - " & Err.Number & " " & Err.Description
    MsgBox strMsg
    Resume Exit_Code
        
End Function

Hope this helps.

Blueskies · Mar 14, 2019

OK, tried to post this before but site keeps going down. I'll get this in quick, then review your post next essaytee! - thanks for your help with this - much appreciated!!!

I've got a custom function to run as follows:

Code:

Sub add_menu_item()

     Dim cmbRC As CommandBar
     Set cmbRC = CommandBars("Navigation Pane List Pop-up")
     
     Dim newButton As CommandBarButton
     
     Set newButton = cmbRC.Controls.add(msoControlButton, , , , True)
          
     newButton.Caption = "Run my function"
     newButton.Tag = "Run my function"
     newButton.FaceId = 999
     
     newButton.OnAction = "=test_menu()"
     
End Sub

Public Function test_menu()
    
    MsgBox "Hello"
    
End Function

It looks like this:

and when selected gives me:

So I'm almost there now - just need to pick up the name of the selected table and I know I've seen some code to do that somewhere.....

Blueskies · Mar 14, 2019

Last piece of the puzzle. This is how you get the currently selected item:

Code:

Application.CurrentObjectName

All I need to do now is pass this to my 'hello' function and I'm off and running with whatever I need to do, so I think we're there!

Thanks again for your help essaytee - really made a difference.

essaytee · Mar 14, 2019

Blueskies said:
Last piece of the puzzle. This is how you get the currently selected item:

Code:

Application.CurrentObjectName

All I need to do now is pass this to my 'hello' function and I'm off and running with whatever I need to do, so I think we're there!

Thanks again for your help essaytee - really made a difference.

No worries, happy to help.

Adding items to general context menu (not on a form!) (1 Viewer)

Blueskies

Registered User.

Ranman256

Well-known member

essaytee

Need a good one-liner.

essaytee

Need a good one-liner.

essaytee

Need a good one-liner.

Blueskies

Registered User.

Blueskies

Registered User.

essaytee

Need a good one-liner.

Blueskies

Registered User.

Blueskies

Registered User.

essaytee

Need a good one-liner.

Similar threads

Users who are viewing this thread