CaptionCheck(): Verify All Forms and Reports Have a Caption
This simple pre-deployment check will help save you the embarrassment of forgetting to set your form or report captions.
Recently, I wrote about the technique I use to help me remember to always set a Form or Report Caption:
- Create a text box in your Report Header or Page Header.
- Set its ControlSource to
- 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.
' ---------------------------------------------------------------- ' 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
This seems like it should work, but you soon realize that those two collections refer only to open forms and reports.
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
.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
.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
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:
- Just ignore them.
- Update the code to skip over them (likely based on your subform/report naming convention).
- 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:
Cover image created with Microsoft Designer