Solved Loop recordset and print reports (1 Viewer)

[abrody]

Hello everyone,
I am not very good with loops. I have a select query called sel_tblTrimsheet-Trimsheet. In that query is a field called PT Num. The PT Num can repeat on two or more records in the query. I want to loop through those records and print a report called zTrimsheet for each PT Num.

Each report for each PT Num requires a Report Header and Page Header in the report setup. So I cannot alter the report. It is for this reason I want to use a loop.

Below is the VB code I have tried but I keep getting a "Run-time error 3464: Data type mismatch in criteria expression" error on this line:
DoCmd.OpenReport "zTrimsheet", acViewPreview, , "[PT Num] = " & rst![PT Num]

Please share your thoughts. Thank you in advance. Alex.

Function PrintTrimsheets()
Dim rst As Recordset
Dim db As Database
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT [sel_tblTrimsheet-Trimsheet].[PT Num] FROM [sel_tblTrimsheet-Trimsheet] GROUP BY [sel_tblTrimsheet-Trimsheet].[PT Num]"

Set rst = db.OpenRecordset(strSQL)

rst.MoveFirst

Do Until rst.EOF
DoCmd.OpenReport "zTrimsheet", acViewPreview, , "[PT Num] = " & rst![PT Num]
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
strSQL = ""

End Function

 

[pbaldy]

Try

"[PT Num] = '" & rst![PT Num] & "'"

But note if you keep opening the same report in preview, you'll only end up with one (I suspect the first). You probably want to print out or save to PDF with unique names.

 

[Gasman]

Likely your PT_NUM is text and not a number, so surround that with single quotes.

 

[abrody]

Your suggestions are on target. Thank you both.

 

[pbaldy]

No problem.

 

[Pat Hartman]

If you want ONE report with page breaks and headers for each group, use the report options. NO CODE IS REQUIRED.

If you want a SEPARATE report file for each group, only then will you need a loop.