Consulting

Results 1 to 4 of 4

Thread: Need Help with filtering and printing a PDF report

  1. #1

    Need Help with filtering and printing a PDF report

    Hello,

    I have a daily task of printing multiple PDF reports daily. I decided it would be a good idea to just automate the thing. I found the following code which filters to a single report and prints it to file.

    Public Sub ExportFilteredReportToPDF()
    
    
        Dim reportName As String
        Dim fileName As String
        Dim criteria As String
        
        reportName = "Exposure_Enhancement"
        fileName = "C:\Alert_Import\report_export_file.pdf"
        criteria = "[Item Number] = 'Item-2019-728'"
        
        Debug.Print criteria
        
        DoCmd.OpenReport reportName, acViewPreview, , criteria, acHidden
        DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
        DoCmd.Close acReport, reportName, acSaveNo
    
    
    End Sub
    I then wrote the following to loop through all available items, and print them one by one. However, the filtering does not work and each printed PDF contains all of the items. I confirmed criteria and fileName construct properly, so I have no idea. Any help or hints would be immensely appreciated. Thank you.

    Public Sub ExportFilteredReportToPDF2()
    
    
        Dim reportName As String
        Dim fileName As String
        Dim criteria As String
        Dim strSQL As String
        Dim rs As DAO.Recordset
        Dim DataArray As Variant
        Dim outer As Long
        Dim inner As Long
        Dim rowString As String
        
        strSQL = "SELECT * FROM Item_Numbers"
        Set rs = CurrentDb.OpenRecordset(strSQL)
        rs.MoveLast
        rs.MoveFirst
        
        DataArray = rs.GetRows(rs.RecordCount)
        
        reportName = "Exposure_Enhancement"
        
        
        
         For outer = LBound(DataArray, 1) To UBound(DataArray, 1)
            For inner = LBound(DataArray, 2) To UBound(DataArray, 2)
               rowString = DataArray(outer, inner)
               fileName = """" & "C:\Item_Import\PDFSave\" & rowString & ".pdf" & """"
               criteria = """" & "[Item Number] = " & "'" & rowString & "'" & """"
               DoCmd.OpenReport reportName, acViewPreview, criteria, acHidden
               DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName
               DoCmd.Close acReport, reportName, acSaveNo
            Next
        Next
        
        'cleanup
        rs.Close
        Set rs = Nothing
    
    
    End Sub

  2. #2
    Hello,

    I was actually constructing the strings wrong, hence the error. Thank you.

  3. #3
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,096
    Location

  4. #4
    VBAX Newbie
    Joined
    Dec 2019
    Location
    New York
    Posts
    3
    Location
    Hello! I am glad that you were able to deal with your problem yourself. I will be happy to help you next time: друзья:

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •