Export to different names

[RevJeff]

Hello everyone,

I am trying to export a multi page report to .pdf files. Each page of the report is a different file, so if there are 50 pages to the report, there should be 50 different .pds files, each with a unique name. I can get it to loop through every page and export a file, with this code, but doesn't create a new name and it just over writes the file 50 times.

outputFileName = CurrentProject.Path & "\PreRegCodes_" & ".pdf"

But if I try to add the [CustName] field, I get a "Type Mismatch" Error

outputFileName = CurrentProject.Path & "\PreRegCodes_" & [CustName] & ".pdf"

Below is my complete code. I want the export names to be "PreRegCodes_[CustName]

Public Sub PreRegExport()
    Dim MyRs As DAO.Recordset
    Dim rpt As Report
    Dim outputFileName As String
   
    Set MyRs = CurrentDb.OpenRecordset("qryPreRegBarcode_pdf")
    DoCmd.OpenReport "rptFoodshowPreReg", acPreview, , , acHidden
    Set rpt = Reports("rptFoodshowPreReg")
   
    With MyRs
        .MoveFirst
            Do While Not .EOF
                rpt.Filter = "[PreRegCode] = " & !PreRegCode
                rpt.FilterOn = True
                outputFileName = CurrentProject.Path & "\PreRegCodes_" & [CustName] & ".pdf"
                DoCmd.OutputTo acOutputReport, "rptFoodshowPreReg", acFormatPDF, outputFileName
                .MoveNext
            Loop
    End With
End Sub

Thanks for your help

 

[Minty]

You aren't referring to the rs.CustName in the loop so it will stay the same.

 

[RevJeff]

Thank you for your response Minty. Can you explain how to do that?

 

[jdraw]

I would replace [CustName] with ![CustName] and try.

What is the sql behind qryPreRegBarcode_pdf?

 

[RevJeff]

Ok, jdraw, tried putting the "!" in front of [CustName] and it now creates the individual .pdf files correctly named, but the actually data on the report is the same for all of the files. It is the first persons data. That isn't changing.

 

[RevJeff]

Oh, here is the sql for qryPreRegBarcode_pdf

SELECT tblPreReg.PreRegCode, tblPreReg.Cust_No, tblPreReg.Cust_Name, tblPreReg.Cust_Addr1, tblPreReg.Cust_Addr2, tblPreReg.Cust_City, tblPreReg.Cust_State, tblPreReg.Cust_Zip, tblPreReg.Cust_Phone, tblPreReg.Cust_EMail, [Cust_FName] & " " & [Cust_LName] AS CustName
FROM tblPreReg;

 

[jdraw]

I have a feeling, your filter is restricting the data such that it isn't changing. That's just my best guess.

Seems you open the report then set a filter which constrains the report.
In my view you have to somehow make sure you are using the proper filter as you move through the recordset. But I don't know your application.
Can you post a zip of the database?

The logic (pure air code)

Get next record in the recordset
Open the report with proper filter
process the report and output to proper file
Loop back to get next record.

 

[jdraw]

RevJeff,
I mocked up your data and report with something I had available.
I rearranged the logic and got the code to produce a separate report for each record in my ancestor table.

Here is the revised code using my table [ancestor]. My table contained the field [name] rather than CustName. My report is rpt_ancestor. I concocted the field preregcode. Note that I have commented some of your code and moved it and used my names.

Public Sub PreRegExport()
    Dim MyRs As DAO.Recordset
    Dim rpt As Report
    Dim outputFileName As String
   
    'Set MyRs = CurrentDb.OpenRecordset("qryPreRegBarcode_pdf")
    Set MyRs = CurrentDb.OpenRecordset("ancestor")
    'DoCmd.OpenReport "rptFoodshowPreReg", acPreview, , , acHidden
    'Set rpt = Reports("rptFoodshowPreReg")
   
    With MyRs
        .MoveFirst
            Do While Not .EOF
             Set rpt = Reports("rpt_ancestor")
            DoCmd.OpenReport "rpt_ancestor", acPreview, , , acHidden
            Set rpt = Reports("rpt_ancestor")
                rpt.Filter = "[PreRegCode] = " & !PreRegCode
                rpt.FilterOn = True
              
                outputFileName = CurrentProject.Path & "\PreRegCodes_" & ![name] & ".pdf"
                'DoCmd.OutputTo acOutputReport, "rptFoodshowPreReg", acFormatPDF, outputFileName
                DoCmd.OutputTo acOutputReport, "rpt_ancestor", acFormatPDF, outputFileName
                .MoveNext
            Loop
    End With
End Sub

Substitute your query name for my table name; and your ![Custname] for my ![name]. Also substitute your report name for my rpt_ancestor.

Here is my ancestor table:

name	id	deathdate	birthdate	preregcode
sam		12/03/1976	01/11/1922	21
bob	1	13/03/1976	31/10/1922	44
jim	2	17/09/1964	22/03/1944	63
ken	345	07/02/2006	09/09/1999	82

I hope it's useful.
Good luck.

 

[RevJeff]

Thanks again for your help, but the reports are still only putting the first record in each report and not moving to the second record. It is creating the correct names for the .pdf files though.

If you open the files you created, does the data on the reports change?

 

[jdraw]

RevJeff,

No. I'm still researching and was just deleting my post when I saw your response.
I'll try a few more things and get back.

I added a close on the report, but it didn't fix the issue.
Not sure why it is keeping the data from first record.
I'll try a few more things and get back.

It did process the records, but it did not change the data.

I added some debug pronts to ensure it was looping ok.

 44 bob  C:\Users\mellon\Documents\PreRegCodes_bob.pdf
 63 jim  C:\Users\mellon\Documents\PreRegCodes_jim.pdf
 21 sam  C:\Users\mellon\Documents\PreRegCodes_sam.pdf
 82 ken  C:\Users\mellon\Documents\PreRegCodes_ken.pdf

 

[RevJeff]

Thanks again! I'll keep trying things and if I come up with something I'll let you know.

 

[jdraw]

RevJeff,

I replaced the Filter with a WHERE condition in the OpenReport, and modified the loop to include a DoCmd.Close the report on each iteration.

Seems to work fine for me with this code.

Public Sub PreRegExport()
    Dim MyRs As DAO.Recordset
    Dim rpt As Report
    Dim outputFileName As String
   
    'Set MyRs = CurrentDb.OpenRecordset("qryPreRegBarcode_pdf")
    Set MyRs = CurrentDb.OpenRecordset("ancestor")
    'DoCmd.OpenReport "rptFoodshowPreReg", acPreview, , , acHidden
    'Set rpt = Reports("rptFoodshowPreReg")
   
    With MyRs
        .MoveFirst
            Do While Not .EOF
            
            DoCmd.OpenReport "rpt_ancestor", acViewPreview, , "[PreRegCode] = " & !preregcode, acHidden
           ' Set rpt = Reports("rpt_ancestor")
           '     rpt.Filter = "[PreRegCode] = " & !PreRegCode
           '     rpt.FilterOn = True
              
                outputFileName = CurrentProject.Path & "\PreRegCodes_" & ![name] & ".pdf"
                'DoCmd.OutputTo acOutputReport, "rptFoodshowPreReg", acFormatPDF, outputFileName
              DoCmd.OutputTo acOutputReport, "rpt_ancestor", acFormatPDF, outputFileName
              Debug.Print !preregcode; ![name]; "  " & outputFileName
              DoCmd.Close acReport, "rpt_ancestor"
                .MoveNext
            Loop
    End With
End Sub

Reports contain single record for the proper PreRegCode.

Good luck.

Note: I got all records when using the filter??? So went with the Where clause.

 

[Cronk]

The problem was that the filter was being applied to an instance of the Report (rpt), not the report itself.

One way to solve the problem is to modify the source query in the loop.

 

[jdraw]

So that's why the Where clause works and the Filter doesn't, I guess.