Macro to print PDF / post to folder with parameters (1 Viewer)

emamekyd

Registered User.
Local time
Today, 02:05
Joined
Sep 21, 2016
Messages
13
Hi there,

I have a report created that displays data based on which company name I enter into the parameter. I have to enter in each company individually and print each report to PDF to a separate location. Is there a way of creating a macro that can edit the query and then print out that report to PDF to a specified location for each company? It's only through specific companies, and not all certain types so a loop might not work well. Looking for something simple!

Thanks!
 

sxschech

Registered User.
Local time
Today, 02:05
Joined
Mar 2, 2010
Messages
791
If it is a specific list of companies, you could create a table with the id or unique fieldname to join with you main table/query, company name and folder name (unless they are same) and then use that table to create the loop. If that concept works, with a little more information could provide sample code.

Code:
ID     Company           FileName
573  CompanyOne   c:\Reports\ReportName_CompanyOne.pdf
577  CompanyTwo   c:\Reports\ReportName_CompanyTwo.pdf
 

emamekyd

Registered User.
Local time
Today, 02:05
Joined
Sep 21, 2016
Messages
13
Yes this concept would work great! Sorry, i'm not a big coder but what would be the code to loop through a table like that? That would be the only information changing is the company name and the filepath - exactly the table you have there. I would just like to be able to export as a PDF to each of the filepaths all at once, rather than entering the parameter for 20+ companies and having to float through their individual folders.
 

MarkK

bit cruncher
Local time
Today, 02:05
Joined
Mar 17, 2004
Messages
8,178
You can use a TempVar in a report's recordsource so that every time you run the report it will look to the value in the TempVar. Consider SQL like...
Code:
SELECT * FROM tCompany WHERE CompanyID = TempVars!CompanyIDToPrint
So then, if you open a recordset of companies you want to print, say...
Code:
dim rst as dao.recordset
set rst = currentdb.openrecordset( _
   "SELECT CompanyID, CompanyName FROM tCompany WHERE IsActive")
with rst
   do while not .eof
      TempVars!CompanyIDToPrint = !CompanyID
      DoCmd.OutputTo acOutputReport, "urReport", acFormatPDF, "C:\Reports\urReport_" & !CompanyName & ".pdf", True
      .movenext
   loop
   .close
end with
See how that code...
  1. opens a recordset
  2. sets the TempVar
  3. runs a report which consumes the TempVar and saves it to a custom named file
  4. loops back to 2.
?
hth
Mark
 

emamekyd

Registered User.
Local time
Today, 02:05
Joined
Sep 21, 2016
Messages
13
Okay that logic makes sense, thank you so much for your help! Sorry I am totally new on this kind of stuff and I'm a little confused on where I input that SQL statement . Should I make a new query that joins the current query I use as the record source that joins tCompany for the report to reference?
 

MarkK

bit cruncher
Local time
Today, 02:05
Joined
Mar 17, 2004
Messages
8,178
Don't use the SQL I provided at all. It is just an example. The SQL I wrote needs to be completely replaced with yours, so use your fields and your tables. The SQL I posted does, however, show an example of the syntax you can use to write a TempVar into your WHERE clause, and thereby filter the report's RecordSource at runtime without modifying the report.
Mark
 

Users who are viewing this thread

Top Bottom