Problem with module that creates and names pdf files

[Antigoon]

Problem is the following: The following module creates several pdf files and it would then have to name them according to department. The creation of the various pdfs will succeed but then he gives all of the files in the same name -> rpt_effectie_bezetting.pdf. This means to me that in the first If loop he never gets to the first Else. This database is converted from Office 97 to Office XP and worked fine in both - just in Access 2010 it is giving me difficulties.

Thanks for any help or suggestions.

Greetings from Antwerp, Stefan.

Private Sub cmdRapport_Click()
Dim dbs As Database, rst As Recordset
Dim strSQL As String
Dim m_datum As String

' Database-variabele instellen die naar de huidige database verwijst.
Set dbs = CurrentDb
strSQL = "SELECT * FROM tbl_ltst_toest"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
m_datum = rst.Fields("dat_ltst_berek_toest")
rst.Close
Set dbs = Nothing

ChangeToAcrobat
ChangePdfFileName "c:\test.pdf"
'HKEY_CURRENT_USER = &H80000001
If Me.k_afdruk_0_.Value = True Then
Dim teller As Integer
Dim text As String
For teller = 0 To lstCode.ListCount - 1
text = Trim(lstCode.Column(0, teller))
''SaveSetting &H80000001, "Software\Adobe\Acrobat PDFWriter", "PDFFileName", "c:\effectief\rpt_effectief_bezetting_" + m_datum + "_" + text + ".pdf"
''DoCmd.OpenReport "rpt_effectief_bezetting", acViewNormal, , "[tbl_basis_fromatie]![dienstcode] = '" & text & "'"
SetKeyValue "Software\Adobe\Acrobat PDFWriter", "PDFFilename", "c:\effectief\rpt_effectief_bezetting_" + Format(m_datum, "dd-mm-yyyy") + "_" + text + ".pdf", 1
DoCmd.OpenReport "rpt_effectief_bezetting", acViewNormal, , "[tbl_basis_fromatie]![dienstcode] = '" & text & "'"
Next teller
Else
If Me.keuze.Value <> " " Then
''SaveSetting &H80000001, "Software\Adobe\Acrobat PDFWriter", "PDFFileName", "c:\effectief\rpt_effectief_bezetting_" + Format(m_datum, "dd-mm-yyyy") + "_" + Me.lstCode.Value + ".pdf"
''DoCmd.OpenReport "rpt_effectief_bezetting", acViewNormal, , "[tbl_basis_fromatie]![dienstcode] = '" & Me.lstCode.Value & "'"
SetKeyValue "Software\Adobe\Acrobat PDFWriter", "PDFFilename", "c:\effectief\rpt_effectief_bezetting_" + Format(m_datum, "dd-mm-yyyy") + "_" + Me.lstCode.Value + ".pdf", 1
DoCmd.OpenReport "rpt_effectief_bezetting", acViewNormal, , "[tbl_basis_fromatie]![dienstcode] = '" & Me.lstCode.Value & "'"
Else
''SaveSetting &H80000001, "Software\Adobe\Acrobat PDFWriter", "PDFFileName", "c:\effectief\rpt_effectief_bezetting_" + m_datum + ".pdf"
''DoCmd.OpenReport "rpt_effectief_bezetting", acViewNormal
SetKeyValue "Software\Adobe\Acrobat PDFWriter", "PDFFilename", "c:\effectief\rpt_effectief_bezetting_" + Format(m_datum, "dd-mm-yyyy") + ".pdf", 1
DoCmd.OpenReport "rpt_effectief_bezetting", acViewNormal

End If
End If

'Call ResetDefaultPrinter
End Sub

 

[PNGBill]

With Access 2010 you can produce the report as a .pdf file.

eg

DoCmd.OutputTo acOutputReport, "RptStatementNewStyle", acFormatPDF, "W:\Attachments\" & FullName & " Loan " & LoanRef & " Loan Statement " & TeamMember & ".pdf", 0, , , acExportQualityPrint

FullName, LoanRef & TeamMember are all Dim'd and resolved earlier in the code.

This example will allways save the report in W:\Attachments\ but the name will differ.

RptStatementNewStyle is the Report Name.

 

[smig]

If you use Access prior to 2010 use Stephan Leban's code to convert to PDF
Should work under Access 2010 too

 

[PNGBill]

I had issues with Stephen Leban's code and 2010. Found it easier to use the built in .pdf feature.

 

[smig]

I have some small language issues with Stephan's code under AC2003 too, but at least I can export the report as PDF

I'm sure it's better to use the built in option under AC2010