Thursday, February 18, 2016

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

4 comments:

  1. I am definitely enjoying your website. You definitely have some great insight and great stories.
    microsoft excel vba training

    ReplyDelete
  2. Hi Ramiz. Thank you for your comment. I hope others find it helpful as well. Cheers!

    ReplyDelete
  3. just changed your code a bit ( the glory is not mine )and apparently it works for forms very well

    Public Sub ASSIGNFORMSRIBBON()

    Dim FRM As Variant

    For Each FRM In CurrentProject.AllForms
    DoCmd.OpenForm FRM.Name, acViewDesign
    Forms(FRM.Name).Form.RibbonName = "rbnindependent"
    DoCmd.Close acForm, FRM.Name, acSaveYes
    Next
    End Sub

    ReplyDelete
  4. Hi,

    Glad to hear you were able to apply the technique to forms as well. Cheers!

    ReplyDelete