none
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

    Friday, March 09, 2012 6:11 PM

Answers

  • 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.

    • Marked as answer by cezar1308 Monday, March 19, 2012 8:50 AM
    Monday, March 19, 2012 3:49 AM
    Moderator

All replies

  • 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.

    Friday, March 09, 2012 6:55 PM
    Moderator
  • 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

    Sunday, March 11, 2012 8:39 AM
  • 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.

    Sunday, March 11, 2012 5:21 PM
    Moderator
  • 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

    Monday, March 12, 2012 8:35 AM
  • 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.

    Monday, March 12, 2012 4:13 PM
    Moderator
  • 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

    Tuesday, March 13, 2012 10:23 AM
  • 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.

    Tuesday, March 13, 2012 3:22 PM
    Moderator
  • 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

    Tuesday, March 13, 2012 5:04 PM
  • 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.

    Tuesday, March 13, 2012 6:11 PM
    Moderator
  • 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

    Wednesday, March 14, 2012 8:08 AM
  • 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.

    Thursday, March 15, 2012 11:04 PM
    Moderator
  • 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

    Saturday, March 17, 2012 7:50 AM
  • 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.

    Saturday, March 17, 2012 4:14 PM
    Moderator
  • 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

    Saturday, March 17, 2012 5:04 PM
  • 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.

    • Marked as answer by cezar1308 Monday, March 19, 2012 8:50 AM
    Monday, March 19, 2012 3:49 AM
    Moderator
  •  

    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

    Monday, March 19, 2012 8:50 AM
  • 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.

    Monday, March 19, 2012 5:22 PM
    Moderator
  • 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

    Tuesday, March 20, 2012 7:07 PM