Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    forbudt4u is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    10

    Question Print PDF Loop due to Error 3014

    Hello, tried finding a solution to this error 3014 (cannot open additional tables) I keep receiving when running a print to pdf loop. I've tried everything I can find on the subject and my process produces the Error 3014 at pdf file # 122 of roughly 200. Since I can't seem to fix this, I thought I could see about creating an additional button and have it set up to where when I press button # 1 it will filter & produce the reports for names that start with A-M, and when I press button # 2 it will filter & produce reports for names that start with N-Z. For clarification, when I say names that start with, I'm referring the the first letter of the persons name in Holder field of the Assets Extended Query I'm pulling the data from. My code is below, and I will have to completely close out of Access and open it back up in order to run the 2nd batch of pdf files because once I get the error 3014 it won't let me open anything without producing an additional memory error. Any help or insight given would be greatly appreciated!

    Private Sub Command331_Click()
    Dim db As DAO.Database


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




    mypath = "C:\Users\#####\OneDrive - ConstructMobile\Documents\FAW Small Asset Tracker\WASP REPORT CONNECTOR\Holder Reports\Asset Report"


    Set db = CurrentDb()


    Set rs = db.OpenRecordset("SELECT Distinct [Holder] FROM [Assets Extended]", dbOpenSnapshot)


    Do While Not rs.EOF


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

    DoCmd.OpenReport "Assets by Holder", acViewReport, , "[Holder]='" & temp & "'"
    DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName, , , , acExportQualityPrint
    DoCmd.Close acReport, "Assets by Holder"
    DoEvents


    rs.MoveNext
    Loop




    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    End Sub

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Food for thought: https://stackoverflow.com/questions/...any-tables-ope
    See if you can modify the recordsource of the query on each loop and avoid running DoCmd.OpenReport?

    More food for thought: http://access.mvps.org/access/bugs/bugs0010.htm

    Finally: https://www.fmsinc.com/microsoftacce...idDoEvents.asp

    I haven't fixed your issue but I've refactored your code a bit:
    Code:
    Private Sub Command331_Click()
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim MyFileName As String
        Dim mypath As String
        Dim my_report As String
        
        mypath = "C:\Users\#####\OneDrive - ConstructMobile\Documents\FAW Small Asset Tracker\WASP REPORT CONNECTOR\Holder Reports\Asset Report"
        my_report = "Assets By Holder"
        
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("SELECT Distinct [Holder] FROM [Assets Extended]", dbOpenSnapshot)
        
        If Not (rs.BOF And rs.EOF) Then
            rs.MoveFirst
            
            Do While Not rs.EOF
    
                MyFileName = rs!Holder & ".PDF"
                
                DoCmd.OpenReport my_report, acViewReport, , "[Holder]='" & rs!Holder & "'"
                DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName, , , , acExportQualityPrint
                DoCmd.Close acReport, my_report
                DoEvents
                rs.MoveNext
            Loop
        End If
    
        rs.Close
    ExitHandler:
        Set rs = Nothing
        Set db = Nothing
        Exit Sub
    
    ErrHandler:
        MsgBox Err.Description, , "Error #" & Err.Number
        Resume ExitHandler
    End Sub

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Please use code tags for more than a few lines of code (# on forum toolbar)
    I suspect the issue is a combination of opening the report in a report view as well as outputting the same report. I would try acHidden to see if that helps.
    There was a similar case here within the last couple of days that I posted to. It required output as well as filtering the report but I can't recall exactly what the code was.
    Problem is that you don't have to open in order to output, but there is no ability to filter without opening AFAIK. Will look for that thread, but I don't think OP was opening so many reports. I would also eliminate Do Events as a test - don't think it's necessary.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Seems I forgot to come back after dinner. This was the post
    https://www.accessforums.net/showthr...t=82734&page=2

    Solution in post 18 - same as that of the link I believe.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    forbudt4u is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    10
    Thanks for all of the suggestions guys. I've spent hours running through every option covered in them and I can't seem to get anything to work. @ssanfu, the site you sent me to seems to be promising, but I can't get past two things that seem to hold me up... one is that I'm filtering the report to run through every single holder and it produce a pdf for each respective holder, so this guys solution appears to only produces one file. I can also not get past the "criteria" issue seeing that the filter needs to produce these same respective reports individually. I already have the report grouped, filtered, and sorted the say I want, I'm simply having the code produces a report for each holder, which happens to correspond with how the top grouping of the report runs... by holder!

    Moreover, regarding the error 3014 I keep getting, I know everyone out there says this is a JET issue and that it's breaking because access will only allow so many calls (2048 I believe). However, what I find interesting is that I have been messing around with different things in Access while I have my task manager open. I have purposely pushed my Access used RAM up to different values ranging from 100MB to 400MB, and then running my code after it reaches that threshold. Without fail, when the used Access RAM gets up to 936MB is when the code breaks. Below is what I get when messing around with this...

    Run code with Access freshly open: 122 PDF's produced when it crashes (@ 936MB RAM)
    Run code with 100MB Access RAM used: 109 PDF's produced when it crashes (@ 936MB RAM)
    Run code with 200MB Access RAM used: 96 PDF's produced when it crashes (@ 936MB RAM)
    Run code with 300MB Access RAM used: 84 PDF's produced when it crashes (@ 936MB RAM)
    Run code with 400MB Access RAM used: 77 PDF's produces when it crashes (@ 936MB RAM)

    It seems to me the 3014 error I'm getting is tied to a RAM limitation with Access seeing that the crashes happen at 936MB every single time and the PDF's produced is dwindled further the more RAM is being utilized when running the code.

    With all that being said, what would I put in the Set rs field in order to have my two button attempt work, one button and corresponding code for names (holders) that start with letters A-M, and another button and corresponding code for names (holders) that start with letters N-Z?

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In the off chance that you find A to M too much, then something you could easily tweak would be to have a table with at least 2 fields CharGroup (character group) and Alpha as in this, showing only 2 groups up to K. Then in an outer loop (code of one button), get the characters in order where the number is 1 and use LIKE (LIKE [Alpha] & "*") in the sql in the inner loop. When you exit the inner loop, you advance to the next character until there are no more 1's. To extend or shorten the loop, assign the characters different number values in the table. There would be no real limit to how many groups you could make.

    1 A
    1 B
    1 C
    1 D
    1 E
    1 F
    1 G
    2 H
    2 I
    2 J
    2 K


    Many ways to skin this cat. A Select Case where case tests Left(rs!Holder) for "A","B","C"..."M" might work but each tweak would require modifying code.
    Last edited by Micron; 02-04-2021 at 02:05 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I merged the example from the link in my post #4 with the code provided by kd2017, then created a table vaguely like what I hope is your table design. I added 3 names and a total of 8 records.
    Running the following code I had 3 separate PDF files.
    Code:
    '=======================================
    ' Author: kd2017
    ' accessforums.net
    ' 3 Feb,2021
    ' Modified: ssanfu 4 Feb 2021
    '=======================================
    
    Private Sub Command331_Click()
    On Error GoTo ErrHandler
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim MyFileName As String
        Dim mypath As String
        Dim my_report As String
        Dim criteria As String
    
    '    mypath = "C:\Forum\"    'path for my testing
        mypath = "C:\Users\#####\OneDrive - ConstructMobile\Documents\FAW Small Asset Tracker\WASP REPORT CONNECTOR\Holder Reports\Asset Report\"
        my_report = "Assets By Holder"
        
        Set db = CurrentDb()
        Set rs = db.OpenRecordset("SELECT Distinct [Holder] FROM [Assets Extended]", dbOpenSnapshot)
        
        If Not (rs.BOF And rs.EOF) Then
            rs.MoveFirst
            
    
            Do While Not rs.EOF
            '    "FAW Small Assets Inventory Report, [Holder], Today()"
                MyFileName = mypath & "FAW Small Assets Inventory Report-" & rs!Holder & "-" & Format(Date, " mmm d yyyy") & ".PDF"
    '            Debug.Print MyFileName
                criteria = "[Holder]='" & rs!Holder & "'"
                DoCmd.OpenReport my_report, acViewPreview, , criteria, acHidden
                DoCmd.OutputTo acOutputReport, my_report, acFormatPDF, MyFileName, , , , acExportQualityPrint
                DoCmd.Close acReport, my_report
                
                DoEvents
                rs.MoveNext
            Loop
        End If
    
    ExitHandler:
        On Error Resume Next
        DoCmd.Close acReport, my_report
        rs.Close
        Set rs = Nothing
        Set db = Nothing
        MsgBox "Done"
        Exit Sub
    
    ErrHandler:
        MsgBox Err.Description, , "Error #" & Err.Number
        Resume ExitHandler
    End Sub
    These are the names of the PDF files that were generated:
    Test-steve- Feb 4 2021.PDF
    Test-rob- Feb 4 2021.PDF
    Test-sam- Feb 4 2021.PDF

  9. #9
    forbudt4u is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    10
    ssanfu.... your code worked!!!!!!!!!!!!!!!!!!!!!! I will also report that the used RAM for Access only reached 598MB for 206 produced reports. I could definitely tell it was handling the operation different as it ran much faster than when it was working and breaking before. It's more than likely due to the report staying open in the background and filtering from there rather than running a query for each new name. Either way, it worked and I'm thankful.

    Thanks for your help too, Micron... I really didn't want to have to use two buttons, but I'm pretty confident yours would have worked and will keep it bookmarked if my reports grow too large in number.

    Thanks again everyone... have a great weekend!
    Attached Thumbnails Attached Thumbnails Access Loop PDF DONE.PNG  

  10. #10
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    From my experiences that error usually means you've exceeded your table connections.
    Here's a utility to check.
    Just import the one form.
    Attached Files Attached Files
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm confused. How is the solution from post 5 (OK, post 18 at the provided link) any different than the code you said worked? Perhaps ssanfu can enlighten me because I don't see it. I'm thinking the savior here is opening the report as acHidden but I'm not sure.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    forbudt4u is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    10
    Micron, I too am wondering about that and gave my best guess at it... but can only report what worked and what didn't. This particular code has taken way too much space in my brain this week and I can't charge rent

  13. #13
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Main difference is highlighted in BLUE

    From post 18 at the provided link by Micron:
    DoCmd.OpenReport "GDLVSCL1 User Level Access Report", acViewReport, , "[ManagerName]='" & temp & "'", acHidden
    DoCmd.OutputTo acOutputReport, "GDLVSCL1 User Level Access Report", acFormatPDF, sFolder & "" & MyFileName
    DoCmd.Close acReport, "GDLVSCL1 User Level Access Report"
    From the link I posted:
    criteria = "[Holder]='" & rs!Holder & "'"
    DoCmd.OpenReport my_report, acViewPreview, , criteria, acHidden
    DoCmd.OutputTo acOutputReport, my_report, acFormatPDF, MyFileName, , , , acExportQualityPrint
    DoCmd.Close acReport, my_report
    Actually, I did not look at the link Micron posted until today.

    I left in the "DoEvents" just to ensure there was enough time to finish writing to the PDF and close the report.
    In the OP's original code, there was a missing backslash at the end of the myPath variable
    mypath = "C:\Users\#####\OneDrive - ConstructMobile\Documents\FAW Small Asset Tracker\WASP REPORT CONNECTOR\Holder Reports\Asset Report"
    The OP stated he wanted the PDF file name like
    "FAW Small Assets Inventory Report, [Holder], Today()"
    "Today" is an Excel function, not an Access function; the equivalent function in Access is Date(). But when you use Date(), the result (2/5/2021 - American format) has slashes to separate month, day and year: Windows does not allow "/"in file names, thus the Format function.




    @forbudt4u
    I noticed "mypath = "C:\Users\#####\OneDrive - ........."
    Q) Does this mean multiple people use the code? If so, instead of hard coding the path, you could write a function to get the "USERPROFILE", then check if the path exists. If the full path doesn't exist, then code could create the path.
    Just a thought.......


    One more thing. "Command331" as the button name??
    Don't you think "btnPrint2PDF" would be a better name? In 6 months, do you think you would remember what the sub "Command331" does? How about "btnPrint2PDF"? Ask me why I know this....
    Last edited by ssanfu; 02-05-2021 at 05:42 PM. Reason: added questions

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So was it the missing backslash or the part in blue that made the difference here? What I posted was the solution for the other thread although OP wasn't experiencing a 3014 error - hence my confusion. Thanks for the detailed explanation.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The missing backslash seemed to cause the code to fail because the path was incorrect/invalid. My understanding at this point is only acViewPreview should be used..... but I have not tested this (yet).

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

Similar Threads

  1. Report print loop
    By xjps in forum Access
    Replies: 20
    Last Post: 02-05-2020, 08:31 PM
  2. Loop Query and Print based on multiple criteria
    By beckysright in forum Queries
    Replies: 1
    Last Post: 11-15-2017, 01:17 AM
  3. Replies: 8
    Last Post: 10-10-2016, 04:26 PM
  4. Print in a loop
    By LonghronJ in forum Modules
    Replies: 4
    Last Post: 02-03-2016, 07:44 AM
  5. Replies: 8
    Last Post: 05-16-2011, 06:01 PM

Tags for this Thread

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