Results 1 to 9 of 9
  1. #1
    crowtan1 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    4

    PDF Generation : Error 3014 Cannot Open Any More Files

    Hi Guys

    I have built a solution to create customer statements and then email as PDF's. Everything works well, but when I get up to around 160 generations of the PDF's then I get the 3014 message. I've looked around a few areas and it could be a jet engine issue, but some other users seem to have got around the issue by removing the open report element of the coding and then change the query defn before doing an output to. Seemingly this then removes the issue.

    I am pretty much teaching myself the code, and have little experience of the query defn format, so would appreciate some help in understanding what this query defn would look like


    Below is the start of the do loop, it is how I replace docmd.open report with a reqry that then aligns to the data shown in the output report

    Any advice greatly appreciated
    strReport = "SecurityAdHocLetter"



    Do While Not CustomerEmailAddress.EOF
    DoCmd.OpenReport strReport, acViewReport
    Reports(strReport).Filter = "[Address_No] =" & Chr(34) & CustomerEmailAddress!Customer & Chr(34)
    Reports(strReport).FilterOn = True
    DoCmd.Save acReport, strReport
    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, "D:\Overdue Account.pdf"

    DoCmd.Close acReport, strReport

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You need to close your Do While statement. Where is your Wend?

  3. #3
    crowtan1 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    4
    That's just the top part of the coding, the do loop is further in the code as below, it is more how I do the query redfn rather than open.report

    strReport = "SecurityAdHocLetter"

    Do While Not CustomerEmailAddress.EOF
    DoCmd.OpenReport strReport, acViewReport
    Reports(strReport).Filter = "[Address_No] =" & Chr(34) & CustomerEmailAddress!Customer & Chr(34)
    Reports(strReport).FilterOn = True
    DoCmd.Save acReport, strReport
    DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, "D:\Overdue Account.pdf"

    DoCmd.Close acReport, strReport

    Cust = CustomerEmailAddress![Customer]
    hdr = "Overdue Account"
    spce = " "
    spce1 = " "
    FName = CustomerEmailAddress![FirstName]
    LName = CustomerEmailAddress![Surname]
    finalline = Cust + spce + hdr
    'Ementry = "If you have any concerns with this email please contact"
    DDIs = CustomerEmailAddress![DDI]

    Set objOutlook = CreateObject("Outlook.application")
    Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

    objOutlookMsg.SentOnBehalfOfName = "xyz@abc.co.uk"
    'objOutlookMsg.SentOnBehalfOfName = "xyz@abc.co.uk"
    Email_Address = CustomerEmailAddress![Email_Address]
    With objOutlookMsg
    Set objOutlookRecip = .Recipients.Add(Email_Address)
    objOutlookRecip.Type = olTo


    .Subject = finalline



    BodyFile$ = "X:\Dept\SecStatHTML3.txt"
    Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
    MyBodyText = MyBody.ReadAll
    MyBody.Close



    BodyFile1$ = "X:\Dept\SecFooter.txt"
    Set MyBody1 = fso.OpenTextFile(BodyFile1, ForReading, False, TristateUseDefault)
    MyBodyText1 = MyBody1.ReadAll
    MyBody1.Close

    .HTMLBody = MyBodyText + spce1 + "<p style='margin:1cm;font-size:10pt;font-family:Arial;font-weight:bold'>" + FName + spce1 + LName + spce1 + DDIs + MyBodyText1


    If Not IsMissing(AttachmentPath) Then
    Set objOutlookAttach = .Attachments.Add("D:\Overdue Account.pdf")
    End If
    For Each objOutlookRecip In .Recipients
    objOutlookRecip.Resolve
    If Not objOutlookRecip.Resolve Then
    objOutlookMsg.Display
    End If
    Next
    .Send
    End With
    CustomerEmailAddress.MoveNext

    DoCmd.Close acReport, strReport
    Loop
    Set objOutlookMsg = Nothing
    Set objOutlook = Nothing

    End Function

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by crowtan1 View Post
    ...That's just the top part of the coding, the do loop is further in the code as below, it is more how I do the query redfn rather than open.report...
    I thought the issue is that your code is opening too many reports before closing some of them. What is redfn? I do not see it mentioned anywhere.

  5. #5
    crowtan1 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    4
    No I am closing the reports but as stated some users determine the issue I am facing it is a Jet issue, and the way around it is to build in a QueryDef statement in stead of the Openreport statement I am currently using and I need help to understanding how to format the query definition element I would like to build in

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Here is one example of creating a querydef

    dim MyQueryDef as DAO.querydef
    set MyQueryDef = currentdb.querydefs("NameOfExistingQueryObject")

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It sure seems like you are doing a lot of extra work.......

    I have printed out a run of 620 PDFs without any error message.

    I cut your code down to the minimum and did the same with mine:
    Your code:
    Code:
        Do While Not CustomerEmailAddress.EOF
            DoCmd.OpenReport strReport, acViewReport
            Reports(strReport).Filter = "[Address_No] =" & Chr(34) & CustomerEmailAddress!Customer & Chr(34)
            Reports(strReport).FilterOn = True
            DoCmd.Save acReport, strReport
            DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, "D:\Overdue Account.pdf"
    
            DoCmd.Close acReport, strReport
    
        Loop
    My code:
    Code:
            Do Until r.EOF
                Me.ubEndPK = r("END_PK")
     
                DoCmd.OutputTo acOutputReport, strReport, acFormatPDF, "D:\EmpAccount.pdf", False
    
                r.MoveNext
            Loop
    My code actually creates a unique PDF name for every PDF. I cut that out to make the code samples more equal when comparing.

    My report record source is a query with 5 criteria. One of the criteria changes for every person - the hidden "ubEndPK" control.
    The OutputTo method knows it is outputting a report, knows the report name and knows the save as format. The report is opened (hidden), the record source query gets the criteria from the form, the PDF is created and saved. Done... Move on to the next.

  8. #8
    crowtan1 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Oct 2016
    Posts
    4
    Hi Steve,

    Thanks for this, just a couple of points for clarity if you would indulge. Not sure what you mean with 'gets the criteria from the form' - In my example I am reading directly a file that contains customer and email addresses, and for each record I then filter on the report which the underlying query works with - are you implying that I need a form based on your suggested solution?

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In my example I am reading directly a file that contains customer and email addresses,
    Looking at the code, I realize you didn't post the entire routine. but it sure looks like you are looping through a recordset, opening the report, then creating the PDF.
    Oops, it looks like I forgot to include a line in my post #7. Just before "Loop" in your code, there should be the line "CustomerEmailAddress.MoveNext".

    are you implying that I need a form based on your suggested solution?
    I use an existing form..


    So this is what I think is going on with your code:
    You have a form with a button to initiate the process.
    The report is opened. The report record source is one of three types:
    - a table name
    - a query (SQL string) like "SELECT * FROM TableName" , or
    - a query (SQL string) like "SELECT Customer, FirstName, Surname, Address_No, Email_Address FROM TableName"

    The report opens with every record being returned. Then you create a filter string and filter the report. Next is the PDF creation, then moving to the next "Address_No" (after the email is created and sent).


    What I do:
    I have a report where the record source is a query with criteria. The query looks like:
    Code:
     "SELECT Customer, FirstName, Surname, Address_No, Email_Address FROM TableName WHERE [Address_No] = '" & Forms!MyForm.ubAddress_No &  "';"
    (I really don't have those fields... just example field names)

    I have a form with an unbound text box that is hidden and a button to start the process.
    I loop through a recordset of selected names (in my case, it is a list box).
    I push the PK field to the unbound text box ("ubAddress_No").

    The "DoCmd.OutputTo" command is executed, creating the PDF which only has records matching the "Address_No" because of the record source "WHERE" clause, then moves to the next "Address_No". The report is never "opened".


    Because of the record set in VBA, there are three ways to get the "Address_No" to limit records in the report:
    1) create a filter
    2) change the querydef on-the-fly (like ItsMe suggested)
    3) use criteria (the WHERE clause) to limit records


    #2 & #3 are close to being the same, except #3 always has the same SQL for the report record source (just the parameter changes), where #2 keeps changing the report record source...

    I just think #3 is a cleaner method.


    Hope this is clearer.....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error message on importing files
    By Link in forum Import/Export Data
    Replies: 5
    Last Post: 08-26-2016, 06:14 PM
  2. Replies: 3
    Last Post: 12-24-2014, 11:47 AM
  3. Error on generation of report
    By TOMMY.MYERS668 in forum Reports
    Replies: 1
    Last Post: 03-02-2013, 11:06 AM
  4. run-time error 70 when importing text files from software
    By mejia.j88 in forum Import/Export Data
    Replies: 6
    Last Post: 06-05-2012, 04:10 PM
  5. Full app - find/store/open files in a table of any type
    By pkstormy in forum Sample Databases
    Replies: 2
    Last Post: 02-03-2012, 01:19 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