Set Report Properties in Bulk

Sometimes I want to add functionality to a report property or event in bulk.

One good example of this is my InformNoData() function, which shows a friendly message box if a report contains no data.  

While I don't worry about keeping my forms and reports lightweight, there's also no need to add code modules behind every report to implement this type of solution.  In fact, the InformNoData() function can be called directly from the On No Data event of the report's property sheet:

The next question becomes, then, how do I retrofit this solution into an existing database without having to manually open and edit every report?

Setting Report Properties in Code

We're programmers and we're lazy.  Why would we do something manually if we can automate it instead?

Here's the code I use to set the "On No Data" property for all the reports in an Access application:

' ----------------------------------------------------------------
' Procedure : SetReportProperties
' DateTime  : 6/27/2022 22:46
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/setreportproperties/
' Purpose   : Set report properties in bulk for multiple reports.
' ----------------------------------------------------------------
Sub SetReportProperties()
    DoCmd.Hourglass True
        
    Dim ao As AccessObject, Rpt As Report
    For Each ao In CurrentProject.AllReports
        Debug.Print ".";: DoEvents  'https://nolongerset.com/poor-mans-status-bar/
    
        If ao.IsLoaded Then
            'Reports that are already open could have pending changes
            Debug.Print ao.Name; " skipped...report was already open"
        Else
            'Open report in design view
            DoCmd.OpenReport ao.Name, acViewDesign, , , acHidden
            Set Rpt = Reports(ao.Name)
            
            '-----------------------------------------------------------------------
            ' -----===== CUSTOMIZE THIS SECTION =====-----
            'Set properties here
            If Len(Rpt.OnNoData) = 0 Or Rpt.OnNoData = "=InformNoData([Report])" Then
                Rpt.OnNoData = "=InformNoData([Report])"
            Else
                'Don't overwrite an existing property, just report it to the Immediate window;
                '   we'll decide how to deal with these exceptions later
                Debug.Print Rpt.Name, "InformNoData: "; Rpt.OnNoData
            End If
            '=======================================================================
            
            DoCmd.Close acReport, Rpt.Name, acSaveYes
        End If
    Next ao

    
    Debug.Print "Report property update complete"
    DoCmd.Hourglass False

End Sub

How It Works

Here are some notes about the above code:

• The Reports collection comprises open reports only. We use the CurrentProject.AllReports collection to enumerate both the open and closed reports.
• If a report is already loaded, it might mean that we were in the middle of editing it.  Rather than risk losing changes we made–or blindly saving changes we may not want to have saved–the routine simply skips over these reports.
• To make permanent changes to a report, we need to be editing the report in Design view.
• Once the report has been opened, we can use the Reports collection to return a corresponding Report object.  
• While we could use Reports(ao.Name) directly, by declaring Rpt As Report and assigning to that object variable, it allows us to use IntelliSense when setting up the code in the "CUSTOMIZE THIS SECTION" portion of the routine.
• Before setting any report property, I first check to make sure it hasn't already been set to something else.  We don't want to clobber some other customization we made earlier.
• If there is existing code for a property, we output the name of the report and the value of the property to the Immediate window.  We can then deal with these exceptions after the rest of the reports have been updated.
• I use my Poor Man's Status Bar to show that the routine is progressing.

Making It Your Own

Obviously, this routine is simply a framework for updating any type of report property in bulk on multiple reports.  

By changing the code in the "CUSTOMIZE THIS SECTION" portion, you can update a bunch of different report properties in bulk.  You could even update multiple report properties at the same time (e.g., the .OnNoData and .RibbonName properties).

Referenced articles

Show a Friendly Message When a Report Has No Data

A handy function you can call from an Access report’s Property Sheet to gracefully inform the user when their report has no data to display.

No Longer SetMike Wolfe

Poor Man’s Status Bar in VBA

If you’re looking for a quick and dirty way to keep track of a long-running process while developing, this VBA one-liner will do the trick.

No Longer SetMike Wolfe