Trying to create a separate pdf file for every record displayed in a Report

[IndianaITGuy]

I created a report with several subreports based on the following query:

SELECT    tblSegment.[Item No],
    tblSegment.[Orig PPW],
    tblSegment.Descr,
    tblRawMaterial.Type AS [Raw Matl Type],
    tblRawMaterial.Gauge AS [Raw Matl Gauge],
    tblRawMaterial.Color AS [Raw Matl Color],
    tblRawMaterial.CMD AS [Raw Matl CMD],
    tblRawMaterial.MD AS [Raw Matl MD],
    tblRawMaterial.[No Sheets] AS [Raw Matl  No Sheets],
    tblRawMaterial.[Per Sheet] AS [Raw Matl Per Sheet],
    tblRawMaterial.[Notes/Spec Instr] AS [Raw Matl Notes],
    [tblPre-TrimA].CnC AS [PreTrimA CnC],
    [tblPre-TrimA].[Paper Pattern] AS [PreTrimA Paper Pattern],
    [tblPre-TrimA].[Blank Spec] AS [PreTrimA Blank Spec],
    [tblPre-TrimA].[Coating Holes Side] AS [PreTrimA Coating Holers Side],
    [tblPre-TrimA].[Deburr Coating Edges] AS [PreTrimA Deburr Coating Edges],
    [tblPre-TrimA].[Deburr Coating Holes] AS [PreTrimA Deburr Coating Holes],
    [tblPre-TrimA].[Blank Side] AS [PreTrimA Blank Side],
    [tblPre-TrimA].[Writing Side] AS [PreTrimA Writing Side],
    [tblPre-TrimA].[Corner Zero] AS [PreTrimA Corner Zero],
    [tblPre-TrimA].[Pin Zero] AS [PreTrimA Pin Zero],
    [tblPre-TrimA].T1 AS [PreTrimA T1],
    [tblPre-TrimA].T2 AS [PreTrimA T2],
    [tblPre-TrimA].T3 AS [PreTrimA T3],
    [tblPre-TrimA].T4 AS [PreTrimA T4],
    [tblPre-TrimA].T5 AS [PreTrimA T5],
    [tblPre-TrimA].T6 AS [PreTrimA T6],
    [tblPre-TrimA].T7 AS [PreTrimA T7],
    [tblPre-TrimA].T8 AS [PreTrimA T8],
    [tblPre-TrimA].T9 AS [PreTrimA T9],
    [tblPre-TrimA].T10 AS [PreTrimA T10],
    [tblPre-TrimA].T11 AS [PreTrimA T11],
    [tblPre-TrimA].T12 AS [PreTrimA T12],
    [tblPre-TrimA].T13 AS [PreTrimA T13],
    [tblPre-TrimA].T14 AS [PreTrimA T14],
    [tblPre-TrimA].T15 AS [PreTrimA T15],
    [tblPre-TrimA].Program AS [PreTrimA Program],
    [tblPre-TrimA].[Program No] AS [PreTrimA Program No],
    [tblPre-TrimA].Spoilboard AS [PreTrimA Spoilboard],
    [tblPre-TrimA].[Notes/Spec Instr] AS [PreTrimA Notes],
    [tblPre-TrimA].[Send Parts To] AS [PreTrimA Send Parts To],
    [tblPre-TrimB].CnC AS [PreTrimB CnC],
    [tblPre-TrimB].[Paper Pattern] AS [PreTrimB Paper Pattern],
    [tblPre-TrimB].[Blank Spec] AS [PreTrimB Blank Spec],
    [tblPre-TrimB].[Coating Holes Side] AS [PreTrimB Coating Holes Side],
    [tblPre-TrimB].[Deburr Coating Edges] AS [PreTrimB Deburr Coating Edges],
    [tblPre-TrimB].[Deburr Coating Holes] AS [PreTrimB Deburr Coating Holes],
    [tblPre-TrimB].[Blank Side] AS [PreTrimB Blank Side],
    [tblPre-TrimB].[Writing Side] AS [PreTrimB  Writing Side],
    [tblPre-TrimB].[Corner Zero] AS [PreTrimB Corner Zero],
    [tblPre-TrimB].[Pin Zero] AS [PreTrimB Pin Zero],
    [tblPre-TrimB].T1 AS [PreTrimB T1],
    [tblPre-TrimB].T2 AS [PreTrimB T2],
    [tblPre-TrimB].T3 AS [PreTrimB T3],
    [tblPre-TrimB].T4 AS [PreTrimB T4],
    [tblPre-TrimB].T5 AS [PreTrimB T5],
    [tblPre-TrimB].T6 AS [PreTrimB T6],
    [tblPre-TrimB].T7 AS [PreTrimB T7],
    [tblPre-TrimB].T8 AS [PreTrimB T8],
    [tblPre-TrimB].T9 AS [PreTrimB T9],
    [tblPre-TrimB].T10 AS [PreTrimB T10],
    [tblPre-TrimB].T11 AS [PreTrimB T11],
    [tblPre-TrimB].T12 AS [PreTrimB T12],
    [tblPre-TrimB].T13 AS [PreTrimB T13],
    [tblPre-TrimB].T14 AS [PreTrimB T14],
    [tblPre-TrimB].T15 AS [PreTrimB T15],
    [tblPre-TrimB].Program AS [PreTrimB Program],
    [tblPre-TrimB].[Program No] AS [PreTrimB Program No],
    [tblPre-TrimB].Spoilboard AS [PreTrimB Spoilboard],
    [tblPre-TrimB].[Notes/Spec Instr] AS [PreTrimB Notes],
    [tblPre-TrimB].[Send Parts To] AS [PreTrimB Send Parts To],
    tblForming.Mold AS [Forming Mold],
    tblForming.Oven AS [Forming Oven],
    tblForming.Temp AS [Forming Temp],
    tblForming.Time AS [Forming Time],
    tblForming.Peel AS [Forming Peel],
    tblForming.[Heat W/] AS [Forming Heat With],
    tblForming.[Notes/Spec Instr] AS [Forming Notes],
    tblCoating.Primer AS [Coating Primer],
    tblCoating.Coating,
    tblCoating.Sides AS [Coating Sides],
    tblCoating.Oven AS [Coating Oven],
    tblCoating.Time AS [Coating Time],
    tblCoating.Temp AS [Coating Temp],
    tblCoating.[Notes/Spec Instr] AS [Coating Notes],
    tblDownstacking.[Notes/Spec Instr] AS [Downstacking Notes],
    [tblFinish-TrimA].CnC AS [Finish-TrimA CnC],
    [tblFinish-TrimA].Fixture AS [Finish-TrimA Fixture],
    [tblFinish-TrimA].UAO AS [Finish-TrimA UAO],
    [tblFinish-TrimA].[Blank Side] AS [Finish-TrimA Blank Side],
    [tblFinish-TrimA].[Writing Side] AS [Finish-TrimA Writing Side],
    [tblFinish-TrimA].[Corner Zero] AS [Finish-TrimA Corner Zero],
    [tblFinish-TrimA].[Pin Zero] AS [Finish-TrimA Pin Zero],
    [tblFinish-TrimA].T1 AS [Finish-TrimA T1],
    [tblFinish-TrimA].T2 AS [Finish-TrimA T2],
    [tblFinish-TrimA].T3 AS [Finish-TrimA T3],
    [tblFinish-TrimA].T4 AS [Finish-TrimA T4],
    [tblFinish-TrimA].T5 AS [Finish-TrimA T5],
    [tblFinish-TrimA].T6 AS [Finish-TrimA T6],
    [tblFinish-TrimA].T7 AS [Finish-TrimA T7],
    [tblFinish-TrimA].T8 AS [Finish-TrimA T8],
    [tblFinish-TrimA].T9 AS [Finish-TrimA T9],
    [tblFinish-TrimA].T10 AS [Finish-TrimA T10],
    [tblFinish-TrimA].T11 AS [Finish-TrimA T11],
    [tblFinish-TrimA].T12 AS [Finish-TrimA T12],
    [tblFinish-TrimA].T13 AS [Finish-TrimA T13],
    [tblFinish-TrimA].T14 AS [Finish-TrimA T14],
    [tblFinish-TrimA].T15 AS [Finish-TrimA T15],
    [tblFinish-TrimA].Program AS [Finish-TrimA Program],
    [tblFinish-TrimA].[Program No] AS [Finish-TrimA Program No],
    [tblFinish-TrimA].Spoilboard AS [Finish-TrimA Spoilboard],
    [tblFinish-TrimA].[Notes/Spec Instr] AS [Finish-TrimA Notes],
    [tblFinish-TrimA].[X Coord] AS [Finish-TrimA X Coord],
    [tblFinish-TrimA].[Y Coord] AS [Finish-TrimA Y Coord],
    [tblFinish-TrimA].[Z Coord] AS [Finish-TrimA Z Coord],
    [tblFinish-TrimB].CnC AS [Finish-TrimB CnC],
    [tblFinish-TrimB].Fixture AS [Finish-TrimB Fixture],
    [tblFinish-TrimB].UAO AS [Finish-TrimB UAO],
    [tblFinish-TrimB].[Blank Side] AS [Finish-TrimB Blank Side],
    [tblFinish-TrimB].[Writing Side] AS [Finish-TrimB Writing Side],
    [tblFinish-TrimB].[Corner Zero] AS [Finish-TrimB Corner Zero],
    [tblFinish-TrimB].[Pin Zero] AS [Finish-TrimB Pin Zero],
    [tblFinish-TrimB].T1 AS [Finish-TrimB T1],
    [tblFinish-TrimB].T2 AS [Finish-TrimB T2],
    [tblFinish-TrimB].T3 AS [Finish-TrimB T3],
    [tblFinish-TrimB].T4 AS [Finish-TrimB T4],
    [tblFinish-TrimB].T5 AS [Finish-TrimB T5],
    [tblFinish-TrimB].T6 AS [Finish-TrimB T6],
    [tblFinish-TrimB].T7 AS [Finish-TrimB T7],
    [tblFinish-TrimB].T8 AS [Finish-TrimB T8],
    [tblFinish-TrimB].T9 AS [Finish-TrimB T9],
    [tblFinish-TrimB].T10 AS [Finish-TrimB T10],
    [tblFinish-TrimB].T11 AS [Finish-TrimB T11],
    [tblFinish-TrimB].T12 AS [Finish-TrimB T12],
    [tblFinish-TrimB].T13 AS [Finish-TrimB T13],
    [tblFinish-TrimB].T14 AS [Finish-TrimB T14],
    [tblFinish-TrimB].T15 AS [Finish-TrimB T15],
    [tblFinish-TrimB].Program AS [Finish-TrimB Program],
    [tblFinish-TrimB].[Program No] AS [Finish-TrimB Program No],
    [tblFinish-TrimB].Spoilboard AS [Finish-TrimB Spoilboard],
    [tblFinish-TrimB].[Notes/Spec Instr] AS [Finish-TrimB Notes],
    [tblFinish-TrimB].[X Coord] AS [Finish-TrimB X Coord],
    [tblFinish-TrimB].[Y Coord] AS [Finish-TrimB Y Coord],
    [tblFinish-TrimB].[Z Coord] AS [Finish-TrimB Z Coord],
    tblMasking.StaticInst AS [Masking Static Instr],
    tblMasking.[Notes/Spec Instr] AS [Masking Notes],
    [qryTraveler - Painting].Paint,
    [qryTraveler - Painting].[Notes/Spec Instr] AS [Painting Notes]
FROM
    (
        (
            (
                (
                    (
                        (
                            (
                                (
                                    (
                                        tblSegment
                                        LEFT JOIN tblRawMaterial ON (tblSegment.[Orig PPW] = tblRawMaterial.[Orig PPW])
                                        AND (tblSegment.[Item No] = tblRawMaterial.[Item No])
                                    )
                                    LEFT JOIN [tblPre-TrimA] ON (tblSegment.[Orig PPW] = [tblPre-TrimA].[Orig PPW])
                                    AND (tblSegment.[Item No] = [tblPre-TrimA].[Item No])
                                )
                                LEFT JOIN [tblPre-TrimB] ON (tblSegment.[Orig PPW] = [tblPre-TrimB].[Orig PPW])
                                AND (tblSegment.[Item No] = [tblPre-TrimB].[Item No])
                            )
                            LEFT JOIN tblForming ON (tblSegment.[Orig PPW] = tblForming.[Orig PPW])
                            AND (tblSegment.[Item No] = tblForming.[Item No])
                        )
                        LEFT JOIN tblCoating ON (tblSegment.[Orig PPW] = tblCoating.[Orig PPW])
                        AND (tblSegment.[Item No] = tblCoating.[Item No])
                    )
                    LEFT JOIN tblDownstacking ON (
                        tblSegment.[Orig PPW] = tblDownstacking.[Orig PPW]
                    )
                    AND (tblSegment.[Item No] = tblDownstacking.[Item No])
                )
                LEFT JOIN [tblFinish-TrimA] ON (
                    tblSegment.[Orig PPW] = [tblFinish-TrimA].[Orig PPW]
                )
                AND (
                    tblSegment.[Item No] = [tblFinish-TrimA].[Item No]
                )
            )
            LEFT JOIN [tblFinish-TrimB] ON (
                tblSegment.[Orig PPW] = [tblFinish-TrimB].[Orig PPW]
            )
            AND (
                tblSegment.[Item No] = [tblFinish-TrimB].[Item No]
            )
        )
        LEFT JOIN tblMasking ON (tblSegment.[Orig PPW] = tblMasking.[Orig PPW])
        AND (tblSegment.[Item No] = tblMasking.[Item No])
    )
    INNER JOIN [qryTraveler - Painting] ON (
        tblSegment.[Orig PPW] = [qryTraveler - Painting].[Orig PPW]
    )
    AND (
        tblSegment.[Item No] = [qryTraveler - Painting].[Item No]

    );

The report works fine. It displays a set of subreports for every record retrieved by the query. But I want to now create a separate pdf file for each record processed by the query. instead, the best I can do is create copies of the same report, all with different filenames. Here is the VBA:

Private Sub Command0_Click()

        Dim rs As DAO.Recordset
        Dim db As DAO.Database
        Dim reportName As String
        Dim outputFolder As String
        Dim filenameField As String
        Dim fileName As String
        Dim RecordSetVar As String


        reportName = "rptTraveler" ' <<!nav>>Replace with your actual report name<<!/nav>>
        outputFolder = "S:\IT Stuff\SysAdmin\MISys\MiSys Migration\Routing\Traveler PDFs\" ' <<!nav>>Replace with your desired folder path<<!/nav>>
        filenameField = "Item No" ' <<!nav>>Replace with the name of the field you want to use for the filename<<!/nav>>
        RecordSetVar = "qryTraveler"


        Set db = CurrentDb
        ' Open a recordset based on your report's record source to loop through records
        Set rs = db.OpenRecordset(RecordSetVar) ' Or however your report is sourced
        


        rs.MoveFirst
        Do While Not rs.EOF
            ' Construct the unique filename
            fileName = outputFolder & rs(filenameField).Value & ".pdf"


            ' Apply a filter to the report for the current record
            DoCmd.OpenReport reportName, acViewPreview, , "'" & filenameField & " = " & rs(filenameField).Value & "'"
            
            ' Export the filtered report to PDF
            DoCmd.OutputTo acOutputReport, reportName, acFormatPDF, fileName


            ' Close the report without saving changes
            DoCmd.Close acReport, reportName


            rs.MoveNext
        Loop


        rs.Close
        Set rs = Nothing
        MsgBox "PDF export complete!", vbInformation


End Sub

[IndianaITGuy]

Attached is an abbreviated version of my Access file.

[June7]

The WHERE CONDITION string construction syntax is wrong. Use:

DoCmd.OpenReport reportName, acViewPreview, , "[" & filenameField & "] = '" & rs(filenameField).Value & "'"

You had a misplaced apostrophe and since you use spaces and punctuation in field names (I don't), need bracket delimiters - never hurts to use anyway.
(BTW, .Value is not necessary).

[jojowhite]

you can also try your db now.
see the code behind the button.

[IndianaITGuy]

The WHERE CONDITION string construction syntax is wrong. Use:

DoCmd.OpenReport reportName, acViewPreview, , "[" & filenameField & "] = '" & rs(filenameField).Value & "'"

You had a misplaced apostrophe and since you use spaces and punctuation in field names (I don't), need bracket delimiters - never hurts to use anyway.
(BTW, .Value is not necessary).

Works great! Thanks!