I just inherited an old database report, I have a report that breaks down monthly sales for each of our vendors and starts a new page for each vendor. Currently it creates a report roughly 700-1000+ pages each month. In an effort to save paper and postage we are wanting to export a seperate file for each vendor's report so that we can email or fax them depending on their preference.
So far I've updated the queries/report so that before running the report it pulls the data into a local table from all the linked tables to speed up the export process for each individual report significantly. I also generate a table that lists the unique values for each vendor that is in the month's report run.
For each vendor I now want to export a copy of the report as a .rtf file named for the vendor and the month/year it is for.
From what I've figured out so far it seems that VBA is the best solution to run the loop that would be required but I am stumped as to the best solution for getting the vendor name and id number from the reference table to run each copy of the report on and how best to have the report display the limited results. I'm guessing creating a temp table created by a maketable qry limited to the vendor number would be best with the report pointing to the new table as it's source and then just running an output to.
Any guidance of how to get the data a row at a time from the reference table or a more efficient method would be greatly appreciated.
-edit-
Ideally if Access allowed the OutoutTo command to treat a report as subreports and would output each subreport to a file based on it's subreport criteria my problem would be solved.
So far I've updated the queries/report so that before running the report it pulls the data into a local table from all the linked tables to speed up the export process for each individual report significantly. I also generate a table that lists the unique values for each vendor that is in the month's report run.
For each vendor I now want to export a copy of the report as a .rtf file named for the vendor and the month/year it is for.
From what I've figured out so far it seems that VBA is the best solution to run the loop that would be required but I am stumped as to the best solution for getting the vendor name and id number from the reference table to run each copy of the report on and how best to have the report display the limited results. I'm guessing creating a temp table created by a maketable qry limited to the vendor number would be best with the report pointing to the new table as it's source and then just running an output to.
Any guidance of how to get the data a row at a time from the reference table or a more efficient method would be greatly appreciated.
-edit-
Ideally if Access allowed the OutoutTo command to treat a report as subreports and would output each subreport to a file based on it's subreport criteria my problem would be solved.
Last edited:
