How to produce a pdf file for each group in a report

Hi,

pls refer to

https://www.experts-exchange.com/questions/28253476/How-do-I-get-each-page-of-a-report-to-be-output-as-a-separate-PDF-file.html

Regards

You'd have to open a Recordset that defines your "group", and then loop through that recordset and output the report for each of those "groups". For example:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT GroupName FROM tblGroups")

Do Until rst.EOF
  DoCmd.OpenReport "Your Report", , , "GroupName='" & rst("GroupName") & "'", acHidden
  DoCmd.OutputTo acOutputReport, "Your Report", acFormatPDF, "C:\Somefolder\" & rst("GroupName") & ".pdf"
  DoCmd.Close acReport, "Your Report"
  rst.MoveNext
Loop

Select allOpen in new window

Obviously you'd have to change the various items to match those in your project, and you'd have to change the OpenRecordset call to match the definition of your tables and fields.

Hi,

or for example

DoCmd.OpenReport "YourQuery", acViewPreview , , "ID=" & YourFilter, , acHidden
DoCmd.OutputTo acOutputReport, "YourQuery", acFormatPDF, strFile & YourFilter & ".pdf", False
DoCmd.Close acReport, "YourQuery" 

Select allOpen in new window

Regards

1. convert the Record Source of your report to a query or create a query,
    save it as "Q_Report"
2. using vba to create the pdf files

    dim qd as dao.querydef, rs as dao.recordset
    dim oSql as string, gSql as string, sWhere as string
    set rs=currentdb.openrecordset("select distinct [group] from tablename")
    set qd=currentdb.querydefs("Q_Report")
    oSql=qd.sql
 do until rs.eof
       swhere="[group]='" & rs!group & "'"
       nSql=Replace(oSql,";","") & " where " & swhere
       qd.sql=nsql
 
      'create the pdf report

      docmd.outputto acOutputReport, "reportX", acformatpdf, "c:\foldername\" & rs!group & ".pdf
       

   rs.movenext
loop


if your query have totals and group by, more parsing is required.

Hello folks
Looks like there is a solution that I can use.  I am not in the office now until next week, so will look at it then.

Best regards

Richard

Hello guys
I am looking at this this morning and after looking can see that all three options are broadly speaking the same.  I have just chosen to try the code from Scott.

This is the code that I have ended up with:
***********************************************************************
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT OrgID FROM tblOrg")

Do Until rst.EOF
  DoCmd.OpenReport "rptOrgsWithActiveOppsDetails", , , "OrgID='" & rst("OrgID") & "'", acHidden
  DoCmd.OutputTo acOutputReport, "rptOrgsWithActiveOppsDetails", acFormatPDF, "C:\VBASEpdf\" & rst("OrgID") & ".pdf"
  DoCmd.Close acReport, "rptOrgsWithActiveOppsDetails"
  rst.MoveNext
Loop
**************************************************************************
When I run this I get a type mismatch.

Here is the code for the query (if it helps):
**************************************************************************
SELECT tblOrg.OrgID, tblOrg.Name, tblOrg.Title, tblOrg.FirstName, tblOrg.LastName, tblOrg.ContactAddress1, tblOrg.ContactAddress2, tblOrg.ContactTown, tblOrg.ContactPostcode, tblOrg.ContactTel1, tblOrg.ContactEmail, tblOpp.OID, tblOpp.Title, tblOpp.Description, tblOpp.Active, [tblOrg.Title] & " " & [tblOrg.FirstName] & " " & [tblOrg.LastName] & " - " & [tblOrg.ContactAddress1] & ", " & [tblOrg.ContactAddress2] & ", " & [tblOrg.Town] & ", " & [tblOrg.Postcode] AS OrgFullnameAddress
FROM tblOrg INNER JOIN tblOpp ON tblOrg.OrgID = tblOpp.OrgID
WHERE (((tblOrg.OrgID)<9) AND ((tblOpp.Active)=True));
**************************************************************************
(the Where tblOrg.OrgID<9 is there just to limit the records for testing)

Any ideas why it’s throwing up this error?  I am not very experienced in this, so not completely sure what I should do.  I’m sure it will be quite simple.

It should be creating just three pdf's - for OrgID's 4,5 and 8

Best regards
Richard

What version of Access are you using? This requires 2007 or higher.

Yes - 2007

Regards
Richard

Is OrgID a Text value or Numeric?

If it's Text, you'll use this syntax:

DoCmd.OpenReport "rptOrgsWithActiveOppsDetails", , , "OrgID='" & rst("OrgID") & "'", acHidden

If it's Numeric you use this:

DoCmd.OpenReport "rptOrgsWithActiveOppsDetails", , , "OrgID=" & rst("OrgID"), acHidden

Hi Scott
I have made some slight progress.  This is what the code looks like now:
***********************************************************************
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT Name FROM tblOrg")

Do Until rst.EOF
  DoCmd.OpenReport "rptOrgsWithActiveOppsDetails", , , "Name='" & rst("Name") & "'", acHidden
  DoCmd.OutputTo acOutputReport, "rptOrgsWithActiveOppsDetails", acFormatPDF, "C:\VBASEpdf\" & rst("Name") & ".pdf"
  DoCmd.Close acReport, "rptOrgsWithActiveOppsDetails"
  rst.MoveNext
Loop
**********************************************************************

I changed the OrgID to Name (which is the associated description for each ID in the table).  That is what I actually wanted – the file to be given the name of the organisation.

What happens now is the following:
It starts actually printing to documents (don’t want that!)
It creates pdf’s in the correct folder.
It stops after a short time with the following:

(I am thinking that the error could be related to the apostrophe in Alzheimer’s.)

Anyway, once it stops a look at the folder suggests it is creating files disregarding the Where statement in the query (I have that in there to limit the number to 3 for testing)
Also – looking at the files created, although they have different names, they all have the same content – relating to the first record in the collection.

Any more suggestions?

Regards

Richard

Hi Scott

Our comments crossed.  Yes - using OrgID, the original syntax would have been wrong, because it is numeric.
But the corresponding name is Text, so that looks as it should be.

So still having the issue above.

Regards

Richard

If your data can include single quotes, you need to "escape" them:

  DoCmd.OpenReport "rptOrgsWithActiveOppsDetails", , , "Name='" & Replace(rst("Name"), "'", "''") & "'", acHidden

Essentially, this adds a second single quote.

You can also do this:

  DoCmd.OpenReport "rptOrgsWithActiveOppsDetails", , , "Name=" & Chr(34) & rst("Name") & Chr(34), acHidden

Chr(34) is the double quote.

It starts actually printing to documents (don’t want that!)

Sorry, you need to add the Preview option:

DoCmd.OpenReport "rptOrgsWithActiveOppsDetails", acViewPreview , , "Name='" & rst("Name") & "'", acHidden

Anyway, once it stops a look at the folder suggests it is creating files disregarding the Where statement in the query (I have that in there to limit the number to 3 for testing)

I don't see a WHERE statement, unless you're referring to the WHERE argument in the OpenReport. The below test may resolve this, however.

Also – looking at the files created, although they have different names, they all have the same content – relating to the first record in the collection.

Are you sure the "Name=xxxx" is sufficient to show different data? In other words, if you were to open the report and view it, do you see the data you expect? Try this:

Do Until rst.EOF
  DoCmd.OpenReport "rptOrgsWithActiveOppsDetails", acViewPreview, , "Name='" & rst("Name") & "'"
  rst.MoveNext
Loop

This should show a report for each "Name" value, with the report filtered for that Name.

Hi Scott
I tried your final piece of test code and it gave me a blank report.  But I'm still not clear what is wrong.
Hopefully giving you a bit more details will help:

This is my query:
The Where OrgID < 9 just limits the collection for testing.  However there is another Where condition that is required in the final solution (see SQL below)

This is the SQL of the query:
*********************************************************************
SELECT tblOrg.OrgID, tblOrg.Name, tblOrg.Title, tblOrg.FirstName, tblOrg.LastName, tblOrg.ContactAddress1, tblOrg.ContactAddress2, tblOrg.ContactTown, tblOrg.ContactPostcode, tblOrg.ContactTel1, tblOrg.ContactEmail, tblOpp.OID, tblOpp.Title, tblOpp.Description, tblOpp.Active, [tblOrg.Title] & " " & [tblOrg.FirstName] & " " & [tblOrg.LastName] & " - " & [tblOrg.ContactAddress1] & ", " & [tblOrg.ContactAddress2] & ", " & [tblOrg.Town] & ", " & [tblOrg.Postcode] AS OrgFullnameAddress
FROM tblOrg INNER JOIN tblOpp ON tblOrg.OrgID = tblOpp.OrgID
WHERE (((tblOrg.OrgID)<9) AND ((tblOpp.Active)=True));
********************************************************************

This is the result of running the query:

This is the report in edit view:

This gives the following result (one page for each Org)
(Personal info blanked out)

So what I want is for a pdf of each new Org in the report.
The report is actually grouped by OrgID, but I used Name in the code because I want the pdf to be named by the Org Name (rather than a number)

I hope that is a bit clearer.  Sorry for being a trial!

Regards

Richard

In your image titled "report design", it looks like you've set a Filter on the report. Clear that and try it again.

Hi Scott
OK - I'll look at that tomorrow.
There is a group set so that the report paginates by organisation.  However, I guess that's not required if selecting organisation separately in the code - right?

You'll hear from me tomorrow.  Have a good evening.

Regards

Richard

Hi Scott
I removed the grouping filter (which also removed the header of course).
It made no difference I'm afraid.  
The code:
Do Until rst.EOF
  DoCmd.OpenReport "rptOrgsWithActiveOppsDetails", acViewPreview, , "Name='" & rst("Name") & "'"
  rst.MoveNext
Loop

still just results in a blank report.  That is - a report but with no data.

Removing the grouping would not work anyway I think because I need to have the Organisation details at the top of each page.

Anymore ideas?

Regards

Richard

I removed the grouping filter (which also removed the header of course).

The image I saw was for the Report filter, not the Grouping filter. The image in this thread titled "report design" shows the Report Properties dialog, and shows a Filter in that report.

Only other culprit I could think of would be the query for the report itself.

Can you upload the database, after obfuscating the sensitive data of course?

Hi Scott
Thanks for your patience.  I just looked at the report again and that filter is not there - not sure where it came from.

It is not possible to send the db now - it is gigantic and has lots of personal info in it.

What I intend to do now is to create a test db and work from there.  If I still have a problem with that I will send to you.

Regards

Richard

Hi Scott
Right - I have it all working.  The basic problem was the report query, that included two Where statements.
One was limiting the records for testing, the second filtering a=on a flag that was required.
I needed the latter filter, so I created a separate query on that table and referenced that query in the report query, rather than the table.  
This solved the basic problem.

I then had it crashing at various points and found that was due to characters in the Name field (colons, slashes and return chars).
Rather than try to handle this in the code I went all through the db and found the problems and corrected.  This code will not be used much so that is a reasonable risk.!!

Thank you for your patience and help.

Regards

Richard