Access code fails to pick up query parameter-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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

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