OpenReport Shows Wrong Data
There are several things that annoy me about the default behavior of DoCmd.OpenReport. To address these annoyances, I built a replacement function that I named PreviewReport().
In this series of articles, I will take you through the evolution of this function as I address each of my frustrations.
Part 2: Wrong Data
If you call OpenReport on a report that's already open, Access doesn't apply the new criteria to the report. Instead, it simply sets focus to the already open report.
In the example below, I'm using the Northwind database to open Invoice #30 followed by Invoice #31. However, since I didn't close the report before calling it with the new criteria, Access shows Invoice #30 even when I call the method with "[Order ID]=31".
DoCmd.OpenReport "Invoice", acViewPreview, , "[Order ID]=30"
DoCmd.OpenReport "Invoice", acViewPreview, , "[Order ID]=31"Workaround: Close the report before using new criteria
Here's the same example as above, except this time I'm closing the report before calling it with the new Where condition. Now it works the way we want.
DoCmd.OpenReport "Invoice", acViewPreview, , "[Order ID]=30"
DoCmd.Close acReport, "Invoice"
DoCmd.OpenReport "Invoice", acViewPreview, , "[Order ID]=31"PreviewReport() Evolution
To incorporate my workaround for this behavior, I first check to see if the report I'm trying to open has already been opened. To do this, I use my ReportIsOpen() convenience function.
Here's the next step forward in the evolution of the PreviewReport() function, which now addresses two of my five peeves.
Function PreviewReport(RptName As String, Optional Where As String = "")
If ReportIsOpen(RptName) Then DoCmd.Close acReport, RptName
DoCmd.OpenReport RptName, acViewPreview, , Where
End Function
'source: https://nolongerset.com/why-so-lazy-access/
Function ReportIsOpen(RptName As String) As Boolean
ReportIsOpen = (SysCmd(acSysCmdGetObjectState, acReport, RptName) <> 0)
End Function