Why so lazy, Access?

A powerful feature in Access is passing a Where condition to a form or report so that it opens with only a subset of its full RecordSource.  For example:

Sub ShowAccount(AccountID As Long)
    Dim WhereClause As String
    WhereClause = "AccountID = " & AccountID
    
    DoCmd.OpenForm "MyForm", WhereCondition:=WhereClause
    DoCmd.OpenReport "MyReport", acViewPreview, WhereCondition:=WhereClause
End Sub

If we call ShowAccount 1, then the above code will open a form and report, each of which will only show the record with an AccountID of 1.

Now, let's say that we want to open the same form and report, but for a different AccountID.  Without first closing our form and report, we call ShowAccount 2.  You might expect that the open form and report would switch to displaying the information for the record with an AccountID of 2.  You would be wrong.

Access is lazy

When you call DoCmd.OpenForm or DoCmd.OpenReport, the first thing Access does is check to see if the form or report is already open.  If it is, then Access looks around and--like a dad spying leftover pizza in the refrigerator when he's in charge of making dinner--declares, "My work here is done."  

Access doesn't bother to check to make sure that the form and report are showing the correct information.  It will unhide the form or report if necessary.  It will also bring it to the top of the z-order and give it the focus.  But make no mistake, it's doing the bare minimum here.  It's taking the pizza out of the fridge and putting it on plates.  It's not even bothering to scrape the mold off. (Don't worry, my kids are fine; like I told my wife, a little penicillin never hurt anybody...to which my wife replied, did you forget our daughter has a penicillin allergy?)

Since Access is too lazy to update the filter of our form and report (hey, it's not lazy it's efficient!), we have to force it to do that.  The simplest way to do that is to force an open form or report to close before showing the new data.  We could also open multiple instances of the form or report, but that's beyond the scope of this article.

Checking for an open form or report

There are many different ways to check to see if a form or report is open.  I'm going to use the SysCmd method.  It's quick, it's reliable, but it's not exactly readable.  How do we deal with an unreadable function?  We wrap it up into something more readable:

Function FormIsOpen(FormName As String) As Boolean
    FormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, FormName) <> 0)
End Function

Function ReportIsOpen(RptName As String) As Boolean 'vv
    ReportIsOpen = (SysCmd(acSysCmdGetObjectState, acReport, RptName) <> 0)
End Function

It may seem silly to wrap a single line of code inside of a standalone function, but the result is more readable code.  This approach avoids the need for comments and results in "self-documenting" code.  Use it widely.

Good code

Sub ShowAccount(AccountID As Long)
    Dim WhereClause As String
    WhereClause = "AccountID = " & AccountID
    
    If FormIsOpen("MyForm") Then DoCmd.Close acForm, "MyForm"
    DoCmd.OpenForm "MyForm", WhereCondition:=WhereClause
    
    If ReportIsOpen("MyReport") Then DoCmd.Close acReport, "MyReport"
    DoCmd.OpenReport "MyReport", acViewPreview, WhereCondition:=WhereClause
End Sub

Bad code (logically equivalent but morally reprehensible)

Sub ShowAccount(AccountID As Long)
    Dim WhereClause As String
    WhereClause = "AccountID = " & AccountID
    
    If (SysCmd(acSysCmdGetObjectState, acForm, "MyForm") <> 0) Then
        DoCmd.Close acForm, "MyForm"
    End If
    DoCmd.OpenForm "MyForm", WhereCondition:=WhereClause
    
    If (SysCmd(acSysCmdGetObjectState, acReport, "MyReport") <> 0) Then
        DoCmd.Close acReport, "MyReport"
    End If
    DoCmd.OpenReport "MyReport", acViewPreview, WhereCondition:=WhereClause
End Sub

Conclusion

That's it for today.  There's nothing else to see here.  I could go on about some of the other uses for these two functions, but I'm sure you can figure that out on your own.  As for me, I better go check on my daughter's swelling...

Image by Jan Steiner from Pixabay