SQL to generate Reports

[Peter Bellamy]

I am trying to tidy up some report code and add some new features, can anyone point me in the right direction.

What I currently have is: 8 Reports based on 8 queries all listed in a macro.
The user clicks the button the macor runs and 8 reports are previewed.

I want to 'convert' this to VBA using SQL so I can introduce two additional pieces of variable information not in the tables, a discount % and a Customer name for the report heading, both will be used in all 8 reports.

The reason for thinking SQL is the way to go is because the contents of the reports are the same and they are based on similar queries, only the product name and the consequential selection varies.

They are price lists of parts for a product, and obtain the data from two tables, tblParts and tblCarriage, tblParts having numerous fields listing "where used" which is used to select the parts for that product price list.

I have so far a working dialog box that provides the user input of Discount and Customer.
I have also now a workiing SQL statement with a variable in it that can be the "product name".

What I want guidance on is how to use the query to generate the reports and, a slick way to loop around the SQL substituting the "product name"

Sorry this is such a wordy post but I was trying make it as clear as possible !!!

Cheers
Peter

 

[Peter Bellamy]

So no suggestions then ???
Peter

 

[Peter Bellamy]

I have found a start to a solution but come up with an odd error
It is possible to set the RecordSource property of the report in VBA so the SQL string can be 'put' there.
If I run my code with the report not open I get an error saying the report is not open or reference is spelt incorrectly
If I open the report I get an error saying it is not possible to set the RecordSourcce property when the report is open!
The help page on RecordSource says
"Note Changing the record source of an open form or report causes an automatic requery of the underlying data. If a form's Recordset property is set at runtime, the form's RecordSource property is updated."

My code (simplified) is:

StrSQL = " SELECT blah blah blah..."
With Reports![Customer Price List]
.RecordSource = StrSQL
End With

What is going on?