Solved

Lebans PDF Conversion

Posted on 2010-09-14
12
421 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 - Microsoft MVP, Access and Data Platform) 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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