Creating pdf’s with Intermittent problem

[June7]

Yes, I posted code prematurely. Already edited post and all good now.

 

[earls]

Following revised code works for me:

Private Sub cmdSaveAsPDF_Click()
Dim qdf As DAO.QueryDef
Dim strPathName As String
Dim rs As Recordset
Dim stDocName As String
Dim strSavedSQL As String
If Me.Dirty Then Me.Dirty = False
stDocName = "xinvoice"
Set rs = CurrentDb.OpenRecordset("SELECT orderid, tripname FROM Orders WHERE SelectedPrint;", dbOpenSnapshot)
If rs.EOF Then
    MsgBox "Nothing found to process", vbCritical, "Error"
Else
    CreateFolder CurrentProject.Path & "\orders"
    ' store the current SQL
    Set qdf = CurrentDb.QueryDefs("Invoices")
    strSavedSQL = qdf.SQL
    While Not rs.EOF
        qdf.SQL = Left(strSavedSQL, InStr(strSavedSQL, ";") - 1) & " and (orderid = " & rs!OrderID & ");"
        ' put in the same folder as the database
        strPathName = CurrentProject.Path & "\orders\" & rs!tripname & ".pdf"
        DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, strPathName
        rs.MoveNext
    Wend
    ' restore the SQL
    qdf.SQL = strSavedSQL
    qdf.Close
    Set qdf = Nothing
End If
rs.Close
Set rs = Nothing
End Sub

A million thank's. worked perfect, just did a large scare run with no issues and perfect results.

 

[isladogs]

Yes, I posted code prematurely. Already edited post and all good now.

That'll teach me to be quick on the draw!
BUT Unfortunately it still errors for me when records selected
Definitely logging off now

Glad it works for the OP which is much more important!

 

[Cronk]

The problem with June's code is that the report is based on a saved query. Opening an instance of the querydef (qdf) and changing its sql, does not impact on the original saved query. The saved query has to be updated which can be achieved by

qdf.close: set qdf = nothing

The OP stores the initial sql in strSavedSQL so as to be able to restore the query when the pdf's have been generated

 

[earls]

Following revised code works for me:


The code is running perfect 95% of the time, every once and a while it won’t save than going forward it will not work until I manually clear out the stored criteria in the query (invoices). What I want to do is clear the stored orderID in the query before running the “save as pdf” code (in case of past error). Any suggestions would be appreciated.

Public Sub cmdSaveAsPDF_Click()

Dim qdf As DAO.QueryDef
Dim strPathName As String
Dim rs As Recordset
Dim stDocName As String
Dim strSavedSQL As String
If Me.Dirty Then Me.Dirty = False
stDocName = "xinvoice"
Set rs = CurrentDb.OpenRecordset("SELECT orderid, tripname FROM Orders WHERE SelectedPrint and filecreated=no;", dbOpenSnapshot)
If rs.EOF Then
    MsgBox "Nothing found to process", vbCritical, "Error"
Else
    'CreateFolder CurrentProject.Path & "\orders"
    ' store the current SQL
    Set qdf = CurrentDb.QueryDefs("Invoices")
    
    strSavedSQL = qdf.SQL
    While Not rs.EOF
        qdf.SQL = Left(strSavedSQL, InStr(strSavedSQL, ";") - 1) & " and (orderid = " & rs!OrderID & ");"
        ' put in the same folder as the database
        strPathName = "c:\LCTrips\" & rs!Tripname & ".pdf"
        DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, strPathName
 
        rs.MoveNext
    Wend
    ' restore the SQL
    qdf.SQL = strSavedSQL
    qdf.Close
    Set qdf = Nothing
End If
rs.Close
Set rs = Nothing
End Sub

 

[Cronk]

Why not just take the simple approach and generate the query's sql from scratch.

set qdf = db.querydefs("Invoices")
qdf.sql ="select <fields> from Orders where orderID=" & rs!OrderID
set qdf = nothing
<run the report, etc>

If you are use your Invoices query elsewhere, use a query that is unique to this reporting routine.

 

[June7]

Under what condition does the code get interrupted by error? Really need to figure out what causes that and deal with. And/or incorporate error handler code in the procedure that will allow clean exit of the procedure and reset the query.

...
On Error GoTo ErrProc
If Me.Dirty Then Me.Dirty = False
stDocName = "xinvoice"
Set rs = CurrentDb.OpenRecordset("SELECT orderid, tripname FROM Orders WHERE SelectedPrint;", dbOpenSnapshot)
If rs.EOF Then
...
End If
rs.Close
Set rs = Nothing
 
ExitProc:
    ' restore the SQL
    If Not qdf Is Nothing Then
        qdf.SQL = strSavedSQL
        qdf.Close
        Set qdf = Nothing
    End If
    Exit Sub
ErrProc:
    Resume ExitProc

Review http://allenbrowne.com/ser-23a.html

Cronk's suggestion is an option, however, repeatedly creating/deleting objects causes database bloat. Regardless, should run Compact & Repair periodically.

 

[earls]

Under what condition does the code get interrupted by error? Really need to figure out what causes that and deal with. And/or incorporate error handler code in the procedure that will allow clean exit of the procedure and reset the query.

...
On Error GoTo ErrProc
If Me.Dirty Then Me.Dirty = False
stDocName = "xinvoice"
Set rs = CurrentDb.OpenRecordset("SELECT orderid, tripname FROM Orders WHERE SelectedPrint;", dbOpenSnapshot)
If rs.EOF Then
...
End If
rs.Close
Set rs = Nothing
 
ExitProc:
    ' restore the SQL
    If Not qdf Is Nothing Then
        qdf.SQL = strSavedSQL
        qdf.Close
        Set qdf = Nothing
    End If
    Exit Sub
ErrProc:
    Resume ExitProc

Review http://allenbrowne.com/ser-23a.html

Cronk's suggestion is an option, however, repeatedly creating/deleting objects causes database bloat. Regardless, should run Compact & Repair periodically.

Initially was getting periotic errors due to font in report (was using Calibri Font), three-page pdf’s became 500kb each. Changed font to Times New Roman now reports are 8kb and running perfect on local computer in batches of 50 reports (my computer), but when running on VM it has periotic issues (the database is split but tables local on VM, no shared tables with others). Im thinking it’s the VM (power issue) or user is doing something odd. I will be visiting the user in 6 days, I’m hoping for an easy fix until I can get on site and troubleshoot.

 

[Cronk]

Cronk's suggestion is an option, however, repeatedly creating/deleting objects causes database bloat

Um, my post did not provide for deleting anything, only changing the sql for an existing query. I was suggesting not restoring to the original sql.

 

[earls]

Why not just take the simple approach and generate the query's sql from scratch.

set qdf = db.querydefs("Invoices")
qdf.sql ="select <fields> from Orders where orderID=" & rs!OrderID
set qdf = nothing
<run the report, etc>

If you are use your Invoices query elsewhere, use a query that is unique to this reporting routine.

I took your suggestion, seems to work will know more tomorrow after end-user test.
Thanks.