Carrying data from form to report

[Bill7927]

I'm developing A/R statements from a table that contains 1 record per customer that I'm reading sequentially through a form. After reading each record I'm then calling the report using
DoCmd.OpenReport "SCUS", acViewPreview, , , , vPassedVariable, but I cannot see the value of vPassedVariable in the report program. Within the report program I need to then join the vPassedVariable to a key field in another detail table via a SQL statement in order to get all of the data on the report for a single customer. I'm then exiting the report and saving the print preview as a PDF. The form will then read the next record and call the report again, save as pdf for about 1,500 customers.

What am I missing in not being able to bring the vPassedVariable from the form to the report?

[jforbes]

Here is some code I use to create Quote PDFs, which is very similar to what you are doing. I hid some of the unrelated code. I think what you will be most interested in is the Where Condition of the DoCmd.OpenReport:

Public Function generateQuotePDF(ByRef sQuoteNumber As String) As String
 
    Dim sFileName As String  
 
    sFileName = sQuoteNumber & "_" & getStringDateTime()& ".PDF"   
 
    Application.Echo False
    DoCmd.OpenReport "rptQuote", acViewPreview, , "QuoteNumber='" & sQuoteNumber & "'"
    DoCmd.OutputTo acOutputReport, "rptQuote", acFormatPDF, gQuoteDirectory & sFileName, False
    DoCmd.Close acReport, sReportName
    Application.Echo True
...

This code will create the PDF with minimal screen updating and just a print notification showing on the screen.
If you set the AutoStart parameter of DoCmd.OutputTo to "True" it will display the PDF to the user after it has been created:

    DoCmd.OutputTo acOutputReport, "rptQuote", acFormatPDF, gQuoteDirectory & sFileName, True

[Bill7927]

@jforbes
Thank you for your reply. I believe I have the form coded as you instructed, but I'm not sure how to reference the field in the report program in order to use in in the SQL statement.

That is from your example, how do you reference or use the sQuoteNumber in the report program?

[jforbes]

I should back up a bit and explain that there are two approaches to limiting a Reports output.

1. Defining the RecordSource when Opening the Report - This is done by passing a Key Value in the OpenArg Parameter (Or Global variable or TempVar). The last parameter in DoCmd.OpenReport, OpenArgs, gets stuffed into the OpenArgs Propery on the Report, which can then be used to define a Record Source. This is an example that is similar, but for filtering a Form: https://msdn.microsoft.com/en-us/lib.../ff836583.aspx
   
   I believe this is the way you originally started developing the Report. You would then write some code to use OpenArgs and build a SQL Statement for the RecordSource. This approach will work, but I wouldn't recommend it as the Next approach is easier to Develop and easier to use.
   
2. Passing a Where Clause with DoCmd.OpenReport. This link has three different examples: https://msdn.microsoft.com/EN-US/lib.../ff820845.aspx
   This is also the example I provided. The idea behind this it to Write your Report with no Filtering at all. Get it to display the way you want. I often, put in a Group on the Key Value. In the example above, the Report is Grouped by QuoteNumber, which aligns the Report correctly on the Page. Then when opening the Report, limit the report to only the single Key value that is of interest.
   
   This is what is being done in the example I provide by the variable sQuoteNumber. The QuoteNumber is passed in sQuoteNumber and then the Where Clause of "QuoteNumber='" & sQuoteNumber & "'" is passed to the Report, which then limits the Report to the One Desired Quote.

[Bill7927]

Thanks again, that fixed it! I took your advice and simplified the report by removing the SQL code and grouped by the customer number from the form. Works great!

Your help is greatly appreciated!