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