Solved

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

Posted on 2014-01-16
20
366 Views
Last Modified: 2014-01-22
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
Comment
Question by:rltomalin
  • 10
  • 7
  • 2
  • +1
20 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39785231
0
 
LVL 84
ID: 39785234
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
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39785243
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39785253
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
 

Author Comment

by:rltomalin
ID: 39785319
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
 

Author Comment

by:rltomalin
ID: 39793856
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
 
LVL 84
ID: 39793946
What version of Access are you using? This requires 2007 or higher.
0
 

Author Comment

by:rltomalin
ID: 39794011
Yes - 2007

Regards
Richard
0
 
LVL 84
ID: 39794059
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
 

Author Comment

by:rltomalin
ID: 39794099
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:rltomalin
ID: 39794111
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
 
LVL 84
ID: 39794148
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
 

Author Comment

by:rltomalin
ID: 39794278
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
 
LVL 84
ID: 39794843
In your image titled "report design", it looks like you've set a Filter on the report. Clear that and try it again.
0
 

Author Comment

by:rltomalin
ID: 39794912
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
 

Author Comment

by:rltomalin
ID: 39796320
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
 
LVL 84
ID: 39796532
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
 

Author Comment

by:rltomalin
ID: 39796632
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39796839
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
 

Author Closing Comment

by:rltomalin
ID: 39799400
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now