Solved

Lebans PDF Conversion

Posted on 2010-09-14
12
416 Views
Last Modified: 2012-05-10
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
Comment
Question by:SweetingA
  • 6
  • 4
  • 2
12 Comments
 
LVL 84
ID: 33675905
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
 

Author Comment

by:SweetingA
ID: 33676160
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 33676723
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
 

Author Comment

by:SweetingA
ID: 33677901
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
 
LVL 75
ID: 33677912
Exactly where does that error occur?
Module / Function / Line of code ?

mx
0
 

Author Comment

by:SweetingA
ID: 33678057
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:SweetingA
ID: 33678220
It seems to be this line that does not rn second time around

ShellExecuteA Application.hWndAccessApp, "open", sOutFile, vbNullString, vbNullString, 1
0
 
LVL 75
ID: 33678245
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
 

Author Comment

by:SweetingA
ID: 33678317
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
 
LVL 75
ID: 33678869
Well, now I'm not sure.  ShellWait is a replacement for Shell.  I'm not sure what ShellExecuteA is ... sorry.

mx

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

Author Closing Comment

by:SweetingA
ID: 33720553
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now