Hide Options Menu from Backstage

There have been a couple of times when users in the forums asked how to hide the backstage menu, specifically Options, to prevent their users from making adjustments to the database settings. When a database application is executed in a runtime environment, most of the menus are disabled; but sometimes, that is not enough to protect the database from curious users. One possible approach is to create a custom Ribbon that manipulates the Backstage to hide these menus.

Figure 1 shows a typical Access 2010 File Menu (Backstage) with all the choices available.



Figure 2 shows what the File Menu might look like after applying the custom Ribbon that manipulates the Backstage.



To achieve the result shown in Figure 2, just include the following XML snippet within your custom Ribbon.

<backstage>
  <button idMso="FileSave" visible="false"/>
  <button idMso="SaveObjectAs" visible="false"/>
  <button idMso="FileSaveAsCurrentFileFormat" visible="false"/>
  <button idMso="FileOpen" visible="false"/>
  <button idMso="FileCloseDatabase" visible="false"/>
  <tab idMso ="TabInfo" visible="false"/>
  <tab idMso ="TabRecent" visible="false"/>
  <tab idMso ="TabNew" visible="false"/>
  <tab idMso ="TabPrint" visible="false"/>
  <tab idMso ="TabShare" visible="false"/>
  <tab idMso ="TabHelp" visible="false"/>
  <button idMso="ApplicationOptionsDialog" visible="false"/>
  <button idMso="FileExit" visible="false"/>
</backstage>

To see this in action, please download the demo file here. As usual, I would appreciate any comments or feedback. Thank you.

Original post date: September 30, 2014

Assigning Ribbons to Forms and Reports using VBA

I was recently asked to upgrade an Access 2003 database into Access 2010. I thought to myself "that can't be hard," so I went ahead and accepted the challenge. The old database file used a Switchboard, and all the Menu bars were hidden. That's great, so I thought all I had to do was also hide the Ribbon because everything the user needs would be in the Switchboard anyway (I decided not to convert the Switchboard to a Navigation Form because I didn't want to deal with new problems).

So, I moved all the objects from the old database into a brand new ACCDB file and removed all references to any ActiveX objects. Once I debugged out all the errors, I gave the new application to the client. Then, I realized that one thing I forgot to do was provide a Ribbon for the reports (remember, I had hidden the Ribbon for the whole application). I guess I could just "unhide" the Ribbon when a report is opened, but I thought I would give the client a taste of the new Access 2010 features and create a custom Ribbon for the reports.

Now, there were quite a few existing reports in this database, and it would take me some time to assign the new custom Ribbon to these reports. So, I decided I would just create a function to do the assignment for me. The following is what I came up with:

Public Sub AssignReportRibbon()
'accessmvp.com/thedbguy
'7/18/2014

Dim rpt As Variant

For Each rpt In CurrentProject.AllReports
    DoCmd.OpenReport rpt.Name, acViewDesign
    rpt.RibbonName = "RPTReport"
    DoCmd.Close acReport, rpt.Name, acSaveYes
Next

End Sub

However, it didn't work because I kept getting an error on this line:

rpt.RibbonName = "RPTReport"

I couldn't understand it, so I Googled around for an answer. Luckily, I discovered that fellow MVP, Daniel, had already figured it out. Check out his blog post here. So, based on his discovery, I modified my code to the following:

Public Sub AssignReportRibbon()
'accessmvp.com/thedbguy
'7/18/2014

Dim rpt As Variant

For Each rpt In CurrentProject.AllReports
    DoCmd.OpenReport rpt.Name, acViewDesign
    Reports(rpt.Name).Report.RibbonName = "RPTReport"
    DoCmd.Close acReport, rpt.Name, acSaveYes
Next

End Sub

And voila, it now works! It can't be that hard to convert that code to use it for Forms, but I'll leave that challenge for you.
Please let me know if you have any questions. Thanks, as always, for reading.

Cheers!

Original post date: July 30, 2014