Record Printing Loop

[BrianM2]

I am setting up a button on a form to print a group of of records which have been filtered by the form's underlying query. For test purposes the number of records has been limited to two. The report comprises two pages. The intention is that the procedure will call the report, print the first record, close the report, loop to the next record, recall the report, print the second record and stop having reached the end of the file. Longer term the idea is to include within the "Do" loop the selection of specific reports to print different record types which are stored in a common table - but I need to get this one working first.

The code used is shown below. I have tried three versions of the reporting section two of which are commented out.

Private Sub cmdElectInspBulkPrint_Click()
On Error GoTo Err_cmdElectInspBulkPrint_Click

Dim rst As Recordset
Dim stDocName As String

Set rst = Me.RecordsetClone
rst.MoveFirst
Do
stDocName = "rptElecInspEN"

'Version 1
DoCmd.OpenReport stDocName, acViewNormal, , , , "[ID]=" & Me![ID]
DoCmd.PrintOut acPages, 1, 2, , , -1

'Version 2
'DoCmd.OpenReport stDocName, acViewNormal

'Version 3
'DoCmd.SelectObject acReport, stDocName, True
'DoCmd.PrintOut acPrintAll, 1, 2



DoCmd.Close acReport, stDocName, acSaveNo
Debug.Print rst!Equipment

rst.MoveNext

Loop Until rst.EOF

Exit_cmdElectInspBulkPrint_Click:
Exit Sub

Err_cmdElectInspBulkPrint_Click:
MsgBox Err.Description
Resume Exit_cmdElectInspBulkPrint_Click

End Sub

Version 1 - Believes the report is 4 pages long, loops after printing the two records on the 4 pages plus an additional 2 page print of the form itself and then gives me another set of 6 pages.

Version 2 - Has done best but also believes it has a 4 page report to print. However it has provided a 4 page report with record 1 on pages 1 & 2 and record 2 on pages 3 & 4. It then loops and repeats this.

Version 3 - Also believes it has a 4 page report but has given me two prints of the first record only.

Has anyone got any ideas?

 

[HiTechCoach]

I am setting up a button on a form to print a group of of records which have been filtered by the form's underlying query. For test purposes the number of records has been limited to two. The report comprises two pages. The intention is that the procedure will call the report, print the first record, close the report, loop to the next record, recall the report, print the second record and stop having reached the end of the file. Longer term the idea is to include within the "Do" loop the selection of specific reports to print different record types which are stored in a common table - but I need to get this one working first.

The code used is shown below. I have tried three versions of the reporting section two of which are commented out.

Private Sub cmdElectInspBulkPrint_Click()
On Error GoTo Err_cmdElectInspBulkPrint_Click

Dim rst As Recordset
Dim stDocName As String

Set rst = Me.RecordsetClone
rst.MoveFirst
Do
stDocName = "rptElecInspEN"

'Version 1
DoCmd.OpenReport stDocName, acViewNormal, , , , "[ID]=" & Me![ID]
DoCmd.PrintOut acPages, 1, 2, , , -1

'Version 2
'DoCmd.OpenReport stDocName, acViewNormal

'Version 3
'DoCmd.SelectObject acReport, stDocName, True
'DoCmd.PrintOut acPrintAll, 1, 2



DoCmd.Close acReport, stDocName, acSaveNo
Debug.Print rst!Equipment

rst.MoveNext

Loop Until rst.EOF

Exit_cmdElectInspBulkPrint_Click:
Exit Sub

Err_cmdElectInspBulkPrint_Click:
MsgBox Err.Description
Resume Exit_cmdElectInspBulkPrint_Click

End Sub

Version 1 - Believes the report is 4 pages long, loops after printing the two records on the 4 pages plus an additional 2 page print of the form itself and then gives me another set of 6 pages.

Version 2 - Has done best but also believes it has a 4 page report to print. However it has provided a 4 page report with record 1 on pages 1 & 2 and record 2 on pages 3 & 4. It then loops and repeats this.

Version 3 - Also believes it has a 4 page report but has given me two prints of the first record only.

Has anyone got any ideas?

Try Something like:

Private Sub cmdElectInspBulkPrint_Click()
On Error GoTo Err_cmdElectInspBulkPrint_Click

Dim rst As Recordset
Dim stDocName As String

Set rst = Me.RecordsetClone

rst.MoveFirst

stDocName = "rptElecInspEN"

Do

    DoCmd.OpenReport stDocName, acViewNormal, , "[ID]=" & rst![ID]

    rst.MoveNext

Loop Until rst.EOF

Exit_cmdElectInspBulkPrint_Click:
Exit Sub

Err_cmdElectInspBulkPrint_Click:
MsgBox Err.Description
Resume Exit_cmdElectInspBulkPrint_Click

End Sub

This assumes the report rptElecInspEN will print for ever "[ID]". This way the WHERE can be passed.

 

[Kiwiman]

Howzit

This is what I use. This loops through my customers, and prints their master data on individual reports.

Private Sub cmdPrntAll_Click()

' Declare variables
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Dim strDocName As String
Dim strWhere As String

' Set selection string for recordset
strSQL = "Select CustomerID From tblCustomers"

' What report to print
strDocName = "rptCustomers"

' Set recordset
Set rs = CurrentDb.OpenRecordset(strSQL)

' Move to last record then back to first record to get accurate count
rs.MoveLast
rs.MoveFirst

' For all records print the customer record
For i = 1 To rs.RecordCount
    
    ' Limit the report to just the current record
    strWhere = "[CustomerID] = " & rs!CustomerID
    
    ' Print the record
    DoCmd.OpenReport strDocName, acViewNormal, , strWhere
    
    rs.MoveNext

Next i
' Close and set the recordset to nothing
rs.Close
Set rs = Nothing

End Sub

 

[BrianM2]

Thank you to both HiTechCoach and Kiwiman.

Two quite different approaches and this makes it very interesting.

We have people staying for the weekend so time is limited until Monday. So I'll have a look at both a little later on and feed back to you.

Best Regards - Brian

 

[CSPGG]

Dear Kiwiman,

I tried your code on a query from where i want to obtain de recordset.

Everything works fine, except a Enter Parameter Value dialog box that apears in every line of the result query.

If i leave the dialog box blank, the report will be without any data from the query, but if i insert the id of the field [ID_MacroIniciativa], then the report will be correct.

Can you, or anyone, help me in this matter?

Thanks in advance.
PG

This a copy of the code that i have in VB:

Private Sub cmdPrntAll_Click()
' Declare variables
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Dim strDocName As String
Dim strWhere As String

strSQL = "Select ID_MacroIniciativa From qry_overview_iniciativas1"
strDocName = "qry_overview"

Set rs = CurrentDb.OpenRecordset(strSQL)

rs.MoveLast
rs.MoveFirst

For i = 1 To rs.RecordCount


strWhere = "[ID_MacroIniciativa] = " & rs!ID_MacroIniciativa


DoCmd.OpenReport strDocName, acViewNormal, , strWhere

rs.MoveNext
Next i

rs.Close
Set rs = Nothing
End Sub