How to output multiple PDF files based on record i

问题:

I'm new on the MS Access, now I have a report based on the table with 100 records. I want to output the pdf files based on each record. It means each record will have its own single pdf file and the file name will be based on the record's column name.

I have searched on the Internet and I found this vba code is work.

Option Compare Database
Option Explicit

Private Sub Command4_Click()
  Dim rsGroup As DAO.Recordset
  Dim ColumnName As String, myPath As String

  myPath = "C:\test\"

  Set rsGroup = CurrentDb.OpenRecordset("SELECT DISTINCT columnName FROM Table_Name", _
                                    dbOpenDynaset)
  Do While Not rsGroup.EOF
  ColumnName = rsGroup!columnName 

  ' OPEN REPORT, FILTERING RECORDSOURCE BY COLUMN VALUE
  DoCmd.OpenReport "Table_Name_Report", acViewPreview, , "Column='" & ColumnName & "'"
  ' OUTPUT REPORT TO FILE
  DoCmd.OutputTo acOutputReport, "Table_Name_Report", acFormatPDF, _
                                myPath & ColumnName & ".pdf", False
  ' CLOSE PREVIEW
  DoCmd.Close acReport, "Table_Name_Report"

 rsGroup.MoveNext
 Loop

 rsGroup.Close

I never used VBA, and every time I run this code, it will return a window and let me input the column record value. That's not automatically, how to change the code to let it read the record value automatically and populate the pdf?

回答1:

The way I would do this is to just dynamically set the query the report is based on (i.e. the recordsource) complete with the "columnName". Something like:

(code not run)

Public Sub cmdOpenMyReport_Click()

    Dim strSQL As String
  Dim rsGroup As DAO.Recordset
  Dim ColumnName As String, myPath As String

  myPath = "C:\test\"

  Set rsGroup = CurrentDb.OpenRecordset("SELECT DISTINCT pharmacyName FROM Sfwy_Patients_New", dbOpenDynaset)

  Do Until rsGroup.EOF
    ColumnName = rsGroup!pharmacyName
    strSQL = "SELECT ... WHERE Column = " & COLUMName & ";"  'copy sql from the report record source and put in the column name as a variable
    CurrentDb.QueryDefs("myReportRecordSource").SQL = strSQL 

  ' OUTPUT REPORT TO FILE
  DoCmd.OutputTo acOutputReport, "Sfwy_Patients_New_Report", acFormatPDF, _
                                myPath & ColumnName & ".pdf", False
    rsGroup.movenext
  Loop

end sub