• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Lebans PDF Conversion

Has anyone working experience of this code?
The code works perfectly by i am have problems using the code nire than once without closing and reoping the access application.
The problem is that it does not create the file second time around.
You get an error An Unknown Error occured.Attachmenyts.Object
I think i have to kill soemthing to use the code over and over again but i don't know how.

Any help would be welcome.

0
SweetingA
Asked:
SweetingA
  • 6
  • 4
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
 
SweetingAAuthor Commented:
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

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
This:

ConvertReportToPDF("rep_ComplaintFormPDF", vbNullString, "rep_ComplaintFormPDF.pdf", False, True, 150, "", "", 0, 0, 0)

Aren't you missing a parameter ... sDirPath ... after .PDF and before False ?

For example, when I used this I have:

all ConvertReportToPDF(Me.lstRpts.Column(3),   vbNullString, sFileName & ".pdf", sDirPath, True, False, 0, "", "", 0, 0)                                                                                                             ^^^^^^^^^

?

mx
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
SweetingAAuthor Commented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Exactly where does that error occur?
Module / Function / Line of code ?

mx
0
 
SweetingAAuthor Commented:
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
0
 
SweetingAAuthor Commented:
It seems to be this line that does not rn second time around

ShellExecuteA Application.hWndAccessApp, "open", sOutFile, vbNullString, vbNullString, 1
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
0
 
SweetingAAuthor Commented:
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?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, now I'm not sure.  ShellWait is a replacement for Shell.  I'm not sure what ShellExecuteA is ... sorry.

mx

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
ShellExecute is just the API implementation of the VBA Shell command (and there are other "flavors" of ShellExecute as well, in different libraries).
0
 
SweetingAAuthor Commented:
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
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now