Function PrintReports - "Too Few Parameters" ERROR

[LDW]

Hello! I have one report I'm trying to run through VBA that will output 500 territories in own PDF (so 500 PDFs when done). Help needed with error saying "Too Few Parameters". Here is what I have for my VBA...any help you can offer is GREATLY appreciated!

Function PrintReports()

Dim sql As String
Dim db As Database
Dim rs As Recordset

sql = "SELECT TERRITORY, CODE, LAST_NAME FROM TERR_CODES"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
DoCmd.OpenReport "Q2_Reports", acViewPreview, , "[TERRITORY]='" & rs![TERRITORY] & "'"
DoCmd.OutputTo acOutputReport, "Q2_Reports", "PDFFormat(*.pdf)", "C:\Desktop\PrintFiles\" & rs![TERRITORY] & " - " & rs!

 & " - " & rs![LAST_NAME] & ".pdf", False
DoCmd.Close acReport, "Q2_Reports"
DoEvents
rs.MoveNext
Loop
 
rs.Close
Set rs = Nothing
Set db = Nothing
 
MsgBox ("Reports have been printed.")
 
End Function

 

[pbaldy]

On the Set rs line? My guess would be something is misspelled in the SQL. Perhaps there are really spaces instead of underscores?

 

[LDW]

SOLVED: Function PrintReports - "Too Few Parameters" ERROR

OMG, I can't believe I overlooked that when reviewing my code. One of the underscores is actually a dash. Thank you so much!

 

[pbaldy]

Happy to help!

 

[LDW]

If I wanted to add the date to my pdf name and pull that from another table, how could I add this to my code? It is a table called "PC" and the date is "M_Date". Thanks again!

Function PrintReports()

Dim sql As String
Dim db As Database
Dim rs As Recordset

sql = "SELECT TERRITORY, CODE, LAST_NAME FROM TERR-CODES"
Set db = CurrentDb
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Do Until rs.EOF
DoCmd.OpenReport "Q2_Reports", acViewPreview, , "[TERRITORY]='" & rs![TERRITORY] & "'"
DoCmd.OutputTo acOutputReport, "Q2_Reports", "PDFFormat(*.pdf)", "C:\Desktop\PrintFiles\" & rs![TERRITORY] & " - " & rs!

 & " - " & rs![LAST_NAME] & ".pdf", False
DoCmd.Close acReport, "Q2_Reports"
DoEvents
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

MsgBox ("Reports have been printed.")

End Function

 

[pbaldy]

This type of thing:

Format(Date, "yyyymmdd")

You can use a DLookup() to get the date from the table if it's not the current date.

 

[LDW]

Thanks, pbaldy! Was pulling the current date last week, but got the DLookup working today in my VBA code. This was awesome...thanks so much for the help!

 

[pbaldy]

Happy to help!