Solved Loop recordset and print reports (1 Viewer)

abrody

New member
Joined
Apr 26, 2023
Messages
3
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
 
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.
 
Likely your PT_NUM is text and not a number, so surround that with single quotes.
 
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.
 

Users who are viewing this thread

Back
Top Bottom