Turn off Shortcut Menu on Reports

[Snowflake68]

I am trying to find a way of disabling the shortcut menu on reports using Access 2013. There is a property called 'Shortcut Menu' on forms that allows you to set it to 'No' which switches them off but this is missing from the reports property and only allows you to specify the name of custom shortcut menu.

I do not want to untick 'Allow Default Shortcut Menus' via the system options for the Current Database as this will switch them off for everything and I need them on for some forms.

I did consider creating a custom shortcut menu with an empty menu but there doesnt seem to be a lot of help out there for creating custom shortcut menus in Access 2013. The only thing I found was some VBA that needed to reference an object library reference but I would like to avoid this if I can because there are many users of my system and this has caused me issues in the past due to users with different versions of Access.

 

[arnelgp]

there are two ways to do this. vba or macro.
i'll show you the macro.

create a macro:
on the macro textbox type Beep
now right click anywhere and Make a SubMacro on top of the Beep.
type anything on a description, like "menu disabled" (without quote).
save this macro as mcrBeep.

create another macro.
on the textbox type AddMenu.
type whatever you like on Menu Name.
type the macro (mcrBeep) we created before,
on Menu Macro Name.
save this macro as mcrNoShortCut.

open your report in design view.
on its Property->Other->Shortcut Menu Bar.
manually type mcrNoShortCut.
save you report, you'll have no shortcut menu
for the report. only showing "menu disabled".

 

[Snowflake68]

there are two ways to do this. vba or macro.
i'll show you the macro.

create a macro:
on the macro textbox type Beep
now right click anywhere and Make a SubMacro on top of the Beep.
type anything on a description, like "menu disabled" (without quote).
save this macro as mcrBeep.

create another macro.
on the textbox type AddMenu.
type whatever you like on Menu Name.
type the macro (mcrBeep) we created before,
on Menu Macro Name.
save this macro as mcrNoShortCut.

open your report in design view.
on its Property->Other->Shortcut Menu Bar.
manually type mcrNoShortCut.
save you report, you'll have no shortcut menu
for the report. only showing "menu disabled".

Cool thanks for this. So if I want to add items to a menu how would I do this. So for example if I just wanted to allow them to right click and print?

 

[arnelgp]

firstly you need to create a public function in standard module for printing:

Public Function fnPrint()
Docmd.RunCommand acCmdPrint
End Function

next modify mcrBeep.
change the SubMacro Name to "Print" (without quote).
instead of Beep there on the textbox, type RunCode and fnPrint() as the Function Name.

 

[Snowflake68]

firstly you need to create a public function in standard module for printing:

Public Function fnPrint()
Docmd.RunCommand acCmdPrint
End Function

next modify mcrBeep.
change the SubMacro Name to "Print" (without quote).
instead of Beep there on the textbox, type RunCode and fnPrint() as the Function Name.

Thank you. I think I can now work out what I need, plus just got to put some error trapping in for when the user clicks cancel.

 

[Snowflake68]

Just for the record I added some error handling to the function. Probably not the best in error handing but it does exit smoothly when clicking on the cancel on the print window.

Public Function fnPrint()

On Error GoTo ErrorHandling

    DoCmd.RunCommand acCmdPrint
   
ErrorHandling:

Exit Function

End Function

 

[arnelgp]

just put On Error Resume Next to function fnPrint()

 

[arnelgp]

"On Error Resume Next" is not global or database-wide.
so when the function exits, normal error handling is restored.

 

[Snowflake68]

Ive been trying to find out how to create an icon next to the print option. I have download the msocontrolbutton policy id numbers file for Access but cannot figure out how to use the policy Id to add the print icon to the menu.

I have tried but failed, are you able to help me again please?

 

[arnelgp]

sorry but you cant put any FaceID on the macro.
you have to use VBA and create the menu there.
paste in Module:

Sub CreateContextMenu()
    Const strMenuName As String = "ContextMenu1"
    On Error Resume Next
    Dim cbar As CommandBar
    Dim bt As CommandBarButton
    'delete first if already exists
    CommandBars.Item(strMenuName).Delete
    'recreate
    Set cbar = CommandBars.Add(strMenuName, msoBarPopup, , False)
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "&Print"
        .OnAction = "=fnPrint()"
        .FaceId = 15948
    End With
End Sub

run this and this "ContextMenu1" will be available
on your form/report Property->Other->Shortcut Menu Bar

 

[Snowflake68]

sorry but you cant put any FaceID on the macro.
you have to use VBA and create the menu there.
paste in Module:

Sub CreateContextMenu()
    Const strMenuName As String = "ContextMenu1"
    On Error Resume Next
    Dim cbar As CommandBar
    Dim bt As CommandBarButton
    'delete first if already exists
    CommandBars.Item(strMenuName).Delete
    'recreate
    Set cbar = CommandBars.Add(strMenuName, msoBarPopup, , False)
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "&Print"
        .OnAction = "=fnPrint()"
        .FaceId = 15948
    End With
End Sub

run this and this "ContextMenu1" will be available
on your form/report Property->Other->Shortcut Menu Bar

i have created a new module and pasted your code into it but when I try and run the code I receive a compile error. User-defined type not defined.

Any ideas what I could be doing wrong?

 

[arnelgp]

add Reference to Microsoft Office XX.X Object Library

XX.X is the version of office you have.

 

[Snowflake68]

add Reference to Microsoft Office XX.X Object Library

XX.X is the version of office you have.

The reference was already made, see attached.
When you say run the code do you mean just click on the Run button in the VBA editor?

 

[arnelgp]

Not that one the other one the Object Library

 

[arnelgp]

Keep that plus add the Object Library

 

[Snowflake68]

Not that one the other one the Object Library

Doh! what a nitwit I am, I really didnt read your post properly did I sorry.

Its working now, thanks.

Just one last question. How do I add other items to the same shortcut menu, so for example if I wanted to add the zoom option or Export to Excel option?

 

[arnelgp]

just add it below these line:

    ' this is your print menu item
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "&Print"
        .OnAction = "=fnPrint()"
        .FaceId = 15948
    End With

    ' new item here.. just add what you like
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "zoom"
        .OnAction = "=[your function to zoom]"
        .FaceId = [another face id]
    End With

    ' export to excel
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "Export to Excel"
        .OnAction = "=[your function to export]"
        .FaceId = [another face id]
    End With

Then run the sub again

 

[Snowflake68]

just add it below these line:

    ' this is your print menu item
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "&Print"
        .OnAction = "=fnPrint()"
        .FaceId = 15948
    End With

    ' new item here.. just add what you like
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "zoom"
        .OnAction = "=[your function to zoom]"
        .FaceId = [another face id]
    End With

    ' export to excel
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "Export to Excel"
        .OnAction = "=[your function to export]"
        .FaceId = [another face id]
    End With

Then run the sub again

That simple ah! Thank you so much, you have taught me so much today and I am extremely grateful to you for your time and patience.

 

[Snowflake68]

Here is the code to help others build their own shortcut menu which you need to put 'ReportsShortcutMenu' in the Shortcut Menu property of either a report or form

Sub CreateContextMenu()

'To add additional items to the Shortcut Menu you first need to create a separate function similar to fnPrint()
' then add the item code as outlined in comments below (place the new code between the existing items _
    where you want it to appear in the list)
' Then run this function to recreate the shortcut menu containing the new items.

    Const strMenuName As String = "ReportsShortcutMenu"
    On Error Resume Next
    
    Dim cbar As CommandBar
    Dim bt As CommandBarButton
    
    'delete first if already exists
    CommandBars.Item(strMenuName).Delete
    
    'recreate
    Set cbar = CommandBars.Add(strMenuName, msoBarPopup, , False)

''' add additional items by copying and amending the code below (indicated by the '*****)  _ 
using the correct face id from the AccessControls Developers Excel file
'**********StartOfMenuItemCode****************
    ' Print
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "&Print" ' The & underlines the initial character
        .OnAction = "=fnPrint()"
        .FaceId = 15948
    End With
'**********EndOfMenuItemCode******************
    
    ' Export to Excel
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "Export to Excel"
        .OnAction = "=fnExportExcel()"
        .FaceId = 11723
    End With
        
    ' Close window
    Set bt = cbar.Controls.Add
    With bt
        .Caption = "Close"
        .OnAction = "=fnClose()"
        .FaceId = 923
    End With
    
End Sub

 

[Snowflake68]

These are the separate functions used in the shortcut menu above

Public Function fnPrint()

On Error Resume Next

    DoCmd.RunCommand acCmdPrint

End Function

Public Function fnExportExcel()

On Error Resume Next

    DoCmd.RunCommand acCmdExportExcel
         
End Function

Public Function fnClose()

On Error Resume Next

    DoCmd.RunCommand acCmdClose
         
End Function