OpenReport Shows Wrong Data
Calling DoCmd.OpenReport on a report that's already open will not update the filtering on the report. You have to close it first.
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
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"
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