What's wrong with this code for Repeat Records in Report?

[Sandi Vogel]

I posted this at UtterAccess this morning, but got no answers. I'm hoping to
find some help here. Thanks!

I got this code from a MSoft KB to repeat records in a report. Here's how
it's supposed to work: From a form (frmHolidayParty), user inputs a number of
times to repeat records on report in a text box (TimesToRepeatRecord), and
then clicks the button to preview the report (which is based on a select
query). It appears to work correctly when the report is previewed:

Bob Smith
Bob Smith
Bob Smith
Kim Jones
Kim Jones
Kim Jones, etc.

However, when actually printed:

If the user input 1-3, it prints as expected.
If the user input 4-10, the first record will either print only once, or 3
times; although the print preview shows the correct # of records.

I'm baffled (and new at VBA). Any clues?

Thanks, Sandi

 

[Sandi Vogel]

Oh, I guess the code would help!:


Option Compare Database
Option Explicit

Dim intPrintCounter As Integer
Dim intNumberRepeats As Integer

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
'Note: intNumberRepeats and intPrintCounter are initialized
'in the report's OnOpen event.
If intPrintCounter < intNumberRepeats Then
intPrintCounter = intPrintCounter + 1
'Do not advance to the next record.
Me.NextRecord = False
Else
'Resent intPrintCounter and advance to the next record.
intPrintCounter = 1
Me.NextRecord = True
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
intPrintCounter = 1
intNumberRepeats = Forms!frmHolidayParty!TimestoRepeatRecord
End Sub

 

[Marshall Barton]

Oh, I guess the code would help!:

Dim intPrintCounter As Integer
Dim intNumberRepeats As Integer

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
'Note: intNumberRepeats and intPrintCounter are initialized
'in the report's OnOpen event.
If intPrintCounter < intNumberRepeats Then
intPrintCounter = intPrintCounter + 1
'Do not advance to the next record.
Me.NextRecord = False
Else
'Resent intPrintCounter and advance to the next record.
intPrintCounter = 1
Me.NextRecord = True
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
intPrintCounter = 1
intNumberRepeats = Forms!frmHolidayParty!TimestoRepeatRecord
End Sub

If you are printing while the report is being previewed,
then I suspect the problem is that the print counter is not
(re)initialized in the report header section's Format event.
There are vaious combinations of CanGrow/KeepTogether/??
that can throw a monkey wrench into that technique.

An alternative approach is to dump all that code and create
a table (named Numbers) with one field (named Num) and
populated with the values 1,2,3,... up to more than you will
ever need.

Then use a query as the report's record source:

SELECT Num, T.*
FROM yourtable As T, Numbers
WHERE Num < Forms!frmHolidayParty!TimestoRepeatRecord

You can even bind the Num field to a text box on the report
to number the copies.

 

[Sandi Vogel]

Thank you SO MUCH! This has been driving me insane. I didn't try your
alternative approach, but your supposition that:

If you are printing while the report is being previewed,
then I suspect the problem is that the print counter is not
(re)initialized in the report header section's Format event.
There are vaious combinations of CanGrow/KeepTogether/??
that can throw a monkey wrench into that technique.

was totally on the money. I changed acViewPreview to acViewNormal, and
stopped Keep Together. It's printing perfectly now.

THANK YOU!!!!