An Access Bound Form Reminder

I was recently called in to help troubleshoot a query that was being used as the record source of an MS Access form.   The query’s execution plan looked fine and it executed quickly through SSMS.  In Access, however, the query took about twice as long to run.  I ran profiler, and to my surprise (and the developer’s), we found that the query was actually executing twice.    

 The culprit was the following lines of VBA code behind his button:

frm.RecordSource = sSQL
frm.requery

The developer’s intent was straightforward: he wanted to make sure the form had the latest data after the  record source had changed. It was also unnecessary.

A bound control’s recordsource property connects it to a table or query. When an open control’s recordsource is changed, it automatically requeries the underlying data. The requery method also runs the query on which the control is based.

In the case above, the developer was unintentionally querying the source twice! Simply removing the extra requery was all it took to cut the runtime in half.

Cancelling an Access Form Close

Suppose you need to add a standard “Are you sure…” confirmation when the user closes an Access form.  It’s easy to add the confirmation message to a button you create, but how do you handle it when the user clicks the form’s standard close button (the “x” in the corner)? Simply adding Cancel = True to the Form_Close() event doesn’t work because the close event can’t be cancelled.

The key is to use the Form’s Unload event, because it can be cancelled.

1. Private Sub Form_Unload(ByVal Cancel As Integer)
2.  
3.     If MsgBox("Are your sure", vbYesNo + vbQuestion, "Close Form") = vbNo Then
4.         Cancel = True
5.     End If
6.  
7. End Sub

If you’ve created your own Close button that uses Access’ standard docmd.close method you’ll also need to modify your button’s err handler because cancelling the form’s close method will raise error 2501 “The Close action was cancelled”.  Here’s a simple example of how to handle the error.

1. Private Sub cmdClose_Click()
2.   On Error GoTo HandleError
3.  
4.     DoCmd.Close(acForm, Me.Name)
5.  
6. ExitHere:
7.     Exit Sub
8. HandleError:
9.     '* Error 2501 will be caused by canceling the form close.
10.  
11.     If Err.Number = 2501 Then
12.         Resume Next
13.     Else
14.         MsgBox("Error: " & Err.Number & " (" & Err.Description & ")", vbOKOnly, "Error")
15.         GoTo ExitHere
16.     End If
17. End Sub