Output file name

[pensived]

I have a report that exports to PDF with the shipment number as the filename, but I want the file name to include the values from two other fields [CSR] and [facility] in the same query. Nothing I do works. Any Help??

Private Sub Command30_Click()
 
  
  Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MyFileName As String
    Dim mypath As String
    Dim temp As String


    mypath = "c:\paf\reports\"

    Set db = CurrentDb()

    Set rs = db.OpenRecordset("SELECT [shipment] FROM [headerreportquery]", dbOpenSnapshot)

    Do While Not rs.EOF

        temp = rs("shipment")
        MyFileName = rs("shipment") & ".PDF"

        DoCmd.OpenReport "pafreport", acViewReport, , "[shipment]=" & temp & ""

        DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
        DoCmd.Close acReport, "pafreport"
        DoEvents

        rs.MoveNext
    Loop


    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

 

[Minty]

It should be something like

  MyFileName = rs("shipment") & "_" &  rs("CSR") & "_" & rs("facility") & ".PDF"

depending on the format you want.

This will only work if all fields concerned haven't got any special characters in them. e.g. / \ # .' etc etc as they will be invalid for a file name.

 

[pensived]

Thank you for the reply, but it generates an Error "item not found in this collection". I'm pretty sure I need to include the fields in the recordset statement, but I can't figure it out.

 

[Minty]

Oops sorry I missed you were only bringing in the one field. Change this line;

 Set rs = db.OpenRecordset("SELECT [shipment][COLOR="Red"],[CSR],[facility][/COLOR] FROM [headerreportquery]", dbOpenSnapshot)

Assuming those fields are in that report query.

 

[pensived]

Awesome!! That did it, thank you SO MUCH.