时间戳

首页 > 解决方案 > 使用更新的参数集无法从 VB 关闭或刷新访问报表

vba - 使用更新的参数集无法从 VB 关闭或刷新访问报表

问题描述

单击表单的按钮会打开一个包含数据的访问报告。这些参数与对返回记录的 SQL 存储过程的传递查询一起使用。报告没有出现模态，我希望它保持这种状态。但是，如果用户在返回表单并尝试设置新参数之前没有关闭报表，则报表会在后台保持打开状态，并且单击按钮后，报表会以旧参数和数据出现并且不会刷新带有新的参数/数据。

一种选择是对报告使用模态，但这会导致用户不得不主动关闭报告的粗略过渡。另一种选择是在重试期间关闭报告，这是我一直在尝试的。我努力了：

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

在几个不同的位置：_MousedDown，作为 _Click 中的第一个 If，以及 _BeforeInsert。每次 CurrentProject.AllReports(rpt_ptq_uspWorkCentreReport).IsLoaded 在报告位于后台并且正在使用下一次尝试新参数重新处理表单时，在第二次传递期间出现错误。同样在第二次尝试期间，.OpenReport 行失败并出现 SQL 错误，因为 strSQLP1 不完整。这是 _Click 事件：

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

以及 .AllReports 当前所在的 _MouseDown：

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

这是 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

标签： vbams-accessms-access-reports

解决方案

为什么不在 OpenReport 之前关闭报告？我修改了你的代码：

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

推荐阅读