Access VBA按指定字段生成单记录PDF：代码输出值不符预期

Looks like your code has a couple of syntax errors in the report filter and field reference that's throwing off your expected output. Let's break down the issues and fix them step by step:

Key Problems in Your Original Code #

• Empty field reference: myrs.Fields("").value has no field name specified—you clearly intended to target the "Code1" field here.
• Malformed filter string: The WhereCondition in DoCmd.OpenReport was incorrectly formatted, so the report wasn't filtering to the correct record for each iteration.
• Unspecific close action: DoCmd.Close without parameters might accidentally close the wrong object; it's safer to explicitly target the report you opened.
• Incomplete variable declaration: In VBA, Dim myPDF, myStmt As String only sets myStmt as a String—myPDF would default to a Variant. We'll fix that to ensure type consistency.

Corrected Code #

Private Sub Command_PDF_Click()
    Dim myrs As Recordset
    Dim myPDF As String, myStmt As String ' Explicitly declare both variables as String
    myStmt = "Select distinct Code1 from Query_Certificate_Eng"
    Set myrs = CurrentDb.OpenRecordset(myStmt)
    
    Do Until myrs.EOF
        ' Build the PDF filename with properly formatted Code1
        myPDF = "C:\Users\93167\Desktop\Output Certificate\" & Format(myrs.Fields("Code1"), "0000000000000") & ".pdf"
        
        ' Open the report with the correct filter for the current Code1
        ' Use single quotes if Code1 is a text field; omit them if it's numeric
        DoCmd.OpenReport "Certificate_Eng", acViewPreview, , "Code1 = '" & myrs.Fields("Code1").Value & "'"
        
        ' Export the filtered report to PDF
        DoCmd.OutputTo objectType:=acOutputReport, _
                      objectName:="Certificate_Eng", _
                      outputFormat:=acFormatPDF, _
                      outputFile:=myPDF, _
                      outputQuality:=acExportQualityPrint
        
        ' Explicitly close the report to avoid leftover windows
        DoCmd.Close acReport, "Certificate_Eng"
        
        myrs.MoveNext
    Loop
    
    myrs.Close
    Set myrs = Nothing
End Sub

Quick Extra Tips #

• Check folder existence: Ensure the output folder C:\Users\93167\Desktop\Output Certificate\ exists. If not, add MkDir "C:\Users\93167\Desktop\Output Certificate\" before the loop (wrap it in an error handler to avoid crashes if the folder already exists).
• Adjust filter for numeric Code1: If Code1 is a numeric field instead of text, remove the single quotes in the filter string so it reads: "Code1 = " & myrs.Fields("Code1").Value.

内容的提问来源于stack exchange，提问作者Kane Jolejole