Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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

I'm not sure if this will be possible, but here goes.

I have a fairly simple report that is grouped on a particular field.
There is a new page for each new group with a group header.
Most groups are limited to one page but a few are 2 pages (and could be more)
The report produces about 160 pages.

What I need to do is to create a separate pdf file for each group.
The pdf file should have the name of the grouped by field
So I'm basically looking to press a button and produce around 150 pdf files in a folder somewhere.

This does not have to be particularly elegant - it won't be required very often (if ever again!)
But I would like to be able to avoid doing this manually (yawns).

Any tips for a possible approach would be most welcome.

Best regards

Richard
0
rltomalin
Asked:
rltomalin
  • 10
  • 7
  • 2
  • +1
1 Solution
 
Rgonzo1971Commented:
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Open 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.
0
 
Rgonzo1971Commented:
Hi,

or for example

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

Open in new window


Regards
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rey Obrero (Capricorn1)Commented:
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.
0
 
rltomalinAuthor Commented:
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
0
 
rltomalinAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What version of Access are you using? This requires 2007 or higher.
0
 
rltomalinAuthor Commented:
Yes - 2007

Regards
Richard
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
rltomalinAuthor Commented:
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:
Syntax error window
(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
0
 
rltomalinAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
rltomalinAuthor Commented:
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:
QueryThe 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:
Query result
This is the report in edit view:
Report design
This gives the following result (one page for each Org)
Report view(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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In your image titled "report design", it looks like you've set a Filter on the report. Clear that and try it again.
0
 
rltomalinAuthor Commented:
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
0
 
rltomalinAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
 
rltomalinAuthor Commented:
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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's probably the best way forward.

Often issues like this are caused by the query used to filter the report, or by groupings or settings on the report. You might try creating a new report with no grouping or such, and see what happens with that. Then add your groupings, etc etc until it "breaks" and let us know this.
0
 
rltomalinAuthor Commented:
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
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 10
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now