遍历记录集以将Ms-Access报告输出到pdf文件 [英] Looping through a recordset to output Ms-Access report to a pdf file

问题描述

我正在尝试使用docmd.outputTo通过tenant_id获取pdf文件.不幸的是，该例程产生的输出pdf文件全部都在同一个tenant_id中.如果删除docmd.outputTo最后一个参数pathName & fileName，则需要通过对话框输入文件名，并通过tenant_id很好地过滤输出文件.任何帮助，将不胜感激.

I'm trying to get pdf file by tenant_id using docmd.outputTo. Unfortunately this routine produce output pdf file that all are in a same single tenant_id. If I remove docmd.outputTo last parameter pathName & fileName then it's require file name through dialogue and output file nicely filtered by tenant_id. Any help would be appreciated.

这是发票查询:SELECT * FROM tblInvoice WHERE tenant_id = CurTenantID()

Public Sub Output()
    Dim MyRs As DAO.Recordset
    Dim rpt As Report
    Dim fileName As String, pathName As String, todayDate As String

    pathName = "C:\Users\abzalali\Dropbox\tenant_db\Invoice\"
    todayDate = Format(Date, "MMDDYYYY")
    Set MyRs = CurrentDb.OpenRecordset("SELECT tenant_id, name, company, email FROM qryEmailClientList")

    DoCmd.OpenReport "Invoice", acPreview, , , acHidden
    Set rpt = Reports("Invoice")

    With MyRs
        .MoveFirst
            Do While Not .EOF
                fileName = "Invoice_" & todayDate & !tenant_id & ".pdf"
                rpt.Filter = "[tenant_id] = " & !tenant_id
                rpt.FilterOn = True
                DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, pathName & fileName
               .MoveNext
            Loop
    End With

End Sub

推荐答案

由于DoCmd.OutputTo缺少过滤参数，因此最好的选择是将报表基于公共函数以获取当前ID.

Since DoCmd.OutputTo lacks parameters for filtering, the best option is to base the report on a public function to get the current ID.

例如

' Function to both set and retrieve the current Tenant ID
Public Function CurTenantID(Optional SetTenantID As Long = 0) As Long

    Static TenantID As Long

    If SetTenantID > 0 Then
        TenantID = SetTenantID
    End If

    CurTenantID = TenantID

End Function

您的报告在其记录源中使用此功能，例如

Your report uses this function in its record source, e.g.

SELECT * FROM tblInvoice WHERE tenant_id = CurTenantID()

在循环中生成PDF报告时，请使用SetTenantID参数:

And when generating the PDF reports in the loop, you use the SetTenantID parameter:

Public Sub Output()

    Dim MyRs As DAO.Recordset
    Dim fileName As String, pathName As String, todayDate As String

    pathName = "C:\Users\abzalali\Dropbox\tenant_db\Invoice\"
    todayDate = Format(Date, "MMDDYYYY")
    Set MyRs = CurrentDb.OpenRecordset("SELECT tenant_id, name, company, email FROM qryEmailClientList")

    With MyRs
        ' .MoveFirst -- unneeded after OpenRecordset()
        Do While Not .EOF
            fileName = "Invoice_" & todayDate & !tenant_id & ".pdf"
            Call CurTenantID(!tenant_id)
            DoCmd.OutputTo acOutputReport, "Invoice", acFormatPDF, pathName & fileName
            .MoveNext
        Loop
    End With

End Sub

这篇关于遍历记录集以将Ms-Access报告输出到pdf文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！