Access report cannot be closed or refreshed from VB with updated parameter set

Question

A form's button click opens an access report that comes up with data. The parameters are used with a pass-through query to an SQL stored procedure which returns records. The report does not come up Modal and I would like it to remain that way. However, if the user does not close the report before going back to the form and tries to set new parameters, the report remains open in the background and upon the button click the report is brought to the fore with old parameters and data and not refreshed with new parameters/data.

One option is to go Modal with the report but that makes for rough transitions with the user having to actively close the report. The other option is to close the report during retries which is what I have been trying. I have tried:

    If CurrentProject.AllReports(rpt_ptq_uspWorkCentreReport).IsLoaded Then
        DoCmd.Close acReport, rpt_ptq_uspWorkCentreReport, acSaveNo

in several different locations: _MousedDown, as the first If in the _Click, and _BeforeInsert. Each time CurrentProject.AllReports(rpt_ptq_uspWorkCentreReport).IsLoaded comes up false during the second pass when the report is sitting in the background and the form is being reworked with the next tries new parameters. Also during the second attempt the .OpenReport line fails with an SQL error because strSQLP1 is incomplete. Here's the _Click event:

Private Sub btnPreviewP1_Click()

    If (Me.txtToDateP1 < Me.txtFromDateP1) Then
        MsgBox ("The From Date must occurr before the To Date!")
    End If

    Dim strFromDateHMS  As String
    Dim strToDateHMS    As String
    Dim strSQLP1    As String
    Dim strOpenArgs As String

    strFromDateHMS = Format(Me.txtFromDateP1, "yyyy-mm-dd") & " " & Me.cboFromHourP1 & ":" & Me.cboFromMinuteP1 & ":" & Me.cboFromSecondP1
    strToDateHMS = Format(Me.txtToDateP1, "yyyy-mm-dd") & " " & Me.cboToHourP1 & ":" & Me.cboToMinuteP1 & ":" & Me.cboToSecondP1

    strSQLP1 = "exec dbo.uspWorkCentreReport '" & strFromDateHMS & "','" & strToDateHMS & "','" & strWCP1 & "'," & strShiftP1

    strOpenArgs = Me.RecordSource & "|" & strFromDateHMS & "|" & strToDateHMS & "|" & strWCP1 & "|" & strShiftP1

    ' This line is all that's needed to modify the PT query
    CurrentDb.QueryDefs("ptq_uspWorkCentreReport").SQL = strSQLP1

    DoCmd.OpenReport "rpt_ptq_uspWorkCentreReport", acViewReport, , , , strOpenArgs

End Sub

And the _MouseDown where the .AllReports is currently:

Private Sub btnPreviewP1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    If CurrentProject.AllReports(rpt_ptq_uspWorkCentreReport).IsLoaded Then
        DoCmd.Close acReport, rpt_ptq_uspWorkCentreReport, acSaveNo
    End If
End Sub

This is the Report_Open:

Private Sub Report_Open(Cancel As Integer)
    Dim SplitOpenArgs() As String
    SplitOpenArgs = Split(Me.OpenArgs, "|")
    Me.lblFromDate.Caption = SplitOpenArgs(1)
    Me.lblToDate.Caption = SplitOpenArgs(2)
    Me.lblWC.Caption = SplitOpenArgs(3)
    Me.lblShift.Caption = SplitOpenArgs(4)
End Sub

`CurrentProject.AllReports().IsLoaded`is the way to go but`AllReports`expects a string as parameter. `OPTION EXPLICT`at top of module would have avoided that error! See https://stackoverflow.com/q/1139321/9439330 — ComputerVersteher, Jun 14 '19 at 19:28
`CurrentProject.AllReports("rpt_ptq_uspWorkCentreReport").IsLoaded` gives correct results. Without the quotes vba assumes`rpt_ptq_uspWorkCentreReport`to be an undeclared variable implicit casted to an empty string`""`, what leads to`CurrentProject.AllReports("").IsLoaded` and result is false, because no report named`""`is loaded;) That error is prevented by [Option Explicit](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/option-explicit-statement) on top of every modul!! — ComputerVersteher, Jun 16 '19 at 05:35

score 4 · Accepted Answer · answered Jun 06 '19 at 17:47

Why not just close report before OpenReport? I modified your code:

Private Sub btnPreviewP1_Click()

    If (Me.txtToDateP1 < Me.txtFromDateP1) Then
        MsgBox ("The From Date must occurr before the To Date!")
    End If

    Dim strFromDateHMS  As String
    Dim strToDateHMS    As String
    Dim strSQLP1    As String
    Dim strOpenArgs As String
    Dim R

    strFromDateHMS = Format(Me.txtFromDateP1, "yyyy-mm-dd") & " " & Me.cboFromHourP1 & ":" & Me.cboFromMinuteP1 & ":" & Me.cboFromSecondP1
    strToDateHMS = Format(Me.txtToDateP1, "yyyy-mm-dd") & " " & Me.cboToHourP1 & ":" & Me.cboToMinuteP1 & ":" & Me.cboToSecondP1

    strSQLP1 = "exec dbo.uspWorkCentreReport '" & strFromDateHMS & "','" & strToDateHMS & "','" & strWCP1 & "'," & strShiftP1

    strOpenArgs = Me.RecordSource & "|" & strFromDateHMS & "|" & strToDateHMS & "|" & strWCP1 & "|" & strShiftP1

    ' This line is all that's needed to modify the PT query
    CurrentDb.QueryDefs("ptq_uspWorkCentreReport").SQL = strSQLP1

    ' Check if report is open and close it without saving:
    For Each R In Reports
        If R.Name = "rpt_ptq_uspWorkCentreReport" Then
            DoCmd.Close acReport, "rpt_ptq_uspWorkCentreReport", acSaveNo
            Exit For
        End If
    Next R

    DoCmd.OpenReport "rpt_ptq_uspWorkCentreReport", acViewReport, , , , strOpenArgs

End Sub

Access report cannot be closed or refreshed from VB with updated parameter set

1 Answers1

Linked