Lebans PDF Conversion

Lebans PDF code is in use in thousands of Access apps around the world, and I daresay it's about as stable as code like this comes.

Did you make any changes to it? Can you show the areas where you changed these things?

I have changed absolutely nothing - i left the module in its original state.
I have 3 buttons on a form which create three seperate pdf files - all work individually but if i press one directly after another without closing the database and reopening it - i get the error.
It seems to do everything except save the file so i can't pick it up.
I assume that i have to reset something (object?) but i'm still at novice at all this so i really don't know.
Button codes are below.
Thanks


Private Sub CMEmail_Click()
On Error GoTo Err_CMEmail_Click
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("qry_CustomerConcernsPDF")
qdf.SQL = "SELECT tbl_CustomerConcernsCopy.* FROM tbl_CustomerConcernsCopy " _
& "WHERE (tbl_CustomerConcernsCopy.[Our Ref] In (" & Me.txtOurRef & "))"
Set qdf = Nothing
With DoCmd
    .SetWarnings False
    .OpenQuery "qry_CustomerConcernsPDF"
    .Close acQuery, "qry_CustomerConcernsPDF", acSaveYes
    .SetWarnings True
End With
Dim blRet As Boolean
blRet = ConvertReportToPDF("rep_ComplaintFormPDF", vbNullString, _
"rep_ComplaintFormPDF.pdf", False, True, 150, "", "", 0, 0, 0)
Call OpenCMEmail(Me.txtOurRef, Me.Customer, Me.Nature_of_Complaint)
Exit_CMEmail_Click:
    Exit Sub
Err_CMEmail_Click:
    MsgBox Err.Description
    Resume Exit_CMEmail_Click  
End Sub

Private Sub CUEmail_Click()
On Error GoTo Err_CUEmail_Click
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("qry_CustomerConcernsPDF")
qdf.SQL = "SELECT tbl_CustomerConcernsCopy.* FROM tbl_CustomerConcernsCopy " _
& "WHERE (tbl_CustomerConcernsCopy.[Our Ref] In (" & Me.txtOurRef & "))"
Set qdf = Nothing
With DoCmd
    .SetWarnings False
    .OpenQuery "qry_CustomerConcernsPDF"
    .Close acQuery, "qry_CustomerConcernsPDF", acSaveYes
    .SetWarnings True
End With
Dim blRet As Boolean
blRet = ConvertReportToPDF("rep_ComplaintFormPDF", vbNullString, _
"rep_ComplaintFormPDF.pdf", False, True, 150, "", "", 0, 0, 0)
Call OpenCUEmail(Me.txtOurRef, Me.Customer, Me.Nature_of_Complaint)
Exit_CUEmail_Click:
    Exit Sub
Err_CUEmail_Click:
    MsgBox Err.Description
    Resume Exit_CUEmail_Click  
End Sub

Private Sub CNEmail_Click()
On Error GoTo Err_CNEmail_Click
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("qry_CustomerConcernNotificationPDF")
qdf.SQL = "SELECT tbl_CustomerConcernsCopy.* FROM tbl_CustomerConcernsCopy " _
& "WHERE (tbl_CustomerConcernsCopy.[Our Ref] In (" & Me.txtOurRef & "))"
Set qdf = Nothing
With DoCmd
    .SetWarnings False
    .OpenQuery "qry_CustomerConcernNotificationPDF"
    .Close acQuery, "qry_CustomerConcernNotificationPDF", acSaveYes
    .SetWarnings True
End With
blRet = ConvertReportToPDF("rep_ConcernNotification", vbNullString, _
"rep_ConcernNotification.pdf", False, True, 150, "", "", 0, 0, 0)
Call OpenCNEmail(Me.txtOurRef, Me.Customer, Me.Nature_of_Complaint)
Exit_CNEmail_Click:
    Exit Sub
Err_CNEmail_Click:
    MsgBox Err.Description
    Resume Exit_CNEmail_Click  
End Sub

Honesty i don't know but its not included in Lebans orignal script.
Actually all the PDF filesid are created ok, so each block of code works as an indivual block.
Problem is when i try to run one block of code directly after another - the first pdf file is created ok but the others are not saved in the target directory (in this case there is only one other as two run the same report).
I get the error notice posted above - if i close access and reopen i can run any one of the code blocks without problem, i jut can't run a second one.

Thanks

Exactly where does that error occur?
Module / Function / Line of code ?

mx

Lebans codes does not error.
The problem is that the file is not saved to the target directory and i get an error when i try to pick it up.
In Lebans code the DoCmd creates the fiile (i see it flash on screen) but it does not stay on screen or save like it does first  i run the code.

Module modReportToPDF
Function ConvertReportToPDF
Line DoCmd.OutputTo acOutputReport, RptName, "SnapshotFormat(*.snp)", _
       strPathandFileName

It seems to be this line that does not rn second time around

ShellExecuteA Application.hWndAccessApp, "open", sOutFile, vbNullString, vbNullString, 1

Hey ... idea.

Maybe .... if you work in Shell and Wait found here:

http://www.mvps.org/access/api/api0004.htm

... you might be able to resolve this.  I use this in my automated Compact & Repair routine, which runs on 25 mdb's nightly (C&R executes on local workstation).  So, I can execute a C&R and mdb1, and Shell & Wait lets me know what that is *done*, and move on to mdb2 ... and so on.  This way, it's not trying to do all C&R's at one time.  This works VERY well ...

mx

What should i do with it?

I thought this might just be a problem that a temporary file is stored somewhere and i am trying to store another one in the same place?

Well, now I'm not sure.  ShellWait is a replacement for Shell.  I'm not sure what ShellExecuteA is ... sorry.

mx

ShellExecute is just the API implementation of the VBA Shell command (and there are other "flavors" of ShellExecute as well, in different libraries).

Absolutely correct
Strange thing is it appears to work but saves files in various directories everytime the code is run so its not possible to pick up a changed file (of the same name)  after the first run
Specifying the directory solved this
Thanks