Save to Multiple Reports to PDF and AutoName using DLookup

[Brilstern]

Got it working with the code below! Yay ME. Ok so I still have one issue though. In LN 23 i have the name of the PDF. Currently it is just the last name of the Marine. I want it to look like LN 11 "RANK & ' ' & LNAME & ', ' & FNAME". Any ideas

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strPath As String
Dim strFileName As String
Dim strReport As String
Dim strCurrentPath As String
Dim strmonth As String
Dim strPDFNAme As String

strPDFNAme = "RANK & ' ' & LNAME & ', ' & FNAME"
strmonth = Me.MonthSelect
strCurrentPath = Application.CurrentProject.Path
strSQL = "SELECT DISTINCT SSN, RANK, LNAME, FNAME FROM [TBLBAND MEMBERS]"
strPath = "" & strCurrentPath & "\" & strmonth & " ECR\"
strReport = "rptECR"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
MsgBox strPath
With rs
   If .RecordCount > 0 Then
      Do Until .EOF
         strFileName = .Fields!LName & ".pdf"
         DoCmd.OpenReport strReport, acViewPreview, , "[SSN] = '" & !SSN & "'", acHidden
         Call DoCmd.OutputTo(objecttype:=acOutputReport, objectname:=strReport, outputformat:=acFormatPDF, outputfile:=strPath & strFileName)
         DoEvents
         DoCmd.Close acReport, strReport
         .MoveNext
      Loop
   End If
End With

rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.Close acForm, "frmPDFSave"

Thank you both so much for the help, i am understanding it a little better now.

Sgt B

[NeoPa]

1. Empty string not necessary in line #15 :
   

   strPath = strCurrentPath & "\" & strmonth & " ECR\"

2. Line #19 not required after all working.
3. Lines #21 and #30 not required and misleading. Do Until .EOF handles this properly as an empty recordset returns .EOF and .BOF as True. There are some situations where .RecordCount is not set until after this point (or at all in some circumstances).
4. strPDFName and strFileName are both for the same purpose so one of them should be ditched.
5. Whichever is ditched line #11 is useless and can go.
6. Line #23 changes to :
   

   (whichever variable is used) = !RANK & " " & !LNAME & ", " & !FNAME.PDF

Otherwise all should be fine. Nice attempt by the way. Much closer to the mark and quite recognisable as to what it's trying to do.

[Brilstern]

Ok, when I do this it gives me error "Invalid or Unqualified Resource" compile error on LN 23. It highlights !Rank

Sgt B

[Brilstern]

NeverMind. I forgot to re add a folder in the path that I created this afternoon. simple mistake. Everything is all good!!

[NeoPa]

Stevan Bias:
Everything is all good!!

That's what I like to hear! Good for you :-)