Solved Loop recordset and print reports (1 Viewer)

abrody

New member
Local time
Today, 16:29
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
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 13:29
Joined
Aug 30, 2003
Messages
36,112
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

Enthusiastic Amateur
Local time
Today, 21:29
Joined
Sep 21, 2011
Messages
13,890
Likely your PT_NUM is text and not a number, so surround that with single quotes.
 

abrody

New member
Local time
Today, 16:29
Joined
Apr 26, 2023
Messages
3
Your suggestions are on target. Thank you both.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:29
Joined
Feb 19, 2002
Messages
42,741
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

Top Bottom