Solved

Lebans PDF Conversion

Posted on 2010-09-14
12
410 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Exactly where does that error occur?
Module / Function / Line of code ?

mx
0
 

Author Comment

by:SweetingA
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

ShellExecuteA Application.hWndAccessApp, "open", sOutFile, vbNullString, vbNullString, 1
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Well, now I'm not sure.  ShellWait is a replacement for Shell.  I'm not sure what ShellExecuteA is ... sorry.

mx

0
 
LVL 84
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 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

11 Experts available now in Live!

Get 1:1 Help Now