Page 1 of 4 123 ... LastLast
Results 1 to 15 of 50
  1. #1
    Join Date
    Nov 2003
    Posts
    300

    Unanswered: Need to automate .PDF creation from report list

    I have a report that allows the user to enter in criteria, say select a State, and it prints a report for them. What I would like to do is to have a MACRO button, that when they select it. Reads a State from the table, puts it in the report criteria, and prints the PDF or creates the PDF file in a folder.

    It sounds easy but have not been able to get it to work.

    Here is what I am using to select the report, a pop-up form that asks them for the State
    THANKS!
    [Forms]![frmStateSelectDialog]![Category]


  2. #2
    Join Date
    Nov 2003
    Posts
    300
    Can anyone help with this?

  3. #3
    Join Date
    May 2010
    Location
    Prison Town, USA
    Posts
    22
    I'm sorry, I don't have an answer for you, but I think I'm looking to do something similar to what you are.

    I want to have a bunch of PDFs saved under an employees name located in a folder on the server and to have a macro or something that can look at the employee name in the current record and open that PDF file for me to view it.

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by databasemon View Post
    I have a report that allows the user to enter in criteria, say select a State, and it prints a report for them. What I would like to do is to have a MACRO button, that when they select it. Reads a State from the table, puts it in the report criteria, and prints the PDF or creates the PDF file in a folder.

    It sounds easy but have not been able to get it to work.

    Here is what I am using to select the report, a pop-up form that asks them for the State
    THANKS!
    [Forms]![frmStateSelectDialog]![Category]


    What might have scared some off is the fact you said a macro. I never use macros except for the special macros autoexec and autokeys. I am not sure if this is even possible to do totally with macro commands and no VBA code. If it were me, I would use VBA code not a macro.

    What version of Access are you using?

    What method are you wanting to use to create the PDF? The 2007 feature or a third-party PDF writer.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Nov 2003
    Posts
    300
    Thank you ! No, I am open to using VBA coding although being so new to it [coming from an RPG background] I will need lots of examples or spelled out.

    I am using MS Access 2007, and would like to have Access create the PDF. I do have the Cute PDF and Adobe, but they are the free ones downloaded from their prospective web sites.

    I hope this helps!

  6. #6
    Join Date
    May 2010
    Posts
    601
    If you are using Access 2007, you can use the PDF creation feature add-in.

    I am not sure I understand this part:

    Quote Originally Posted by databasemon View Post
    Reads a State from the table, puts it in the report criteria, and prints the PDF or creates the PDF file in a folder.
    Will there be multiple records (states) in the table?

    If multiple records, will you need a report for each record in the table?

    Please provide more specific details on exactly what you need.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  7. #7
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Glühbirne View Post
    I'm sorry, I don't have an answer for you, but I think I'm looking to do something similar to what you are.

    I want to have a bunch of PDFs saved under an employees name located in a folder on the server and to have a macro or something that can look at the employee name in the current record and open that PDF file for me to view it.
    While this may sound like a good idea. I have tried it and found lots of issues.

    Using this method, I would urge you to use the primary key, hopefully a system assigned number like a autonumber, that will never change.

    I would avoid using the name since it can change. If the name was spelled incorrectly or changes then your link to all the documents no longer works. You would have to rename the files/folder.


    I find it best to have a record where you can put details about the file. No you have an "index" to search on. I store just the path to the file. This way the system can control the name to avoid duplicate file names.


    Here is a basic example:

    Document Links
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  8. #8
    Join Date
    Nov 2003
    Posts
    300
    Sure, let me explain further:

    Let's say the user has reports they need to create based on a Manager and then send the PDF report to them via PDF file. So, the number of Managers in the Table could vary depending on demotions, promotions, firings.. etc.

    ManagerSalesVolumeTipsAreaFred4511234433NorthTodd56123445NorthMark45654242North

    Instead of the report asking for the criteria, ie Manager Name, then producing the PDF...
    I want the VBA code to Pick the first record, which is FRED as the criteria, create the PDF, then pick TODD.. make PDF.. then MARK... etc. until the end of record.
    So, if this can be done automatically, when the user hits a command button and it runs the VBA code, which makes all the PDFs, then great, that is what I want it to do.

  9. #9
    Join Date
    Nov 2003
    Posts
    300
    ManagerSalesVolumeTipsAreaFred4511234433NorthTodd56123445NorthMark45654242North


    just an example of some of the records in the table

  10. #10
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by databasemon View Post
    ManagerSalesVolumeTipsAreaFred4511234433NorthTodd56123445NorthMark45654242North


    just an example of some of the records in the table
    That was my guess.

    So the steps will be:

    1) open a recordset that is a unique list oo the field [Manager Name] that has data matching the desired criteria.

    2) loop through the records generating report using the [Manager Name] from the recordset as the filter.


    This can all be done in the On Click event of a command button.

    So let start with step one. Is this something you can do?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  11. #11
    Join Date
    Nov 2003
    Posts
    300
    I will certainly try.
    _____________________________



    Function ManagerRecs As Boolean
    Do While Not rst.EOF

    If rst![MANAGER] = NOT NULL Then

    DOCMD.. Make PDF

    End If

    rst.MoveNext

    Loop
    I am not exact on my VBA coding, but I guess that this is what I am trying to do.

  12. #12
    Join Date
    May 2010
    Posts
    601
    Getting there, but missing some VBA code.

    Since you are wanting to do this in the On Click Event of a command button it would start out like this:

    Code:
    
    Private Sub report_to_pdf_Click()
    On Error GoTo Err_report_to_pdf_Click
    
    
        Dim rs As DAO.Recordset
        Dim strSQL As String
        
         
        strSQL = "Select ...."    
    
        Set rs = CurrentDb.OpenRecordset(strSQL)
        
        ' loop code goes here    
    
        rs.Close
        Set rs = Nothing
    
    Exit_report_to_pdf_Click:
        Exit Sub
    
    Err_report_to_pdf_Click:
        MsgBox Err.Description
        Resume Exit_report_to_pdf_Click
        
    End Sub
    The first thing to do is to create the SQL to select the unique list of managers that need the report.

    What will the SQL need to be in the variable strSQL in the above code?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  13. #13
    Join Date
    May 2010
    Posts
    601
    I have created an example that you might find helpful

    See:

    Batch Printing and Save As PDF
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  14. #14
    Join Date
    Nov 2003
    Posts
    300
    Much appreciation.

    I like to learn new things, especially with VBA, just a beginner here.
    The example you sent is excellent, I could try that solution as well.
    I also want to keep trying at that command button solution as the first try.

    Thanks HTC!

  15. #15
    Join Date
    Nov 2003
    Posts
    300
    Okay, Here is the SQL statement I plan to use:


    SELECT tblInsured.ApplicantLastName, tblInsured.ApplicantFirstName, tblAgent.Region, tblAgent.Manager, tblAgent.OfficeCode
    FROM tblInsured INNER JOIN tblAgent ON (tblInsured.Agent = tblAgent.AgentID) AND (tblInsured.MO = tblAgent.MO);

    This will give me the detail records that I want to print to a PDF when I run this query / print the PDF. This is fine....

    The problem that I am running into is that I want a PDF for each of the Regions. I am also storing the regions in a table called Regions.. So instead of the user typing in each Region name, there are almost 150, I want this code to select the first region from the table, then execute the SQL query above to get all the detail for the report, then Print the PDF, then next record.. etc.

    How am I doing so far?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •