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.
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.