VBA and reports (1 Viewer)

[Peter Bellamy]

I can't get my head around this, can someone help.

I have a selection of Price lists for different customers each has their version of a price list for several different products. The products don't change, just the customer and the price they get.

I have created individual queries and reports for each of the variants. They vary just by the two variables, customer and product.
A long list but it has been done over time.

Now any change requires editing nearly every query and nearly every report, too time consuming !

What I want to do is code it in VBA using SQL statements to provide the query for the reports, altering the variables in the query, in it in a loop, to get the different product price lists.
That way it should be possible to boil it down to a short bit of code but I am stuck on how to pass the output of the queries to the reports?

Cheers

pnb

 

[Mr. B]

Peter,

The simple answer (may not be the one you need) is that the results of queries are used in reports by simple using the query as the record source for the report. In certain situations, it can be much more complicated than this simple answer, but the genereal principal is the same. This is true even if you are using sub reports and linking them to the main report.

I hope this will give you the nudge you need in the right direction.

 

[Peter Bellamy]

Thanks, but that I understand.
What I want to do is have a vanilla report and feed it all the details from VBA code.

So how do I pass the results of the query to the report?

Something like this?

SQLQry = Select etc................

DoCmd.OpenReport "RptName", acPreview, "SQLQry"

Cheers

pnb

 

[Mr. B]

Peter,

Sorry for the long delay in getting back to you.

You can develop the sql statement through code much as you already describe.

You would then just use the On Open event of the report to assign the sql statement as the Record Source for the report.
Like this: Me.RecordSource = SQLQry

HTH