My Dirty Little Secret

Mike Wolfe -

Consider a bound form with a command button that opens a report tied to the underlying data.  The user changes the data then clicks on the button to open the report.  Much to the user's surprise, the data they just entered does not appear on the report!  What happened?  The changes never got committed to the database, so the report is showing the previous information.

Now, the user could manually commit the changes by either A) clicking on the record-selector or B) pressing [Shift] + [Enter] on the keyboard  (so long as they're not inside a multi-line textbox).  A cardinal rule of designing a good user experience (UX), though, is to never rely on the user to perform an action that the application can easily handle.

Solution A - The naive approach

In this case, the most common solution is to set the form's Dirty property to False.

Private Sub btnShowReport_Click()
    Me.Dirty = False
    DoCmd.OpenReport "MyReport", acViewPreview
End Sub
Commit changes then show report (version 1)

This will get the job done if there are no issues with the data being saved.  But, if the data can't be saved for some reason, you often end up with two separate error messages: 1) a form error with useful information about the invalid data and 2) a generic and useless error--"The setting you entered isn't valid for this property."--which simply means that you can't set the Dirty property to False because the record can't be saved.

The first error message is valuable Signal; it informs the user how to fix the problem.  The second error message is Noise; it provides no useful information and serves only to distract from the real issue.

Solution B - Signal Booster

My solution boosts the Signal to Noise ratio for both user and developer.  Let's improve on the Me.Dirty = False construct by wrapping it inside a function:

Function SaveForm(Frm As Form) As Boolean
    On Error Resume Next
    Frm.Dirty = False
    If Err.Number = 0 Then SaveForm = True
End Function

We call the above function like this:

Private Sub btnShowReport_Click()
    If SaveForm(Me) Then 
        DoCmd.OpenReport "MyReport", acViewPreview
    End If
End Sub

For the user, they now only get the original form error message, pointing them to the field or data that needs to be corrected.  And for the developer, "SaveForm(Me)" is much clearer than "Dirty = False".

This is all well and good, but now we've introduced a level of nesting into our code.  Nesting makes code harder to read and follow.  Some nesting can't be helped, like loops.  Since nesting makes code harder to follow, we should avoid it when we can.

Solution C - Guard Clause

How can we refactor our solution to avoid nesting?  Flip the logic of the If statement and turn it into a guard clause.  A guard clause is an If statement at the top of a routine to ensure that certain criteria are met before the routine will run.  Depending on the situation, if the guard clause fails we either A) exit the routine or B) throw an error.

In this case, since the form is going to be raising its own error, we can simply exit the function.

First, we change the name of our function from SaveForm to SaveFails.  This avoids a Not in the guard clause.  This saves space and improves readability.

Note: Normally I would avoid creating a Boolean function with a "negative" sounding name so as to avoid a double-negative when negating the function call.  In this case, though, I've never called this as Not SaveFails, so I'm happy with the function-naming decision I made.

Function SaveFails(Frm As Form) As Boolean
    On Error Resume Next
    Frm.Dirty = False
    SaveFails = (Err.Number <> 0)
End Function

Here's how it looks in our button code:

Private Sub btnShowReport_Click()
    If SaveFails(Me) Then Exit Sub
    
    DoCmd.OpenReport "MyReport", acViewPreview
End Sub

This is highly readable code.  One quick glance at the top of our routine shows us all the conditions that must be met before continuing on.  I'm only showing a single guard clause, but I could easily have several more.

Also, I strongly prefer single-line If-Then statements when writing guard clauses because it makes a group of them much easier to read.

Solution D - Making SaveFails() Robust

To finish this off, I want to post the most current version of my SaveFails() routine.  This updated function has support for recursively forcing saves on nested subforms and deals with several corner cases that have arisen over the years.

Incidentally, the difference between the original and robust versions of the SaveFails() function is a microcosm of the reason why Joel Spolsky calls full software rewrites the "single worst strategic mistake that any software company can make."

'---------------------------------------------------------------------------------------
' Procedure : SaveFails
' Author    : Mike@nolongerset.com
' Date      : 10/30/2008 - 10/23/2014
' Purpose   : Use in form events to attempt to save the current record.  If the save
'               attempt fails the function returns True.
' Usage     : If SaveFails(Me) Then GoTo Exit_MyProcedure
'  2/ 7/14  : Recursively call subforms to ensure all records are saved.
'  2/ 7/14  : Don't return True when calling SaveFails on unbound forms.
' 10/23/14  : Don't recursively call subform if it has no source object.
' 11/18/14  : Allow for subforms not yet open when SaveFails called from Parent Form Open/Load event
'---------------------------------------------------------------------------------------
'
Function SaveFails(Frm As Form) As Boolean
    Dim Ctl As Control
    For Each Ctl In Frm.Controls
        If Ctl.ControlType = acSubform Then
            If Len(Ctl.SourceObject) > 0 Then
                On Error Resume Next
                Dim Dummy As String: Dummy = Ctl.Form.Name
                Select Case Err.Number
                Case 0
                    If SaveFails(Ctl.Form) Then
                        SaveFails = True
                        Exit Function
                    End If
                Case 2455
                    'Err 2455 may occur for subforms that have not yet been opened when error occurs in parent form Open/Load event
                    '2455: You entered an expression that has an invalid reference to the property Form.
                    Err.Clear
                Case Else
                    SaveFails = True
                    Exit Function
                End Select
                On Error GoTo 0
            End If
        End If
    Next Ctl
    On Error Resume Next
    Frm.Dirty = False
    Select Case Err.Number
    Case 0
    Case 2455    'You entered an expression that has an invalid reference to the property Dirty.
    Case Else
        SaveFails = True
    End Select
End Function

P.S. As a reminder, the reason you never see the kind of traditional error handling code you're used to seeing in VBA examples is because I use a global error handler in conjunction with vbWatchdog.  Read all about that here: Error Handling Evolution.

Image by Sammy-Williams from Pixabay