Pass parameter from daoRecordset to report

[Les Isaacs]

Hi All
In code I have a recordset rsOrders which includes the field rsOrders!orderID. I need to generate a report for each value of rsOrders!orderID, and then save that report as a PDF with date and the rsOrders!orderID value appended to the filename. So if rsOrders has 15 records I end up with 15 PDFs - e.g. order72345, order15346, etc.

So I want loop through the recordset, then generate the PDF with a DoCmd.OutputTo acOutputReport, "rptOrder", acFormatPDF, ("Order" & Format(Now(), "mmmyyyy") & "-"& rsOrders!orderID & ".pdf"), True

But this obviously does not filter the report for the selected rsOrders!orderID value, and I can't work out how to do that!

Hope someone can help.
Thanks i advance.
Les

 

[Ranman256]

you pass params to the query, not the report.

i have a list box connected to the query that shows the Order# in the query.
the code below will cycle thru the list and generate a report, 1 for each Order#.
the query in the report looks at the listbox and uses that #.

ie: select * from table where [order#]=forms!myForm!lstBox

sub btnPrintRpts_click()
dim vOrder, vStamp
dim i as integer
dim sFile as string

vStamp = format(date(),"mmmyyyy")
for i = 0 to lstBox.listcount -1
    vOrder = lstBox.itemdata(i)    'get next item in listbox
    lstBox = vOrder	         'set the listbox to this item


   sFile = "c:\temp\Purchase_Order" & vOrder & "_" & vStamp & ".pdf"
   DoCmd.OutputTo acOutputReport, "rPurchase_Order", acFormatPDF, sFile
next

end sub

 

[arnelgp]

this snippet has explanation on
its comment line.

Private Sub test()
Dim rs As DAO.Recordset
Dim strFile As String
'====== change the table here ================
' replace "yourOrderTableHere with correct order Table Name
Set rsOrders = CurrentDb.OpenRecordset("yourOrderTableHere")
With rsOrders
    If Not (.BOF And .EOF) Then .MoveFirst
    While Not .EOF
        ' Open the report
        ' filter the report based on OrderID
        ' open the report but hidden
        
        ' 1. Use this one instead if OrderID is Numeric
        DoCmd.OpenReport "rptOrder", acViewPreview, , "OrderID=" & !OrderID, acHidden
        
        ' 2. this one if OrderID is string
        'DoCmd.OpenReport "yourReport", acViewPreview, , "OrderID='" & !OrderID & "'", acHidden
        
        ' this will save the PDF in MyDocuments
    strFile = Environ("userprofile") & "\Documents\" & _
                "Order" & Format(Date, "mmmyyyy") & "-" & !OrderID & ".pdf"

    'If PDF exists, delete and create new
    If Dir(strFile) <> "" Then Kill strFile

    ' generate the PDF
        DoCmd.OutputTo acOutputReport, "rptOrder", acFormatPDF, strFile, True

        ' close the report
        ' since we cannot Refresh the report
        ' with new Filter
        DoCmd.Close acReport, "rptOrder"
        .MoveNext
    Wend
    .Close
End With
Set rsOrders = Nothing