Access打开报表时崩溃及MsgBox重复弹出问题求助

我需要实现的功能是：报表仅在从窗体应用筛选后才能保持打开。打开报表时检查筛选条件，若筛选为空，则弹出提示框、关闭报表并打开DataByAgencyF窗体。但实际运行出现两个问题：

• 直接从数据库打开报表时，报表关闭、窗体打开，但提示框弹出两次；
• 从窗体打开报表（本应自动应用筛选）时，Access直接崩溃。

报表AgencyOutcomesR代码 #

Private Sub Report_Close()
   Me.ReportCaption.Caption = ""
   Me.Filter = ""
End Sub


Private Sub Report_Open(Cancel As Integer)

    Dim filt As String
    filt = Me.Filter
    'MsgBox filt
    
    If Me.Filter = "" Then
       Me.ReportCaption.Visible = False
       DoCmd.Close acReport, Me.Name
       DoCmd.OpenForm "DataByAgencyF", acNormal
       MsgBox "Data is unfiltered. Please apply filter."
       Exit Sub
    Else: Me.ReportCaption.Visible = True
       Dim strAgency As String, strString As Long
       strAgency = filt     
       strStart = InStr(1, strAgency, "'")
       strAgency = Mid(strAgency, strStart + 1)
       strAgency = Left(strAgency, Len(strAgency) - 1)
       Me.ReportCaption.Caption = strAgency & " Case Outcomes"
    End If

    If CurrentProject.AllForms("AgencyOutcomesF").IsLoaded = True Then
        Me.Filter = Forms![AgencyOutcomesF].Filter
        Me.FilterOn = Forms![AgencyOutcomesF].FilterOn
        Me.OrderByOn = Forms![AgencyOutcomesF].OrderByOn
        Me.OrderBy = Forms![AgencyOutcomesF].OrderBy
    End If

End Sub

[DataByAgencyF]窗体包含一个显示所有机构的组合框，用户可通过选择筛选两个子窗体的数据。

子窗体AgencyOutcomesF按钮代码 #

Private Sub OpenReportBtn_Click()
    Dim filt As String
    filt = Parent!Agencies
    MsgBox filt
    DoCmd.OpenReport "AgencyOutcomesR", acViewPreview, , "[Agency]='" & filt & "'"
End Sub

问题原因及修复方案 #

1. 直接打开报表时提示框重复弹出的修复 #

原因：在Report_Open事件中调用DoCmd.Close会触发Report_Close事件，而Report_Close里重置Me.Filter = ""，导致报表关闭过程中触发额外逻辑，引发提示框重复弹出。

修复：使用Cancel = True取消报表打开操作，而非手动关闭，这样不会触发Report_Close事件：

If Me.Filter = "" Then
   Cancel = True ' 取消报表打开，避免触发Close事件
   DoCmd.OpenForm "DataByAgencyF", acNormal
   MsgBox "数据未筛选，请先应用筛选条件。"
   Exit Sub
End If

2. 从窗体打开报表崩溃的修复 #

原因：

• 报表Report_Open中重复设置筛选条件，覆盖了打开报表时传入的筛选，引发冲突；
• 未声明strStart变量（默认变体型），且存在未使用的strString变量，导致运行时错误；
• 筛选字符串解析逻辑未做容错处理，若筛选格式异常会引发错误。

修复：

• 移除报表中对AgencyOutcomesF窗体筛选的重复设置，删除以下代码块：

  If CurrentProject.AllForms("AgencyOutcomesF").IsLoaded = True Then
      Me.Filter = Forms![AgencyOutcomesF].Filter
      Me.FilterOn = Forms![AgencyOutcomesF].FilterOn
      Me.OrderByOn = Forms![AgencyOutcomesF].OrderByOn
      Me.OrderBy = Forms![AgencyOutcomesF].OrderBy
  End If
  

• 声明所有变量，删除未使用的strString，并优化解析逻辑：

  Else
     Me.ReportCaption.Visible = True
     Dim strAgency As String, strStart As Long
     strAgency = filt     
     strStart = InStr(1, strAgency, "'")
     If strStart > 0 Then ' 增加容错判断，避免找不到单引号时出错
         strAgency = Mid(strAgency, strStart + 1)
         strAgency = Left(strAgency, Len(strAgency) - 1)
         Me.ReportCaption.Caption = strAgency & " 案件处理结果"
     End If
  End If
  

3. 清理Report_Close事件冗余代码 #

Report_Close中Me.Filter = ""属于冗余操作，报表关闭后属性会自动重置，且会干扰正常逻辑，建议删除：

Private Sub Report_Close()
   Me.ReportCaption.Caption = ""
   ' 移除 Me.Filter = ""
End Sub

内容的提问来源于stack exchange，提问作者Colin Carmello