Access code fails to pick up query parameter

Question

I am trying to revive an old MS Access code I had successfully used a few years ago to generate individual letters and reports for student participants in a scientific conference contest. Now, it all seems to be working except it it apparently is failing to capture the unique names in the query that I want the reports generated for (so I can e-mail them out). Been fighting with this the whole weekend. I assume I am missing something(s) that are obvious. Can anyone help me find the error(s)?

Thank you!

Public Sub ContestantLtr()
    Dim myrs As Recordset
    Dim myPDF, myStmt As String
    Dim LtrName As String

    ' Open a record set with a list of Competitor ID numbers to print
    Set myrs = CurrentDb.OpenRecordset("qryContestantLetter", dbOpenSnapshot)
    myStmt = "SELECT [LtrName] FROM [qryContestantLetter]"
    
    ' For each competitor, print as .pdf
    Do Until myrs.EOF

          ' Open the report with the proper where condition
        DoCmd.OpenReport "repContestantLetter", acViewPreview, , "myStmt = " & myrs!LtrName
        
        ' Set the output path of your PDF file
        myPDF = "W:\H_Rescue\Rangeland\SRM\National\National Conferences\Graduate Presentation Papers\2019\ContestantLetters\Test\" & Format(myrs.Fields("LtrName")) & "_2019 SRM Grad Presentation Contest Letter.pdf"

        ' Generate the output in pdf
        DoCmd.OutputTo acOutputReport, "repContestantLetter", acFormatPDF, myPDF

        DoCmd.Close ' Close the report
        myrs.MoveNext ' read next

    Loop

    ' some cleanup
    myrs.Close
    Set myrs = Nothing

End Sub

I was expecting this code to generate individual reports as PDFs from the database for individual students in the conference. Got a missing parameter error instead.

Why do you use Format() function on LtrName field? You do not provide any input to the Format argument. This accomplishes nothing. — June7, Jan 23 '23 at 08:02
If you want this code to function consistently, I strongly recommend [actually using parameters](https://stackoverflow.com/q/49509615/7296893), not concatenating strings. — Erik A, Jan 23 '23 at 09:14

June7 · Answer 1 · 2023-01-23T08:06:35.653

Not sure what you think this line should do but it does not serve any purpose in the procedure.
myStmt = "SELECT [LtrName] FROM [qryContestantLetter]"
Not only does it set variable to an SQL string but then you enclose variable within quotes resulting in WHERE CONDITION criteria like:
myStmt = somevalue

The WHERE CONDITION needs a field reference. If LtrName is a text field, then need apostrophe delimiters.
"LtrName = '" & myrs!LtrName & "'"

Probably would be better to filter with a numeric ID instead of text. Number fields do not require delimiters for parameters.

score 0 · Accepted Answer · answered Jan 23 '23 at 14:15

0

Try to replace the line with this. The quotes are needed when it’s a text field.

myStmt = ‘" & myrs!LtrName & “‘“

answered Jan 23 '23 at 14:15

Daniel Sanders

14
1

1

Your quotes are not valid. This `‘` is not the same as this `'`. Avoid using word - use a plain text editor. – Kostas K. Jan 23 '23 at 14:38

score 0 · Answer 3 · edited Jan 24 '23 at 10:09

My genuine thanks to everyone who pointed out errors and suggested corrections. That got me back on the right track. Working code below. I was trying to refresh my memory from the last code I had (2019) which I thought was working (but apparently wasn't the final code I had actually used). Have several new projects I want to apply it to, but needed to get it into proper working form. Hopefully I can proceed successfully with this form. Now to see if I can get it to email the reports out...

Public Sub ContestantLtr()
    Dim myrs As Recordset
    Dim myPDF, myStmt As String
    Dim LtrName As String

    ' Open a record set with a list of Competitor ID numbers to print
    Set myrs = CurrentDb.OpenRecordset("qryContestantLetter", dbOpenSnapshot)
        
    ' For each competitor, print as .pdf
    Do Until myrs.EOF

          ' Open the report with the proper where condition
        DoCmd.OpenReport "repContestantLetter", acViewPreview, , "LtrName = '" & myrs!LtrName & "'"
        
        ' Set the output path of your PDF file
        myPDF = "W:\H_Rescue\Rangeland\SRM\National\National Conferences\Graduate Presentation Papers\2019\ContestantLetters\Test\" & Format(myrs.Fields("LtrName")) & "_2019 SRM Grad Presentation Contest Letter.pdf"

        ' Generate the output in pdf
        DoCmd.OutputTo acOutputReport, "repContestantLetter", acFormatPDF, myPDF

        DoCmd.Close ' Close the report
        myrs.MoveNext ' read next

    Loop

    ' some cleanup
    myrs.Close
    Set myrs = Nothing

End Sub

Access code fails to pick up query parameter

3 Answers3