Creating Multiple Reports from one click (1 Viewer)

[manix]

Hi All,

I currently have a complex array of tables and queries that create a report - A supplier performance scorecard. This score card is a report of a supplier performance over a given space of time.

Currently, the query used to create the report looks at x3 combo boxes that the user fills in and runs the query to produce the report. These combo boxes are:

Start Date
End Date
Supplier Name

Now this allows the user to create a report for each supplier one at a time. My issue is that although this works, it's time consuming, especailly if you have to report many suppliers for a given period.

Now the database knows whether a supplier has delivered in a given period, so what I would like to do, is enter Start Date and End Date and click a buttone to produce all the reports at once, based on all those suppliers who have delivered in the period defined by the Start and End Dates.

This is a bit beyond my knowledge, but can anyone provide a possible solution? Basically I would like to have it create PDFs for each supplier and plonk them somewhere on a newtworked drive so we can then mail them out.

The database was built in Access 2003 but I now run Access 2010. This operation runs purely off a series of queries and does not currently make use of any macros or advanced VBA.

Any help would be most appreciated.

 

[Mr. B]

Automating a process like the one describe is quite possible but it will require the use of some VBA coding to make it work.

The general concept would be:
Create a record set of a list of the suppliers using VBA code
Iterate through each record in the record set doing the following for each supplier record:
Create an sql statement providing the variable data for the Start Date, End Date and the Supplier
Assign the sql statement as the record source of your existing report
Use the " DoCmd.OutputTo ..." method to create a PDF document using the report in the appropriate location

Hope this helps.

 

[manix]

Automating a process like the one describe is quite possible but it will require the use of some VBA coding to make it work.

The general concept would be:
Create a record set of a list of the suppliers using VBA code
Iterate through each record in the record set doing the following for each supplier record:
Create an sql statement providing the variable data for the Start Date, End Date and the Supplier
Assign the sql statement as the record source of your existing report
Use the " DoCmd.OutputTo ..." method to create a PDF document using the report in the appropriate location

Hope this helps.

Thanks Mr B,

I kind of get your meaning and I will have a play, but that seems to be a bit beyond my current capability! I will see if, with a bit of time I can figure that out.

Thanks again.

 

[Mr. B]

Remember, search engines are your best friend.

There is a lot of information on line about doing something like this, but it is very accurate to say that learning VBA does have quite a learning curve. Unfortunately, there is just no "out of the box" solution that will allow you to do what you want to do.

The best approach is to take things one step at a time. That is the reason that I tried to layout an over all view. Give it a try and when you need help, search on line, search here in the forums and then if necessary, ask your questions here and I am sure someone will be glad to try to help.