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
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