Pass parameter from daoRecordset to report (1 Viewer)

Les Isaacs · Nov 20, 2017

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 · Nov 20, 2017

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

Code:

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 · Nov 20, 2017

this snippet has explanation on
its comment line.

Code:

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

Uncle Gizmo · Nov 20, 2017

I blogged about this very issue with Reports recently. You can see my blog on my website here:- Multiple Reports - Slide Run Through

Down the bottom of the blog there are some useful links, one in particular from Gina Whipp, where she explains how to send a reports via email.

Pass parameter from daoRecordset to report (1 Viewer)

Les Isaacs

Registered User.

Ranman256

Well-known member

arnelgp

..forever waiting... waiting for jellybean!

Uncle Gizmo

Nifty Access Guy

Similar threads

Users who are viewing this thread