rltomalin
asked on
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
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
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
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
Regards
or for example
DoCmd.OpenReport "YourQuery", acViewPreview , , "ID=" & YourFilter, , acHidden
DoCmd.OutputTo acOutputReport, "YourQuery", acFormatPDF, strFile & YourFilter & ".pdf", False
DoCmd.Close acReport, "YourQuery"
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.
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
set qd=currentdb.querydefs("Q_
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.
ASKER
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
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
ASKER
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("S ELECT OrgID FROM tblOrg")
Do Until rst.EOF
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta ils", , , "OrgID='" & rst("OrgID") & "'", acHidden
DoCmd.OutputTo acOutputReport, "rptOrgsWithActiveOppsDeta ils", acFormatPDF, "C:\VBASEpdf\" & rst("OrgID") & ".pdf"
DoCmd.Close acReport, "rptOrgsWithActiveOppsDeta ils"
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
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("S
Do Until rst.EOF
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta
DoCmd.OutputTo acOutputReport, "rptOrgsWithActiveOppsDeta
DoCmd.Close acReport, "rptOrgsWithActiveOppsDeta
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.
ASKER
Yes - 2007
Regards
Richard
Regards
Richard
Is OrgID a Text value or Numeric?
If it's Text, you'll use this syntax:
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta ils", , , "OrgID='" & rst("OrgID") & "'", acHidden
If it's Numeric you use this:
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta ils", , , "OrgID=" & rst("OrgID"), acHidden
If it's Text, you'll use this syntax:
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta
If it's Numeric you use this:
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta
ASKER
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("S ELECT Name FROM tblOrg")
Do Until rst.EOF
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta ils", , , "Name='" & rst("Name") & "'", acHidden
DoCmd.OutputTo acOutputReport, "rptOrgsWithActiveOppsDeta ils", acFormatPDF, "C:\VBASEpdf\" & rst("Name") & ".pdf"
DoCmd.Close acReport, "rptOrgsWithActiveOppsDeta ils"
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
I have made some slight progress. This is what the code looks like now:
**************************
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("S
Do Until rst.EOF
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta
DoCmd.OutputTo acOutputReport, "rptOrgsWithActiveOppsDeta
DoCmd.Close acReport, "rptOrgsWithActiveOppsDeta
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
ASKER
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
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 "rptOrgsWithActiveOppsDeta ils", , , "Name='" & Replace(rst("Name"), "'", "''") & "'", acHidden
Essentially, this adds a second single quote.
You can also do this:
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta ils", , , "Name=" & Chr(34) & rst("Name") & Chr(34), acHidden
Chr(34) is the double quote.
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta ils", acViewPreview , , "Name='" & rst("Name") & "'", acHidden
Do Until rst.EOF
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta ils", acViewPreview, , "Name='" & rst("Name") & "'"
rst.MoveNext
Loop
This should show a report for each "Name" value, with the report filtered for that Name.
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta
Essentially, this adds a second single quote.
You can also do this:
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta
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 "rptOrgsWithActiveOppsDeta
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 "rptOrgsWithActiveOppsDeta
rst.MoveNext
Loop
This should show a report for each "Name" value, with the report filtered for that Name.
ASKER
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
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.
ASKER
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
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
ASKER
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 "rptOrgsWithActiveOppsDeta ils", 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).
It made no difference I'm afraid.
The code:
Do Until rst.EOF
DoCmd.OpenReport "rptOrgsWithActiveOppsDeta
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?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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