vba - VBA OutputTo PDF 也在保存空白报告

Question

下面的代码为表单上的员工在 StartDate 和 StopDate 字段之间到期的主管生成 PDF 报告。对于没有在该范围内到期的员工的主管，我收到了多份报告，基本上是空白报告。该代码还提供了包含数据的正确报告。

如何防止 OutputTo 保存空白报告？

参考查询的 SQL (qry_Distinct_Supervisors)：

PARAMETERS StartDate DateTime, StopDate DateTime;
SELECT DISTINCT qry_Base_For_All.Supervisor, qry_Base_For_All.LID, qry_Base_For_All._Status, qry_Base_For_All.LASTNAME, qry_Base_For_All.FIRSTNAME, qry_Base_For_All.[End Date]
FROM qry_Base_For_All
WHERE (((qry_Base_For_All.Supervisor) Is Not Null) AND ((qry_Base_For_All.LASTNAME) Is Not Null) AND ((qry_Base_For_All.[End Date]) Between [StartDate] And [StopDate]));

用于报告的 SQL

SELECT DISTINCT qry_Base_For_All.L_ID, qry_Base_For_All.LASTNAME, qry_Base_For_All.FIRSTNAME, qry_Base_For_All.P_ID, qry_Base_For_All.Company, qry_Base_For_All.[End Date], qry_Base_For_All.Supervisor, qry_Base_For_All.Title
FROM qry_Base_For_All
WHERE (((qry_Base_For_All.[End Date]) Between [Forms]![frm_Bldg_Access]![StartDate] And [Forms]![frm_Bldg_Access]![StopDate]) AND ((qry_Base_For_All.Title) Like "*" & "outsource" & "*"));

VBA 保存报告

Private Sub btn_Print_Report_Click()
   'split report into PDFs named after supervisor and open a separate email with each report attached

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim mypath As String
    Dim temp As String
    Dim qry As QueryDef
    Dim StartDate As DAO.Parameter
    Dim StopDate As DAO.Parameter


    Set db = CurrentDb()
    Set qry = db.QueryDefs("qry_Distinct_Supervisors")
    mypath = "C:\Users\cw52450\Desktop\Test Exports\"
    qry.Parameters("StartDate").Value = Forms!frm_Bldg_Access!StartDate
    qry.Parameters("StopDate").Value = Forms!frm_Bldg_Access!StopDate
    Set rs = qry.OpenRecordset(dbOpenSnapshot)

'populate rs
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveLast
        rs.MoveFirst

'start report generation loop
'Currenlty outputting blank reports as well as needed ones
    Do While Not rs.EOF
        temp = rs("Supervisor")
        MyFileName = rs("Supervisor") & Format(Date, ", mmm yyyy") & ".PDF"
        DoCmd.OpenReport "rpt_Expiring_Access", acViewReport, , "[Supervisor]='" & temp & "'"
        DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
        DoCmd.Close acReport, "rpt_Expiring_Access"
        DoEvents

        rs.MoveNext
    Loop
Else
    MsgBox "There are no records in the recordset."
End If
    MsgBox "Report generation complete."
    Set rs = Nothing
    Set db = Nothing
    Set qry = Nothing
End Sub

标签： vbams-access