Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    agure is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2014
    Posts
    30

    Export Report to PDF with Button


    Access 2007 on Windows 8.1. I have installed the Save as PDFandXPS addon .


    So I have a certain code here and I cant seem to get it to run i always get a Runtime error "2501" OutputTo Action was cancelled .

    I searched long and hard and used a code that seemed to have worked for alot of people. Here is the event vba code when I click the button.

    I would like it so when i click the button what happens is the report with the filter for the certain Invoice # is saved as a pdf file.


    Code:
    Dim MyFilter As String
    Dim MyPath As String
    Dim MyFilename As String
    
    
    MyFilter = [Invoice#] = [Forms]![Job List (New Job) Form]![Invoice#]
    
    
    
    
    MyPath = "C:\TestFolder\" & Format(Me.Invoice_NO_)
    
    
    MyFilename = Format(Me.Job_Date, "yyyy") & _
                     "-" & Format(Me.Job_Date, "dd") & Format(Me.Job_Date, "mm") & _
                     "-" & Me.Job_Date & ".pdf"
    
    
    
    
    DoCmd.OpenReport "Final Invoice Report", acViewPreview, , MyFilter
    
    
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename
    
    
    
    
    DoCmd.Close acReport, "Final Invoice Report"

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Is the Invoice# an actual number or is it text? Here is an example for numbers.

    Code:
    
    MyFilter = "[Invoice#] = " & [Forms]![Job List (New Job) Form]![Invoice#]
    Is there a subfolder inside TestFolder?

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I think you need to specify the report to output. I'd use this on the path variables to make sure they're coming out as you expect:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    agure is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2014
    Posts
    30
    The invoice# is a text value. There is no subfolder in there. Thanks for the reply

  5. #5
    agure is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2014
    Posts
    30
    Quote Originally Posted by pbaldy View Post
    I think you need to specify the report to output. I'd use this on the path variables to make sure they're coming out as you expect:

    http://www.baldyweb.com/ImmediateWindow.htm
    I am going to try out this method and let u know where exactly the error is

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Per MSKB:

    ObjectName: If you want to output the active object, specify the object's type for the ObjectType argument and leave this argument blank.

    So don't even use the empty string.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    agure is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2014
    Posts
    30
    Quote Originally Posted by pbaldy View Post
    I think you need to specify the report to output. I'd use this on the path variables to make sure they're coming out as you expect:

    http://www.baldyweb.com/ImmediateWindow.htm
    I added Debug.Print three times plus the variables that I used but in the immediate window i get only TRUE.

  8. #8
    agure is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2014
    Posts
    30
    Quote Originally Posted by June7 View Post
    Per MSKB:

    ObjectName: If you want to output the active object, specify the object's type for the ObjectType argument and leave this argument blank.

    So don't even use the empty string.
    I didn't quite get that so I should write ?? DoCmd.OutputTo acOutputReport, acFormatPDF, MyPath & MyFilename

    I tried this and it didn't work either

  9. #9
    agure is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2014
    Posts
    30
    Quote Originally Posted by pbaldy View Post
    I think you need to specify the report to output. I'd use this on the path variables to make sure they're coming out as you expect:

    http://www.baldyweb.com/ImmediateWindow.htm
    I tried this but i keep getting TRUE in the immediatewindow, i also specified the report to output and same error

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by agure View Post
    I tried this but i keep getting TRUE in the immediatewindow, i also specified the report to output and same error
    Hard to say why without seeing your code.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    agure is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2014
    Posts
    30
    Update :

    I no longer get any sort of error, I used Debug.Print and noticed the MyFileName had a weird character so i Fixed that. The only concern I have is that the filter is not being applied it gives me a pdf file with all the Invoice #'s when I want just the specific Invoice#. This filter works fine when I use for a normal print preview.

    I would also like to know how can i set it up so it doesn't keep overwriting the previous file if i output the same invoice# is there a away to add the current time on to the filename???

    Here is my new code

    Code:
    Dim MyFilter As String
    Dim MyPath As String
    Dim MyFilename As String
    
    
    
    
    MyFilter = [Invoice#] = [Forms]![Job List (New Job) Form]![Invoice#]
    
    
    
    
    Debug.Print MyFilter
    
    
    
    
    
    
    
    
    MyPath = "C:\TestFolder\" & Format(Me.Client_Name)
    Debug.Print MyPath
    
    
    
    
    
    
    MyFilename = Format(Me.Job_Date, "yyyy") & _
                     "-" & Format(Me.Job_Date, "dd") & Format(Me.Job_Date, "mm") & ".pdf"
                    
                     
    Debug.Print MyFilename
    
    
    
    
    DoCmd.OpenReport "Final Invoice Report", acViewPreview, , MyFilter
    
    
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, MyPath & MyFilename, True
    
    
    
    
    DoCmd.Close acReport, "Final Invoice Report"

  12. #12
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Take a look at this code. I added the report name to the outputto and concatenated your where criteria to be able to handle text.

    Code:
    Dim MyFilter As String
    Dim MyPath As String
    Dim MyFilename As String
    MyFilter = "[Invoice#] = '" & [Forms]![Job List (New Job) Form]![Invoice#] & "'"
    MyPath = "C:\TestFolder\" & Me.Client_Name & "_"
    MyFilename = Format(Me.Job_Date, "yyyy") & _
                     "-" & Format(Me.Job_Date, "dd") & Format(Me.Job_Date, "mm") & ".pdf"
                    
    DoCmd.OpenReport "Final Invoice Report", acViewPreview, , MyFilter
    DoCmd.OutputTo acOutputReport, "Final Invoice Report", acFormatPDF, MyPath & MyFilename, True
    DoCmd.Close acReport, "Final Invoice Report"

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The arguments still need to fall in the correct order. The empty string should have worked but I usually don't put anything if the argument is not used. But still need the comma.

    DoCmd.OutputTo acOutputReport, , acFormatPDF, MyPath & MyFilename

    Could simplify, although it seems odd to put day between year and month. Most would do yyyymmdd:

    MyFilename = Format(Me.Job_Date, "yyyy-ddmm-") & Me.Job_Date & ".pdf"

    Why repeat the date in the file name? This will be an issue because the date has slashes.

    Is there supposed to be a folder named by Invoice_No? If yes, concatenation of MyPath and MyFileName needs a \ between them.

    Also had Format() without the format argument.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    agure is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2014
    Posts
    30
    Quote Originally Posted by ItsMe View Post
    Take a look at this code. I added the report name to the outputto and concatenated your where criteria to be able to handle text.

    Code:
    Dim MyFilter As String
    Dim MyPath As String
    Dim MyFilename As String
    MyFilter = "[Invoice#] = '" & [Forms]![Job List (New Job) Form]![Invoice#] & "'"
    MyPath = "C:\TestFolder\" & Me.Client_Name & "_"
    MyFilename = Format(Me.Job_Date, "yyyy") & _
                     "-" & Format(Me.Job_Date, "dd") & Format(Me.Job_Date, "mm") & ".pdf"
                    
    DoCmd.OpenReport "Final Invoice Report", acViewPreview, , MyFilter
    DoCmd.OutputTo acOutputReport, "Final Invoice Report", acFormatPDF, MyPath & MyFilename, True
    DoCmd.Close acReport, "Final Invoice Report"
    Your Code works flawlessly, I'm just wondering now how can I make it so it doesn't overwrite files with the same name how can i successfully add the time function to the filename to do this? Also the Client Name shows in file name and doesnt make it new folder Im having trouble with the Mypath so it makes a new folder with the client name.

  15. #15
    agure is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2014
    Posts
    30
    Quote Originally Posted by June7 View Post
    The arguments still need to fall in the correct order. The empty string should have worked but I usually don't put anything if the argument is not used. But still need the comma.

    DoCmd.OutputTo acOutputReport, , acFormatPDF, MyPath & MyFilename

    Could simplify, although it seems odd to put day between year and month. Most would do yyyymmdd:

    MyFilename = Format(Me.Job_Date, "yyyy-ddmm-") & Me.Job_Date & ".pdf"

    Why repeat the date in the file name? This will be an issue because the date has slashes.

    Is there supposed to be a folder named by Invoice_No? If yes, concatenation of MyPath and MyFileName needs a \ between them.

    Also had Format() without the format argument.

    I would like to make it so the code runs and then the folder would be the client name and then inside that would be the files with invoices kind of having hard time with this lol. everything else works fine. Could you further explain "If yes, concatenation of MyPath and MyFileName needs a \ between them."

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. command button to export to excel
    By jains in forum Forms
    Replies: 5
    Last Post: 06-30-2015, 06:27 PM
  2. Replies: 2
    Last Post: 01-29-2013, 07:01 PM
  3. Export to HTML Button on Form
    By iProRyan in forum Forms
    Replies: 2
    Last Post: 04-26-2012, 11:41 AM
  4. Export to excel on button click
    By Jim.H. in forum Access
    Replies: 2
    Last Post: 01-29-2012, 12:16 PM
  5. Replies: 0
    Last Post: 03-13-2011, 03:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums