With the replacement of the command bars with the ribbon in Access 2007, I have started to always use a standardized ribbon for all my reports. Now, I typically used 2 template reports which I have configured my typical layout, colors, header & footer formatting, etc. and of course defined the Ribbon Name. But like everyone else, sometimes, I start a new report from scratch, modify an existing one and forget to add in the Ribbon Name property and thus the user would not have displayed the ribbon allowing the to print, export or merely close the report; forcing them to shutdown the database just to be able to exit/close the report. So I needed a simple means to ensure all my reports included this ribbon.
I already have a DeployMe function that I run everytime prior to deploying a new version of any database I create to my client’s which sets certain properties, ensures things like auto compact is disabled, auto correct is disables, and much more, so I simply wanted to create a function the I could add to this routine. Below is what I came up with. A simple loop that open each report one by one, sets the RibbonName property and then closes and saves the change. Short, sweet and simple. Best, it work.
'--------------------------------------------------------------------------------------- ' Procedure : SetReportRibbon ' Author : Daniel Pineault, CARDA Consultants Inc. ' Website : http://www.cardaconsultants.com ' Purpose : Loop through all the reports in the current database and assign the ' specified ribbon as the report's ribbon ' Copyright : The following may be altered and reused as you wish so long as the ' copyright notice is left unchanged (including Author, Website and ' Copyright). It may not be sold/resold or reposted on other sites (links ' back to this site are allowed). ' ' Usage: ' ~~~~~~ ' Call SetReportRibbon ' ' Revision History: ' Rev Date(yyyy/mm/dd) Description ' ************************************************************************************** ' 1 2012-Dec-07 Initial Release '--------------------------------------------------------------------------------------- Function SetReportRibbon() On Error GoTo Error_Handler 'Ensure that all the reports are set to use the ReportUtilities ribbon Dim DbO As AccessObject Dim DbP As Object Dim Rpts As String Set DbP = Application.CurrentProject For Each DbO In DbP.AllReports DoCmd.OpenReport DbO.Name, acViewDesign If Reports(DbO.Name).Report.RibbonName = "" Then Reports(DbO.Name).Report.RibbonName = "ReportUtilities" End If DoCmd.Close acReport, DbO.Name, acSaveYes Next DbO Error_Handler_Exit: On Error Resume Next Set DbP = Nothing Exit Function Error_Handler: MsgBox "The following error has occured." & vbCrLf & vbCrLf & _ "Error Number: " & Err.Number & vbCrLf & _ "Error Source: " & sModName & "/SetReportRibbon" & vbCrLf & _ "Error Description: " & Err.Description, _ vbCritical, "An Error has Occured!" Resume Error_Handler_Exit End Function |
