Print Multiple reports to PDF in new directory

[DeanJW2006]

Every month, I create 15 individual reports with each report filtered by two fields, let's say Dept and Exec. You change Dept and Exec via a combo box drop down based on a table, and this information is passed to the query behind the report. Now, I repeat this process for every report. I use an unbound form that gathers the "Title", which is the Dept Name +"Special Report"; report Date using "as of" [DATE], and an updated through [Second Date].
Each monthly set of reports is sent to a new directory and folder (i.e. M:\MOR Reports\2005\January. This changes each month. Somehow, I'd like to automate this where each report prints with its name to a newly created folder in pdf format.

I have been reading prior posts and am coming up with some ideas like adding a "selected" field to the Dept/Exec table and then step through those selected records to determine which report you are running. Any further ideas would be very helpful. It takes a long time to print these to pdf in a new directory every month.

 

[ted.martin]

To achieve the level of automation you describe, will require VBA coding. Are you able to do this? For example, the code

MkDir "M:\MOR Reports\2005\January\"

will create the monthly folder you require and if you replace the word January with a string expression linked to the current month, then that folder will be created. (You will need a check for FolderExists to make sure you don't duplicate though)

The rest is nothing more than a Loop through the combo box fields but again you will need VBA code to do this.

As always with such problems, try and break each element of the process down into its individual parts and try to identify the 'Loop'. Once achieved, then you are on your way; as long as you can code .....

 

[DeanJW2006]

Yes, I am comfortable with VB code. I just couldn't figure out how to do what I'm describing efficiently. Your help is greatly appreciated.

 

[ted.martin]

Ok good so design/write down a workflow for one report using the appropriate variables and then loop through the variable changes you need and within each query to output the report using a derivation of the code below.

Your Report will need to be based on the Query. In this example you would need a different query for each report but if you get it working for one report, then there are ways to use DAO Recordset and SQL to make the query dynamic via QueryDefs (but perhaps that is for another time)

Dim I as integer

For = 1  to 15

DoCmd.OutputTo acOutputReport, "R-Report" & i, acFormatPDF, strFilePath & "Report" & i & ".pdf", False

next i

Firstly once the folder has been initially created, then save the path somewhere and refer to that in the code below, maybe via a variable or DLOOKUP.

to output the report to the directory on your M drive.

Once you have the For/Next correct, the 15 reports should run seamlessly.

 

[DeanJW2006]

One of the objectives of this program is to use one query and one report form. As I was reading older posts on this notion of running a series of reports, I noticed one post where a field was added to a table to designate selected ("Yes or No"). In this fashion a variable list of reports may be run by checking or unchecking, in my case, the business line and busines line executive. The idea then would be to loop through the list of checked business lines. The business line and executive would be passed to the query that runs that specific report. The title of that report would be "Some words" +[Business Line] "as of"[Report Date]. This would be written to M:\MOR Reports\"[Month]"\ as a pdf.

Then the loop would execute to the next business line checked. In this manner, I would, like I do now, use one report and one query and pass the information about the business line, exec, report date, etc. through a form to the query.

In the end, a new directory of between 10 and 15 reports would be written with each report having a unique name based on the business line.

 

[ted.martin]

1 query and 1 report was what I was referring to when I mentioned using DAO.Recordset. and QueryDef. You will need to create each Query using SQL and recordsource the one report to this query.

Using the Recordset, put the Query Def within the DAO loop so that it creates the appropriate query each time.

Research QueryDef here and try it with one criteria option. The code sequence being this

1. Get Criteria
2. Create Query
3. Output report as .pdf
4. Loop to next Criteria

 

[DeanJW2006]

So, here's where I'm at. I have a form called frmNewMOR that you can enter the report title [txtTitle] and report date [txtDate]. These fields are passed to the end Report named "RptNewMOR".

The form also has a subform named "qrysubRespExec" which is a form that lists Business Lines and executives. I added a field for selected. Here you check off the reports you want printed.

RptNewMOR gets fields from the form: Business Line = Forms!frmNewMOR!QrysubRespExec![Business Unit Long] and also ......[Responsible Executive]. Each report is made up of a combination of those fields that is why I made the selected box because the same reports are not always run.

I created a button to execute, and here is the code that I came up with. Unfortunately, it is not working and I need some help. Below is the Code:

Private Sub cmdSaveAsPDF_Click()


Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strPathName As String
Dim blRet As Boolean
Dim rs As DAO.Recordset
Dim stDocName As String

Dim strSavedSQL As String

stDocName = "RptNewMOR"

strSQL = "Select [Business Unit Long], [Responsible Executive]FROM tblRespExec WHERE(((tblRespExec.SelectedPrint)=True));"
Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.RecordCount < 1 Then
MsgBox "Nothing found to process", vbCritical, "Error"
Exit Sub
End If

'store the current SQL
Set qdf = CurrentDb.QueryDefs("qryRespExec")
strSavedSQL = qdf.SQL
qdf.Close

Set qdf = Nothing


Do

Set qdf = CurrentDb.QueryDefs("QryRespExec")
strSQL = Left(strSavedSQL, InStr(strSavedSQL, ";") - 1) & " and([Business Unit Long]=" & rs![Business Unit Long] & ");"
qdf.SQL = strSQL
qdf.Close
Set qdf = Nothing

' put in folder

strPathName = "C:\MOR Reports\" & rs![Business Unit Long] & ".pdf"
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, strPathName
rs.MoveNext

Loop Until rs.EOF
rs.Close

Set rs = Nothing
Set qdf = CurrentDb.QueryDefs("qryRespExec")

qdf.Close
Set qdf = Nothing
qdf.SQL = strSavedSQL
qdf.Close
Set qdf = Nothing


End Sub

I've been debugging it and can not figure out how to execute the report based on the Business units selected. I have not even attempted, yet, to create the directories and report names for each.

 

[ted.martin]

Base the report on the Query used in the Query Def. If you use one generic name for the query and then put this in the Report RecordSource, as long as each report version contains the same fields, when the report runs, it will pick up the latest QueryDef.

I have not checked your syntax but the principle looks fine.

 

[ted.martin]

One small point that will tidy up your coding and make it easier to follow is to use the clear nomenclature for Queries, Forms and Reports.

By this I mean instead of qryRespExec use Q-RespExec
similarly

frmNewMOR becomes F-NewMOR
RptNewMOR becomes R-NewMOR

A bit pedantic and OCD perhaps but make these object really stand out.

Tables I just leave as the name, ie no 'T-'

Macros are mac- and modules are mod-

 

[spikepl]

Using anything but letters or underscores in object names condemns you to eternal PITA, because you then have to use square brackets when referring to these objects. Portability/expandabilty goes out the window, too. Don't do it.

@ted.martin

with 600 posts you should know better than to suggest a silly thing like that

 

[DeanJW2006]

Thank you for your responses. I still can not get it to generate a report. It asks for [Business Unit Long] and then it pops up a query asking for one of the Business units that it is apparently finding. Once you click away the two parameter boxes, the report says no records found.

I changed the qry in the qrydef to the qry that runs the report, like I think you suggested, but this did not help. This is a fairly complex query that gets several fields from the form (i.e. Business Unit Long, Responsible Executive, and Report Date). I'm not sure if that is what you meant or not. If I have to create the query behind the report in the qrydef process, that is something different than I am currently doing. I could copy the SQL to the qrydef if needed.

To summarize, when this report is requested, a frm pops up with places to enter the title, date, and a sub form/qry to check off the Business units to run for. The idea is that a new report will be generated for each business unit checked, but this part is not working.

 

[ted.martin]

I am not sure what else I can say. The QueryDef will create the query that the Report needs as the RecordSource.

Until you get the query def correct, then of course it will not work.

This is why I said at the very start of this, create a query manually that works and then by taking the SQL from this query, put that into the QueryDef statement.

For the next report, amend the SQL accordingly.

Get one report running correctly and the rest should follow. (assuming as I said before - that the report fields are all the same for each report version). If they are not then you need different report and query RecordSource for each report design (field) version.