UtterAccess Discussion Forums > ConvertReporttoPDF vba question

Full Version: ConvertReporttoPDF vba question

UtterAccess Discussion Forums > Microsoft® Access > Access Reports

efdawson

Jun 14 2010, 01:03 PM

I have a couple of questions regarding Stephen Lebans's great ConvertReporttoPDF code. I have it working, but I suspect I did something wrong. This routine should print a seperate PDF file(containing any number of copies of the report - one for each course attended) for each individual selected in the underlying query. Maybe seven people are selected and then seven pdf files are produced. Everything runs OK, but it looks to me like each individual report is being created numerous times. The Acrobat window opens up on creation of the first file and then there is an Acrobat error message stating the file is already in use. Did I do something wrong with my loop?

CODE

Private Sub Command15_Click()

Dim rs As Recordset
Dim blRet As Boolean

Set rs = CurrentDb.OpenRecordset("qryclecertificate")
With rs
.MoveLast
.MoveFirst


Do While Not rs.EOF
Forms!frmselectjudgesforprinting.txtvalue = rs!JUDGE

    blRet = ConvertReportToPDF("rptclecertificatepdf", vbNullString, _
                               "C:\access\cle\" & rs!LastName & "cle.pdf", False, True, 150, "", "", 0, 0, 0)
    DoEvents


    rs.MoveNext
  Loop

End With
rs.Close
Set rs = Nothing

Command15_Click_Exit:

Also, is there anyway to suppress the pdf file and Acrobat from opening after each file is created? That would be great, because I expect creating scores of seperate PDF files.

Thanks,
Eric

dannyseager

Jun 14 2010, 01:06 PM

See my posts here : http://www.utteraccess.com/forum/Print-ind...l&hl=lebans

efdawson

Jun 14 2010, 02:18 PM

Danny-Thanks for the reply. Unfortunately, access keeps crashing each time I actually try and execute your sample code in your db.

But I have looked over your code and modified mine similarly best I could. When I run the event, I keep getting an Error Code:3265, item not found in this collection. I have doublechecked all my relevant field names and they all do exist in the recordsource. I'm not real familar with the Call ChangeQuery. Maybe that is it? But regardless, my previous code does seem to work, but I think I might have my loop somewhat incorrect. I have a filter on the report, and there should be a new pdf for each judge# in my case. All that works, but I think the same file keeps getting created however many times. Anyone see anything wrong the vba there?

Plus, any ideas on suppressing Acrobat from opening on the creation of each pdf?

TIA,
Eric

efdawson

Jun 14 2010, 02:38 PM

Danny-Actually if I remove the changequery line. It works OK. Here is my modified code:

CODE

Dim strRecSource As String  'Change this to match your Table or query Name
Dim strRptName As String  'Change this to match Your Report Name
Dim blRet As Boolean

strRecSource = "qryclecertificate"
strRptName = "rptclecertificatepdf"


Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open strRecSource, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rs.RecordCount = 0 Then
    MsgBox "There are no records to print"
    Exit Sub
Else
End If

Do While Not rs.EOF
Forms!frmselectjudgesforprinting.txtvalue = rs!JUDGE#

    blRet = ConvertReportToPDF(strRptName, vbNullString, "C:\access\cle\" & rs!LastName & "cle.pdf", False, True, 300, "", "", 0, 0, 0)
rs.MoveNext
Loop




rs.Close
Set rs = Nothing
MsgBox "All Reports Exported"

Exit Sub
ErrTrap:

MsgBox "An error Occured" & vbCrLf & "Error Code : " & Err.Number & vbCrLf & "Error Description : " & Err.Description

End Sub

I have a filter on the report tied to JUDGE#. Everything works fine in this case. Are you able to suppress Acrobat from opening?

efdawson

Jun 14 2010, 02:39 PM

one more thing...the reason for this line:

Forms!frmselectjudgesforprinting.txtvalue = rs!JUDGE#

is because the report filters on this.

dannyseager

Jun 14 2010, 06:13 PM

You are telling it to open the pdf with the highlighted argument

blRet = ConvertReportToPDF(strRptName, vbNullString, "C:\access\cle\" & rs!LastName & "cle.pdf", False, True, 300, "", "", 0, 0, 0)

change that to false

efdawson

Jun 15 2010, 08:38 AM

Danny-Wow, great. That is an easy fix. Now, I just need to find how to automate emailing these pdfs via lotus notes from access!

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.