CaptionCheck(): Verify All Forms and Reports Have a Caption

Recently, I wrote about the technique I use to help me remember to always set a Form or Report Caption:

1. Create a text box in your Report Header or Page Header.
2. Set its ControlSource to =[Report].[Caption]
3. Set the report's Caption property in the Property Sheet

In the comments of that article, Access MVP Jack Stockton posted his CaptionCheck() function, which checks for forms or reports that are missing a caption.

This is the sort of procedure you would run before deploying a front-end update.

The Code

' ----------------------------------------------------------------
' Procedure : CaptionCheck
' Author    : Jack Stockton
' Source    : https://nolongerset.com/captioncheck/
' Purpose   : Check for missing captions in all forms and reports.
' ----------------------------------------------------------------
Public Function CaptionCheck()

    On Error GoTo PROC_ERR

    Dim obj             As AccessObject
    Dim frm             As Form
    Dim rpt             As Report
    Dim sObjName        As String
    Dim iCounter        As Integer

    For Each obj In CurrentProject.AllForms
        sObjName = obj.Name
        DoCmd.OpenForm sObjName, acDesign, , , , acHidden
        Set frm = Forms(sObjName).Form
        If Len(Trim(frm.Caption)) = 0 Then
            iCounter = iCounter + 1
            If iCounter = 1 Then
                Debug.Print "Forms without a caption"
            End If
            Debug.Print vbTab & sObjName, frm.Caption
        End If
        DoCmd.Close acForm, sObjName, acSaveNo
    Next obj
    Set obj = Nothing
    iCounter = 0

    For Each obj In CurrentProject.AllReports
        sObjName = obj.Name
        DoCmd.OpenReport sObjName, acDesign, , , , acHidden
        Set rpt = Reports(sObjName).Report
        If Len(Trim(rpt.Caption)) = 0 Then
            iCounter = iCounter + 1
            If iCounter = 1 Then
                Debug.Print "Reports without a caption"
            End If
            Debug.Print vbTab & sObjName, rpt.Caption
        End If
        DoCmd.Close acReport, sObjName, acSaveNo
    Next obj

PROC_EXIT:

    Set frm = Nothing
    Set rpt = Nothing
    Set obj = Nothing
    Exit Function

PROC_ERR:

    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
        "Error Number: " & Err.Number & vbCrLf & _
        "Error Source: CaptionCheck Function in modCaptions " & vbCrLf & _
        "Error Description: " & Err.Description & _
        Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
        , vbOKOnly + vbCritical, "An Error has Occured!"

    Resume PROC_EXIT

    Resume

End Function

A Few Notes About the Code

Application.Forms/Reports vs. CurrentProject.AllForms/Reports

Most Access developers, the first time they want to loop through all the forms or reports in an application, start by using Forms. or Reports..

This seems like it should work, but you soon realize that those two collections refer only to open forms and reports.  Forms and Reports are actually member collections of the the Access.Application object.  Because the Access.Application object is in the global namespace, you don't need to include it when referencing its many methods and properties, of which .Forms and .Reports are two.  

The fact that Access.Application is in the global namespace makes its methods and properties more discoverable than others.

One of the many properties of the Access.Application object is CurrentProject, from which we can access the .AllForms and .AllReports collections.  Those two collections include all of the forms and reports–both closed and open–associated with the currently active Access application (i.e., the currently open .mdb/.accdb).

If you want to loop through all the forms and reports in an Access front-end file, those are the collections you need.

DoCmd.Close ... acSaveNo

Whenever you are opening a form or report via VBA and you don't intend to make any changes to it, it's safest to be explicit that you don't want to save the changes when you close it.

If you forget to include the acSaveNo parameter in the call to DoCmd.Close, you run the risk of having some unintentional change made to the object.  Also, using acSaveNo is more efficient because Access doesn't have to check whether anything changed with the object (as it would with acSavePrompt, the default) or save the object unnecessarily (as it would with acSaveYes).

Subforms and Subreports

The code loops through ALL forms and reports, so forms and reports that you only use as subforms and subreports will appear on the list.  

You can deal with this a few ways:

1. Just ignore them.
2. Update the code to skip over them (likely based on your subform/report naming convention).
3. Give them a Caption (even though it won't be shown to the end user).

I'd advise against option 1, as it would be too easy to miss a new form or report that suddenly started appearing in the list.

The Extra Resume

At the end of Jack's function is a line of code that is unreachable:

    Resume PROC_EXIT
    
    Resume  '<-- this line of code is unreachable
    
End Function

But it's there for a good reason!

I wrote about that technique here:

Referenced articles

Cover image created with Microsoft Designer