Export MS Access 2007 report to pdf for each record in table

Question

 

Hi All

I have a little dilemma I have been asked to make an Access DB not a big problem.

I was asked to make 2 reports still no problem.

Here starts the problem…

I have a table in the database [users] containing a field named [name].

Now I need to create a report in .pdf format for each user I have found this:

http://social.msdn.microsoft.com/Forums/en/isvvba/thread/bf5dc54a-9c39-42e7-a175-2984825a60be

Now this works great as long as I only need to print the open report. I guess what I need is a way to pass all user’s names to the report one after the other and exporting the “report [name].pdf” in between.

Any good ideas how to work around this?

---

Thanks Jesper

Answer 1

Hi Jesper,

I did something similar before, see below code to parse the UserName in the Where condition of your Report and in your FileName.

Make sure you change the PathName, TableName and FieldName accordingly.

See below example:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim temp As String


mypath = "C:\YourpathName\"

Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT UserName FROM tblUSers", dbOpenDynaset)

Do While Not rs.EOF

temp = rs("UserName")
MyFileName = rs("UserName") & ".PDF"

DoCmd.OpenReport "rptYourReportName", acViewReport, , "[UserName]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "rptYourReportName"

rs.MoveNext
Loop

Set rs = Nothing
Set db = Nothing

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 2

Hi Danishani

This looks very good.

I set [UserName] as the Criteria in each query that the report builds on and whenI run the script I am asked for the parameter "UserName" for each query and even though it creates one pdf with the name and the static parts of the report for each user in the table the dynamic part is empty because it is missing the parameter "UserName".

---

Thanks Jesper

Answer 3

Hi Jesper,

No need to set the criteria in your query to [UserName] as in the code the Where condition of opening the Report is already set to each unique UserName as it loops through the Recordset.

The Username Field however has to be available in the Query of your Report to be able to Filter the Report on each UserName.

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 4

Hi Daniel van den Berg

I have removed all the criteria in my queries so far so good access does no longer ask for the parameter.

it does create a pdf document for each user in the table. users

Function ExportToPdf()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim temp As String
mypath = "C:\data\access\"
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT NameForPDF FROM users", dbOpenDynaset)
Do While Not rs.EOF
temp = rs("NameForPDF")
MyFileName = rs("NameForPDF") & ".PDF"
DoCmd.OpenReport "Scorecard", acViewReport, , "[corrected name]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "Scorecard"
rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing
End Function

However the reports are empty:

Here are a picture of the query that should will in the sub report Monthly Customer Sat.

 Let me know if you need to see anything else

---

Thanks Jesper

Answer 5

Hi Jesper,

I see you have a SubReport within your Main Report, how is the relation setup between the Main and Subreport, is that by UserName (corrected name)?

With relation setup I mean the Link Master and Link Child Fields, the Master is for example the [ID] in your Main report and the Child is the [ID] in your SubReport.

While you pass the Where condition with the Main report, the Subreport needs to be linked with the same condition.

In other words if I use the [Corrected Name] as condition, that would be the link to the Subreport as well.

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 6

I have set it up like this:
Let me just thank you for your time and effort in this.

I think this is what you mean and that it should be right but the only change is that the [Corrected name] is now in the header of the page.

---

Thanks Jesper

Answer 7

Hi Jesper,

I am not sure, but what I think happen is that there are several [corrected name] fields in your query, which than might be causing difficulty for the Where statement to find the right one in the query. Again not sure, but you may try to change the code into this:

Function ExportToPdf()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim temp As String
mypath = "C:\data\access\"
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT NameForPDF FROM users", dbOpenDynaset)
Do While Not rs.EOF
temp = rs("NameForPDF")
MyFileName = rs("NameForPDF") & ".PDF"
' set here the [tblMain].[corrected name] of your Main Report, change the tblMain in the actual Table Name
DoCmd.OpenReport "Scorecard", acViewReport, , "[tblMain].[corrected name]='" & temp & "'"
DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "Scorecard"
rs.MoveNext
Loop
Set rs = Nothing
Set db = Nothing
End Function

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 8

Hi Daniel

Tried to add the [user]. or [final weekly personal scorecard] and the first time it asked for the value of [final weekly personal scorecard].[corrected name] I clicked on cancle and after that the function can not run when I try I get told Access encountered an error and needed to restart.

---

Thanks Jesper

Answer 9

I have the feeling that we are almost there, but I can't seem to find the missing clue to solve this.

I did some tests at my end and can't reproduce your situation. I believe it might be related to the links between main report and sub report.

I try to dig into this matter, hope I come up with something useful.

In case you are able to share this part of your database, stripped down, without any sensitive data, only the necessary table/query/reports that would be helpful to do some more testing.

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 10

Hi Daniel

I have uploaded a copy to my skydrive https://skydrive.live.com/redir.aspx?cid=08379bee80ec23a4&resid=8379BEE80EC23A4!195&parid=8379BEE80EC23A4!153&authkey=!AIdzclOPzH1eD-c you should be able to get it from there.

The experince I had yesterday that Access crashed when running the function seem to be only on that one computer now that I am at work it works again.

Hope you can access the file.

---

Thanks Jesper

Answer 11

Hi Jesper,

I can't get it to work, also I have some compatibility problems as I have Access 2007 and yours is build in Access 2010.

Also I am not able to run your reports due to errors like "The expressions is typed incorrectly, or is too complex to be evaluated" and "Data type missmatch in expression".

Do you encounter similar messages? If so, I would suggest solving these issues first, then you might try the automation to PDF after that.

The error messages points to incorrect data type in Querys. So you might check when you link tables/queries togethere that the key fields are from the same DataType.

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 12

Hi Daniel

I have taken a step back and removed all VBA from the DB and changed it to open the report based on what is selected in a dropdown list on the Open report Form. I have also saved a version in Access 2003-2003 mode just in case you would still have problems opening the file from access 2010.

https://skydrive.live.com/redir.aspx?cid=08379bee80ec23a4&resid=8379BEE80EC23A4!195&parid=8379BEE80EC23A4!153&authkey=!AIdzclOPzH1eD-c

I do not get that error message "The expressions is typed incorrectly, or is too complex to be evaluated" at all but I checked just to be sure and I have not been able to find any data type inconsistencies in any of the queries.

Hope this can bring us to a resolution.

---

Thanks Jesper

Answer 13

Hi Jesper,

Thanks for doing this, but unfortunately I receive a message that the "item not exists or is no longer available" message when trying to open the link to SkyDrive.

Maybe try again.

Cheers,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 14

Hi Daniel

Can you try this link instead:

https://skydrive.live.com/redir.aspx?cid=08379bee80ec23a4&resid=8379BEE80EC23A4!198&parid=8379BEE80EC23A4!153&authkey=!ACaBkWRoXlR3mbA

Hope this works if not we can allways try Dropbox :-)

---

Thanks Jesper

Answer 15

Hi Jesper,

It took some time, the problem is you are using the Combobox with a name called "Name".

This is a reserved word in Access, and causes all kind of trouble, therefore you need rename that though all your queries.

I did that for you, so you own me a beer at least....hehe ;)

Then with that, when looping through the code, I can then assign the next Agent name to the Comboxbox and set this as a parameter for the Report.

In your Form Open Scoreboard, I added the button "Export PDF", in here you will find the modified export routine for running the PDF reports for each agent.

I felt free to add me as an Agent Name, so we could test a run with more agents.

Below the modified database:

https://skydrive.live.com/redir.aspx?cid=7427c4b2d60cc2c7&resid=7427C4B2D60CC2C7!316&parid=7427C4B2D60CC2C7!241&authkey=!APGJX4FkeykqdzI

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 16

 

Hi Daniel

That works perfect, thank you very much.

How could I have missed that one with the name "I must have been sleeping big time." I definitely owe you something can we take that in private... somehow.

I don't know if you saw the other report in the Database he one called "Agent feedback" this on needs 2 parameters the Agent name and the week.

Would the solution to that be like this?

Me.cboName.Value = rs("Agent Name")
' set the Me.cboName as filter for your report
DoCmd.OpenReport "Scorecard", acViewReport, Me.cboName AND Week

Or how do I get that to work

---

Thanks Jesper

Answer 17

Hi Jesper,

You don't need to set the filter for the Report, you can leave that out of the code for both Reports, as your already use the Form parameters.

I was playing with some of those properties, and forget to leave that out, it has no affect.

I was just kidding about owing me something, glad to help you out! :)

Below the routine for the Week report, this time you need to use two recordset and loop between both of them.

I did not take care of empty reports, so there will be empty PDF, when there is nothing to report for a certain week.

You can handle this with the .HasData property of your Report.

The routine:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim rs2 As DAO.Recordset

Dim strWhere As String


Dim MyFileName As String
Dim mypath As String
Dim temp As String
mypath = "C:\data\access\"
Set db = CurrentDb()

Set rs = db.OpenRecordset("SELECT [Agent Name] FROM Agents", dbOpenDynaset)
   
   Set rs2 = db.OpenRecordset("SELECT [Week] FROM Weeknumbers", dbOpenDynaset)

Do Until rs.EOF

 Me.AgentName.Value = rs("Agent Name")

  rs2.MoveFirst
    Do Until rs2.EOF
     ' create the Filename with both values
     MyFileName = rs("Agent Name") & "wk" & rs2("Week") & ".PDF"

      Me.Week.Value = rs2("Week")
              
          ' open report, export pdf, closing
          DoCmd.OpenReport "Agent feedback", acViewReport
          DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
          DoCmd.Close acReport, "Agent feedback"
         
    rs2.MoveNext
    Loop

rs.MoveNext
Loop

Set rs = Nothing
Set rs2 = Nothing
Set db = Nothing

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer as an answer when your question is being answered.

Answer 18

Hi Daniel

Sorry for not making it clear but I do not want to create the Agent feedback pdf's for each week in the year every week, but just at any given time the last 3 weeks

Some thing like:
=====

Weeknumber1 = datepart("ww",now())-1

Export pdf for all agents

Then

Weeknumber2 = datepart("ww",now())-2

Export pdf for all agents

Then

Weeknumber3 = datepart("ww",now())-3

Export pdf for all agents

=====

So that each agent will recieve 4 reports each week with the Scorecard and one Agent feedback for each of the last 3 weeks.

---

Thanks Jesper