We help IT Professionals succeed at work.

Report print view option in Access 2016 runt time

urjudo
urjudo asked
on
Hi Experts,
Is any way I can enable the "More" option next to the "PDF or XPS Datain the print view in office 365 access 2016 run time?  I would like the users have ability to merge the report to word but it only works on full version not in run time.  If possible to enable it in runt time version?  I attached the screen shot.

Thank you,
Report.jpg
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:

There are a lot of features of the full version which are not available in the Runtime version.


Access will not let you use the built-in shortcut menus in the run-time environment, but you can create and use your own.  One way to do this is to copy the print menu and rename it, then remove all of the items I'm not interested in.  The easiest way I know to do that is to use the Access Shortcut Tool.  Which makes it easy to copy an existing shortcut menu or create your own, from scratch.  What I do is create a shortcut (right click) menu for all my reports, with options, Print, Save as PDF, Send as attachment, Export to Excel, and Close.  


These article should help you get a better understanding of short-cut menus

Understanding and using Commandbars

Understanding and using Commandbars, Part II

Commandbars (Part III) - Using built-in menus

Using shortcut menus in Access run-time environment



John TsioumprisSoftware & Systems Engineer

Commented:
Check this code if this is what you want
Private Sub Form_Load()
CreateSimpleShortcutMenu
Me.Field1.ShortcutMenuBar = "ShowDataShortcutMenu"
End Sub

Open in new window

Sub CreateSimpleShortcutMenu()
      Dim newMenu As CommandBarControl
Dim cmb As CommandBar
  On Error Resume Next 'If menu with same name exists delete
  CommandBars("ShowDataShortcutMenu").Delete

    'CommandBars("ShowDataShortcutMenu").Delete

    Set cmb = CommandBars.Add("ShowDataShortcutMenu", msoBarPopup, False, False)
    With cmb
.Controls.Add msoControlButton, 11725, , , True


    End With

    Set cmb = Nothing
    Set newMenu = Nothing

End Sub

Open in new window

Author

Commented:
@Dale,
Thanks for the information, I'm still trying to understand how to do that.

@John,
I did tried your suggestion, but I got a compile error "user-defined not defined" on
 Dim newMenu As CommandBarControl  ('CommandBarControl" - user-defined not defined)
 Dim cmb As CommandBar ("CommandBar" - user-defined not defined)

also shall I put the "Private Sub CreateSimpleShortcutMenu()" code in a module or just put anywhere in the report?


Me.Field1.ShortcutMenuBar = "ShowDataShortcutMenu" -- what do you mean "Me.Field1"?  
  it's not Me.ShortcutMenuBar = "ShowDataShortcutMenu?

Thanks
John TsioumprisSoftware & Systems Engineer
Some reference probably is missing..I will take a look

Author

Commented:
@John,
I changed Dim newMenu As CommandBarControl to Dim newMenu As Object but still have problem withe the Dim cmb as CommandBar

also what's the difference if the code is in "On_Load" or "On_Open"?
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

On_Open occurs before the recordset is loaded, On_Load occurs when the recordset is loaded.


I generally don't use the On_Open event of the report, unless I'm checking for the existence of values that might be in the WHERE clause of the reports recordset.  Otherwise, any code I need in a report is in the On_Load event.


But when it comes to commandbars, I generally build them once, and they stay resident in the database, not Access, but the database.  You can put this code in a standard code module, as John did, but you will only need to run that once.  


When the last argument in this statement:


CommandBars.Add([Name], [Position], [MenuBar], [Temporary]) 


and for the control, the last argument is also [Temporary], so if you create the commandbar with that argument set to False, and the control with that argument set to false, the bar and control will be permanent elements of that database, so you only need to call that procedure once.  I generated the code below from within the Access Shortcut tool.  It has been over 10 years since I've done a mail merge between Access and Word, but one of the two options presented here may work for you:

Public Sub commandbarTest()

    Dim cbr As Object
    Dim ctrl0 As Object
    Dim ctrl1 As Object

    On Error GoTo ProcError

    Set cbr = CommandBars.Add("TestShortcutMenu", 5, , 0)
    With CommandBars("TestShortcutMenu").Controls
        Set ctrl0 = .Add(Type:=1, ID:=11725) '&Word RTF File
        Set ctrl0 = .Add(Type:=1, ID:=626) '&Merge it with Microsoft Office Word
    End With

ProcExit:
    Exit Sub 'change to function if needed

ProcError:
    MsgBox Err.Number & vbCrLf & Err.Description, , "Script error"
    Debug.Print "Script error", Err.Number, Err.Description
    Resume ProcExit
   
End Sub

And then I would not use the Report_Load event to set the shortcut menu associated with the report.  I would simply select your report, open it in design view, and set the ShortcutMenu property from the dropdown list (my image below has lots of shortcut menus defined, but yours should only have the one).



Author

Commented:
@Dale,
I tried to test your code, it gave me an error message "Microsoft Access can not find TestShortcutMenu"  also on the report under Short Cut Menu Bar I  don't see anything in the drop down like yours.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Did you run that procedure from the immediate window after you pasted it into a standard code module, not the report code module?  If not, do so using the immediate window:

Call CommandbarTest

Once you have run that code, you should see a dropdown list in the Shortcut Menu property of the report.


Author

Commented:
@Dale,
I paste the code into a standard module called "MyNewMenu" not in the report.  When I run "call commandbarTest in the Immediate window, the error message was "Compile error: Ambiguous name detected: commandbarTest

Author

Commented:
@Dale,
now I can see the TestShortcutMenu in the short cut menu bar but when in the run the report, right click, I see nothing but with a clear little square box .

Author

Commented:
@John,
I'm looking at your code, I have a question about this line:
Set cmb = CommandBars.Add("ShowDataShortcutMenu", msoBarPopup, False, False)

is suppose to be:
Set cmb = CommandBars.Add("ShowDataShortcutMenu", msoBarPopup, False, True) ?

I test both, it seems the same, so should I set the "temporary" to True or False?

Author

Commented:
here is the code that I modified from John's code.  I placed in a standard module so I can use this code on other reports, but I run to a problem is if I don't call the function On_Load on the report, the "vbaShortcutMenu" is disappears from the shortcut menu bar every time after I compact & repair the database unless I add "call CustomShortMenu" in On_Load on the report then it stays.  what did I do wrong?

Public Function CustomShortCutMenu()
Dim menuName As Object
Dim cb As CommandBar
Dim CBB As CommandBarButton

menuName = "vbaShortcutMenu"

On Error Resume Next
Application.CommandBars(menuName).Delete

On Error GoTo 0

Set cb = Application.CommandBars.Add(menuName, msoBarPopup, False, True)


Set CBB = cb.Controls.Add(msoControlButton, 11725, , , True)
CBB.Caption = "Export to Word..."
CBB.FaceId = 42


Set cb = Nothing
Set CBB = Nothing
End Function
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

You only want this code to run once, period.  You want to set the "Temporary" argument for both the Comandbar and the control to FALSE, this way you don't need to call this procedure ever again, ever.  


But if you do this, you don't want to do:

 Application.CommandBars(menuName).Delete
 


Where did you get the FaceID = 42?



Author

Commented:
@Dale,

1).What's difference between:
       Set cb = Application.CommandBars.Add(menuName, msoBarPopup, False, False)
   and
      Set cb =CommandBars.Add(menuName, msoBarPopup, False, False)

  If I put the code in the as Public Function, Shall I use Application.CommandBar.Add instead CommandBars.Add?


2). Is this code below correct?

Public Function CustomShortCutMenu()
Dim menuName As Object
Dim cb As CommandBar
Dim CBB As CommandBarButton

menuName = "vbaShortcutMenu"


Set cb = Application.CommandBars.Add(menuName, msoBarPopup, False, False)


Set CBB = cb.Controls.Add(msoControlButton, 11725, , , True)
CBB.Caption = "Export to Word..."


Set cb = Nothing
Set CBB = Nothing
End Function

I tried #2 but still have the same issue (not show in the ShortCut Menu Bar drop down)
John TsioumprisSoftware & Systems Engineer
If you have checked my code you should see that on control level the CommandBar is set like this :
Me.Field1.ShortcutMenuBar = "ShowDataShortcutMenu"

Open in new window

Setting for the report is
Me.ShortcutMenuBar = "ShowDataShortcutMenu"

Open in new window

Because it was intriguing i created a small application that shows this functionality...it loads a form...just hit the "SHOW Report" button...now the Report has the Export to Word functionality
I tested it in pseudo runtime (.accdr) and it works fine
Clipboard02.jpgLast but not least ..you can create your own Ribbon and add the button on your own.
Test.accdb
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

John,


I don't understand why you keep encouraging him to make this a temporary commandbar/control and set the ShortcutMenuBar property at run-time.  If you define the commandbar and control as permanent ([Temporary] = False) then you never need to run that code again, and can simply set the ShortcutMenuBar property of the report to the menu name.


Dale

John TsioumprisSoftware & Systems Engineer
Good point Dale
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

urjudo,


I'm going to back out of this question.  John and I. although using similar techniques, are just confusing you, so I'm going to let you work with John from here on out.


Dale

John TsioumprisSoftware & Systems Engineer
Dale it would be best not to back out after all you are the "master" ...i rarely deal with CommandBars ... this was a nice feature in Access 2003...but now its too much of a trouble ...i use Ribbon for all these extra features..
As for the CommandBars i just had some small piece of code laying around....
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

John,


I would encourage you to download the Access Shortcut Tool, much easier to use than the method in 2003.


Dale

John TsioumprisSoftware & Systems Engineer
Oh ... i must admit i didn't knew about it...thanks.

Author

Commented:
@Dale & John,
Thank you for both of your efforts to answer my question.  I did try to create a Ribbon and for some reason it did not show.  Like John was saying, I like the Access 2003 version coz it's much easier to create custom shortcut and menu bar.  I don't know why Microsoft removed some of the great features after 2003 version.

Author

Commented:
Thanks again for all your time!  very appreciated!
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Did you download and install the Access Shortcut Tool, hyperlinked in my first post?

This tool was designed to replace the method used in A2003 and has made it very easy for me to make Shortcut menus over the last 4 years.  Give it a try.

Author

Commented:
@Dale,
I did download the Access Shortcut Tool, but it's a read only and every time when I open this sample database, it runs the Access Shortcut Tool, is this normal?.  Is any way I can unchecked the ready only and make it as full version? also how it works (I means I do I copy to my database).

Thanks
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

It's not read only, but if you are trying to launch it from within the zip file, it would be.

There is a text file with instructions in the zip file; follow those instructions to install it so that you can use it to create short-cut menuse within all of your Access applications.

Author

Commented:
Sorry, one more question, I did follow the instructions, and I can see under the Add-Ins in my program, do you have any instruction how to use it?