none
VBA to Set Report RecordSource Property

    Question

  • Hi 

    I have a table with say 200 invoices and related data.  I also have a report template that captures this data.  Currently I print all data in one PDF file with 1 sheet per invoice.  What I need to do is print one PDF FILE per invoice.

    As far as I can tell this requires me to dynamically set the RecordSource at run time (i.e. "Select blah from table where invoicenum = " & strInvoice).

    I would like to do this only using OutputTo and not OpenReport.  I.e. print out the report without having to open it.

    Any ideas?

    Thank you!


    Bonediggler

    Monday, June 10, 2013 8:27 PM

Answers

  • Hi 

    I have a table with say 200 invoices and related data.  I also have a report template that captures this data.  Currently I print all data in one PDF file with 1 sheet per invoice.  What I need to do is print one PDF FILE per invoice.

    As far as I can tell this requires me to dynamically set the RecordSource at run time (i.e. "Select blah from table where invoicenum = " & strInvoice).

    I would like to do this only using OutputTo and not OpenReport.  I.e. print out the report without having to open it.

    You don't have to dynamically set the report's RecordSource at run time. You can set the report's RecordSource at design time to a query with a selection criterion that refers to a form control for the InvoiceNum to be selected.  Then your process to print a specific invoice would be to set the value of that control on the form, then export the report with DoCmd.OutputTo.  To output all the invoices into separate files, you could loop through all the invoice number, placing each in turn into the control and exporting the report.

    That said, you could also leave the report's recordsource as it is and, in your loop, use DoCmd.OpenReport with a where-condition argument and WindowMode:= acHidden to open the report hidden, filtered by the invoice number, then use OutputTo to export that filtered report to a PDF, and then close that instance of the report.  That would also work.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Bonediggler Tuesday, June 11, 2013 5:57 PM
    Monday, June 10, 2013 8:36 PM

All replies

  • Hi 

    I have a table with say 200 invoices and related data.  I also have a report template that captures this data.  Currently I print all data in one PDF file with 1 sheet per invoice.  What I need to do is print one PDF FILE per invoice.

    As far as I can tell this requires me to dynamically set the RecordSource at run time (i.e. "Select blah from table where invoicenum = " & strInvoice).

    I would like to do this only using OutputTo and not OpenReport.  I.e. print out the report without having to open it.

    You don't have to dynamically set the report's RecordSource at run time. You can set the report's RecordSource at design time to a query with a selection criterion that refers to a form control for the InvoiceNum to be selected.  Then your process to print a specific invoice would be to set the value of that control on the form, then export the report with DoCmd.OutputTo.  To output all the invoices into separate files, you could loop through all the invoice number, placing each in turn into the control and exporting the report.

    That said, you could also leave the report's recordsource as it is and, in your loop, use DoCmd.OpenReport with a where-condition argument and WindowMode:= acHidden to open the report hidden, filtered by the invoice number, then use OutputTo to export that filtered report to a PDF, and then close that instance of the report.  That would also work.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by Bonediggler Tuesday, June 11, 2013 5:57 PM
    Monday, June 10, 2013 8:36 PM
  • Thanks Dick.

    I am opting for option 2.  However, I am getting a dialogue box that prompts me to "Enter Parameter Value", although I am already setting this in the where condition...

    ???


    Bonediggler

    Monday, June 10, 2013 9:07 PM
  • Thanks Dick.

    I am opting for option 2.  However, I am getting a dialogue box that prompts me to "Enter Parameter Value", although I am already setting this in the where condition...


    Does the report or its recordsource have a reference to a control on some form that isn't open?  If you open the report directly in print preview, with no other forms open, does it prompt you for a parameter? 

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Monday, June 10, 2013 9:14 PM
  • You'll find an example as InvoicePDF.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    The report is restricted to the current invoice by means of a parameter in its query.  This enables the OutputTo method to be used.  In the demo only one invoice is generated, that currently selected in the bound form, but it would not be difficult to do as Dirk suggests and loop through a recordset of invoice numbers, inserting the invoice number into a hidden control in an unbound form, and referencing that control as a parameter in the report's query.  The OutputTo method would then be called at each iteration of the loop to generate an invoice as a PDF file in each case.

    Ken Sheridan, Stafford, England

    Monday, June 10, 2013 11:20 PM
  • Ken/Dirk

    Could you elaborate on how I would reference this control via a parameter in the report's query?

    Thanks


    Bonediggler

    Tuesday, June 11, 2013 2:17 PM
  • Take a look at the qryPDFInvoice query in my InvoicePDF demo file to which I referred you.  You'll see how it has the following parameter in the 'criteria' row of the InvoiceNumber column in design view:

    [Forms]![frmInvoice]![InvoiceNumber]

    When the report based on this query is opened from the invoice form this parameter restricts it to the current invoice.  This applies to whether its opened in print preview, output to a PDF file or as an attachment to an email.  What I'm not clear about, however, is whether to simply want to create a single PDF file of one invoice, as in my demo, or whether you want to create multiple files at one click of a button, each of a different invoice, in which case you'd need to explain the basis on which the multiple invoices would be selected.

    Ken Sheridan, Stafford, England

    Tuesday, June 11, 2013 5:35 PM
  • Dirk

    Got option 2 to work.  Thanks for the help.


    Bonediggler

    Tuesday, June 11, 2013 5:57 PM