Loop through records not working

[Joan]

Hi,
The code that I have written to loop through Invoice records and print them
out one by one is not working as it should. My code is only printing the
first Invoice record in the range and not the others. Below is my code.
Can someone tell me where my problem is?


Private Sub cmdPrint_Click()
'On Error GoTo Err_cmdPrint_Click
Dim DB As Database
Dim rstInvoices As DAO.Recordset
Dim InvoiceCriteria As String
............
Case 2 'Print Invoice Number range.

'Print range of invoices' Invoice Statement
If Forms![frmPrintReportsDialog2]![ckInvoice] = -1 Then
Report = "rptInvoiceStatement"
Set DB = CurrentDb
Set rstInvoices = DB.OpenRecordset("SELECT * FROM Invoices
WHERE (Invoices.[Invoice Number] Between " & Forms!
frmPrintReportsDialog2!FromInvoice & " And " &
Forms!frmPrintReportsDialog2!ToInvoice & ") And (Invoices.Type = 'INV')",
dbOpenDynaset)

Do While Not rstInvoices.EOF
InvoiceCriteria = rstInvoices("[Invoice Number]")
rstInvoices.Edit
If ReportDest = acPreview Then
DoCmd.OpenReport Report, ReportDest, ,
"[Invoices].[Invoice Number] = " & InvoiceCriteria
Else 'if choose to print, open report in acPreview,
otherwise PrintOut prints (ISCopies) copies of dialog form and not report.
ISCopies = Me!txtISCopies
DoCmd.OpenReport Report, acViewPreview, ,
"[Invoices].[Invoice Number] =" & InvoiceCriteria
DoCmd.PrintOut , , , , ISCopies
End If
rstInvoices.Update
rstInvoices.MoveNext
Loop
End If
rstInvoices.Close


To move to the next record in the record set, don't I have to first Update
the recordset? Or do I have something out of order? Any help with this
would be appreciated.

Joan

 

[Marshall Barton]

The code that I have written to loop through Invoice records and print them
out one by one is not working as it should. My code is only printing the
first Invoice record in the range and not the others. Below is my code.
Can someone tell me where my problem is?


Private Sub cmdPrint_Click()
'On Error GoTo Err_cmdPrint_Click
Dim DB As Database
Dim rstInvoices As DAO.Recordset
Dim InvoiceCriteria As String
...........
Case 2 'Print Invoice Number range.

'Print range of invoices' Invoice Statement
If Forms![frmPrintReportsDialog2]![ckInvoice] = -1 Then
Report = "rptInvoiceStatement"
Set DB = CurrentDb
Set rstInvoices = DB.OpenRecordset("SELECT * FROM Invoices
WHERE (Invoices.[Invoice Number] Between " & Forms!
frmPrintReportsDialog2!FromInvoice & " And " &
Forms!frmPrintReportsDialog2!ToInvoice & ") And (Invoices.Type = 'INV')",
dbOpenDynaset)

Do While Not rstInvoices.EOF
InvoiceCriteria = rstInvoices("[Invoice Number]")
rstInvoices.Edit
If ReportDest = acPreview Then
DoCmd.OpenReport Report, ReportDest, ,
"[Invoices].[Invoice Number] = " & InvoiceCriteria
Else 'if choose to print, open report in acPreview,
otherwise PrintOut prints (ISCopies) copies of dialog form and not report.
ISCopies = Me!txtISCopies
DoCmd.OpenReport Report, acViewPreview, ,
"[Invoices].[Invoice Number] =" & InvoiceCriteria
DoCmd.PrintOut , , , , ISCopies
End If
rstInvoices.Update
rstInvoices.MoveNext
Loop
End If
rstInvoices.Close


To move to the next record in the record set, don't I have to first Update
the recordset?

What you have looks like a valid loop. The problem is that
it doesn't wait for the report to close before opening it
again for the next invoice. In other words, if you try to
open a report that is already open, it just gives the focus
to the one that's already open.

There are three ways to deal with this. One is to open
mutiple instances of the same object using Set New, but it
is rather complex to manage having all those copes of the
report on the screen at the same time.

Another way that is rather crude is add code to what you
have so that your loop waits for the current instance of the
report to close before opening it again (use the IsLoaded
function and DoEvents in a loop).

If at all possible, I strongly prefer to print a single
report that contains all the desired invoices. It may
require you to change a few things about how the report
handles headers/footers, grouping and page numbers, but it
is so much better in the end that I rarely ever consider any
other approach.

 

[Joan]

Marsh,
You're right. I finally printed a single report that prints all of the
invoices and this has worked much smoother.

Joan

The code that I have written to loop through Invoice records and print them
out one by one is not working as it should. My code is only printing the
first Invoice record in the range and not the others. Below is my code.
Can someone tell me where my problem is?


Private Sub cmdPrint_Click()
'On Error GoTo Err_cmdPrint_Click
Dim DB As Database
Dim rstInvoices As DAO.Recordset
Dim InvoiceCriteria As String
...........
Case 2 'Print Invoice Number range.

'Print range of invoices' Invoice Statement
If Forms![frmPrintReportsDialog2]![ckInvoice] = -1 Then
Report = "rptInvoiceStatement"
Set DB = CurrentDb
Set rstInvoices = DB.OpenRecordset("SELECT * FROM Invoices
WHERE (Invoices.[Invoice Number] Between " & Forms!
frmPrintReportsDialog2!FromInvoice & " And " &
Forms!frmPrintReportsDialog2!ToInvoice & ") And (Invoices.Type = 'INV')",
dbOpenDynaset)

Do While Not rstInvoices.EOF
InvoiceCriteria = rstInvoices("[Invoice Number]")
rstInvoices.Edit
If ReportDest = acPreview Then
DoCmd.OpenReport Report, ReportDest, ,
"[Invoices].[Invoice Number] = " & InvoiceCriteria
Else 'if choose to print, open report in acPreview,
otherwise PrintOut prints (ISCopies) copies of dialog form and not report.
ISCopies = Me!txtISCopies
DoCmd.OpenReport Report, acViewPreview, ,
"[Invoices].[Invoice Number] =" & InvoiceCriteria
DoCmd.PrintOut , , , , ISCopies
End If
rstInvoices.Update
rstInvoices.MoveNext
Loop
End If
rstInvoices.Close


To move to the next record in the record set, don't I have to first Update
the recordset?

What you have looks like a valid loop. The problem is that
it doesn't wait for the report to close before opening it
again for the next invoice. In other words, if you try to
open a report that is already open, it just gives the focus
to the one that's already open.

There are three ways to deal with this. One is to open
mutiple instances of the same object using Set New, but it
is rather complex to manage having all those copes of the
report on the screen at the same time.

Another way that is rather crude is add code to what you
have so that your loop waits for the current instance of the
report to close before opening it again (use the IsLoaded
function and DoEvents in a loop).

If at all possible, I strongly prefer to print a single
report that contains all the desired invoices. It may
require you to change a few things about how the report
handles headers/footers, grouping and page numbers, but it
is so much better in the end that I rarely ever consider any
other approach.