Repeat Labels with Running Sum

  • Thread starter Thread starter Sumit
  • Start date Start date
S

Sumit

Hi Everyone,

I used the code given in http://support.microsoft.com/?kbid=207664
for Repeating Report Records a Specified Number of Times (in Labels)

I introduced a new field "RepeatNumber" in my table so that the Report
Record are repeated the number of times mentioned in this field; this works
fine.

For each type of record, I need to show RecordToRepeat and count in the
report.
Example:
If my data in table is-
RecordToRepeat RepeatNumber
----------------- ---------------
AB 2
CD 1
EF 2
Report should show-
AB - 1
AB - 2
CD - 1
EF - 1
EF - 2
I used running sum over group but it does not work and displays the
following-
AB - 1
AB - 1
CD - 1
EF - 1
EF - 1

Any help would be much appreciated.
Thank you in advance.
Sumit
 
Hi Everyone,

I used the code given in http://support.microsoft.com/?kbid=207664
for Repeating Report Records a Specified Number of Times (in Labels)

I introduced a new field "RepeatNumber" in my table so that the Report
Record are repeated the number of times mentioned in this field; this works
fine.

For each type of record, I need to show RecordToRepeat and count in the
report.
Example:
If my data in table is-
RecordToRepeat RepeatNumber
----------------- ---------------
AB 2
CD 1
EF 2
Report should show-
AB - 1
AB - 2
CD - 1
EF - 1
EF - 2
I used running sum over group but it does not work and displays the
following-
AB - 1
AB - 1
CD - 1
EF - 1
EF - 1

Any help would be much appreciated.
Thank you in advance.
Sumit
Click to expand...

Here is my method to skip missing label positions and to repeat a
label as indicated in a record field named [QTY].

As written it will print
1 of 3
2 of 3
3 of 3
etc.
on each label.
You can change that if you wish.

First make a regular label report that correctly prints one label per
record.

It assumes a Report Record Source with a field [Qty] that contains the
number of times you wish to repeat a label.

Make sure the [Qty] field (or your equivalent field name) is included
in the Detail section.
Set it's Visible property to No.

Add an Unbound text control to the label detail section where you want
to show the label count number.
Name this control LabelCount

Next add a Report Header to the report.
Add 2 unbound text boxes to the Header.
1) Name one SkipControl
Leave it's Control Source unbound
2) Name the other SkipCounter
Set it's Control Source to:
= [Skip How Many?]

Then code the Report Header Format event as follows:
(watch out for word wrap on the longer lines):

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
[SkipControl] = "Skip"
Cancel = True
End Sub
=================
Next code the Detail Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [QTY] <= 0 Or IsNull([QTY]) Then
Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False
End If

End Sub
======
Next code the Detail Print event:
(Note that intMyPrint is Static!!)

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static intMyPrint As Integer
If PrintCount <= [SkipCounter] And [SkipControl] = "Skip" Then
Me.NextRecord = False
Me.PrintSection = False
intMyPrint = 0
Else
[SkipControl] = "No"
Me.PrintSection = True
Me.NextRecord = True

intMyPrint = intMyPrint + 1

' Print 1 of X on the label
LabelCount = intMyPrint & " of " & [QTY]
If [QTY] <= 0 Then
Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False
Exit Sub
End If

If intMyPrint Mod [QTY] = 0 Then
Me.NextRecord = True
intMyPrint = 0
Else
Me.NextRecord = False
End If
End If


End Sub
=========

When you run the report, it will ask how many missing label positions
to skip.
Then it will repeat labels as indicated in each records [Qty] field.
**Remember to change the [Qty] field to whatever you named your
field.**

Always print all of the labels starting from Page 1

Hope this helps.
 
Dear Fred,
Thankyou so much for your time.
I have been able to solve the problem by making a minor changes on my code
based on your reply.
With this, I can also skip specified number of blank records at first.

FYI, I have added the following line at two places of DetailOnPrint Section-
rpt!LabelCount = iLSCopiesCount & " of " & iLSCopiesToPrint

(LabelCount is the text box added to report as advised by you)
iLSCopiesCount is for counting the number of times record to repeat
iLSCopiesToPrint is the total number of times record to be repeated (from
table)

Following is the full code for DetailOnPrint Section in my module:
--------------------------------------
Sub ls_DetailOnPrint(rpt As Report)

On Error GoTo ls_DetailOnPrint_err

iLSCopiesToPrint = rpt![Repeat]
' [Repeat] is a text box containing the number of times for a record to
repeat. This is set to invisble in report.

If iLSBlankCount < iLSBlankRecordsToPrint Then
'Leave a blank detail section without skipping a record
rpt.NextRecord = False
rpt.PrintSection = False
iLSBlankCount = iLSBlankCount + 1
Else
If iLSCopiesCount < iLSCopiesToPrint Then
rpt.NextRecord = False
rpt!LabelCount = iLSCopiesCount & " of " & iLSCopiesToPrint
'code line added here-------------------------------------------------
iLSCopiesCount = iLSCopiesCount + 1
Else
rpt!LabelCount = iLSCopiesCount & " of " & iLSCopiesToPrint
'code line added here ------------------------------------------------
iLSCopiesCount = 1
End If
End If

ls_DetailOnPrint_exit:
Exit Sub
ls_DetailOnPrint_err:
MsgBox "Error in Label Saver subroutine ls_DetailOnPrint - " & Err & " -
" & Err.Description
GoTo ls_DetailOnPrint_exit
End Sub


fredg said:
Hi Everyone,

I used the code given in http://support.microsoft.com/?kbid=207664
for Repeating Report Records a Specified Number of Times (in Labels)

I introduced a new field "RepeatNumber" in my table so that the Report
Record are repeated the number of times mentioned in this field; this works
fine.

For each type of record, I need to show RecordToRepeat and count in the
report.
Example:
If my data in table is-
RecordToRepeat RepeatNumber
----------------- ---------------
AB 2
CD 1
EF 2
Report should show-
AB - 1
AB - 2
CD - 1
EF - 1
EF - 2
I used running sum over group but it does not work and displays the
following-
AB - 1
AB - 1
CD - 1
EF - 1
EF - 1

Any help would be much appreciated.
Thank you in advance.
Sumit
Click to expand...

Here is my method to skip missing label positions and to repeat a
label as indicated in a record field named [QTY].

As written it will print
1 of 3
2 of 3
3 of 3
etc.
on each label.
You can change that if you wish.

First make a regular label report that correctly prints one label per
record.

It assumes a Report Record Source with a field [Qty] that contains the
number of times you wish to repeat a label.

Make sure the [Qty] field (or your equivalent field name) is included
in the Detail section.
Set it's Visible property to No.

Add an Unbound text control to the label detail section where you want
to show the label count number.
Name this control LabelCount

Next add a Report Header to the report.
Add 2 unbound text boxes to the Header.
1) Name one SkipControl
Leave it's Control Source unbound
2) Name the other SkipCounter
Set it's Control Source to:
= [Skip How Many?]

Then code the Report Header Format event as follows:
(watch out for word wrap on the longer lines):

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
[SkipControl] = "Skip"
Cancel = True
End Sub
=================
Next code the Detail Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [QTY] <= 0 Or IsNull([QTY]) Then
Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False
End If

End Sub
======
Next code the Detail Print event:
(Note that intMyPrint is Static!!)

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static intMyPrint As Integer
If PrintCount <= [SkipCounter] And [SkipControl] = "Skip" Then
Me.NextRecord = False
Me.PrintSection = False
intMyPrint = 0
Else
[SkipControl] = "No"
Me.PrintSection = True
Me.NextRecord = True

intMyPrint = intMyPrint + 1

' Print 1 of X on the label
LabelCount = intMyPrint & " of " & [QTY]
If [QTY] <= 0 Then
Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False
Exit Sub
End If

If intMyPrint Mod [QTY] = 0 Then
Me.NextRecord = True
intMyPrint = 0
Else
Me.NextRecord = False
End If
End If


End Sub
=========

When you run the report, it will ask how many missing label positions
to skip.
Then it will repeat labels as indicated in each records [Qty] field.
**Remember to change the [Qty] field to whatever you named your
field.**

Always print all of the labels starting from Page 1

Hope this helps.
Click to expand...
 
Hi everyone / Hi Fred,
I have another problemwith the same label report (below); when i try to
print a specific page of my multi-paged report, irrespective of page number
chosen to print, it prints only from the first page.
If I want to print say pages 3 to 4, it prints pages 1-2. (it always starts
from 1).
(The report displayed has everything ok.)
Could this be the result due to the codes on the OnPrint section of details?
Pls advise.
Thanks in adavance.
Sumit


Sumit said:
Dear Fred,
Thankyou so much for your time.
I have been able to solve the problem by making a minor changes on my code
based on your reply.
With this, I can also skip specified number of blank records at first.

FYI, I have added the following line at two places of DetailOnPrint Section-
rpt!LabelCount = iLSCopiesCount & " of " & iLSCopiesToPrint

(LabelCount is the text box added to report as advised by you)
iLSCopiesCount is for counting the number of times record to repeat
iLSCopiesToPrint is the total number of times record to be repeated (from
table)

Following is the full code for DetailOnPrint Section in my module:
--------------------------------------
Sub ls_DetailOnPrint(rpt As Report)

On Error GoTo ls_DetailOnPrint_err

iLSCopiesToPrint = rpt![Repeat]
' [Repeat] is a text box containing the number of times for a record to
repeat. This is set to invisble in report.

If iLSBlankCount < iLSBlankRecordsToPrint Then
'Leave a blank detail section without skipping a record
rpt.NextRecord = False
rpt.PrintSection = False
iLSBlankCount = iLSBlankCount + 1
Else
If iLSCopiesCount < iLSCopiesToPrint Then
rpt.NextRecord = False
rpt!LabelCount = iLSCopiesCount & " of " & iLSCopiesToPrint
'code line added here-------------------------------------------------
iLSCopiesCount = iLSCopiesCount + 1
Else
rpt!LabelCount = iLSCopiesCount & " of " & iLSCopiesToPrint
'code line added here ------------------------------------------------
iLSCopiesCount = 1
End If
End If

ls_DetailOnPrint_exit:
Exit Sub
ls_DetailOnPrint_err:
MsgBox "Error in Label Saver subroutine ls_DetailOnPrint - " & Err & " -
" & Err.Description
GoTo ls_DetailOnPrint_exit
End Sub


fredg said:
Hi Everyone,

I used the code given in http://support.microsoft.com/?kbid=207664
for Repeating Report Records a Specified Number of Times (in Labels)

I introduced a new field "RepeatNumber" in my table so that the Report
Record are repeated the number of times mentioned in this field; this works
fine.

For each type of record, I need to show RecordToRepeat and count in the
report.
Example:
If my data in table is-
RecordToRepeat RepeatNumber
----------------- ---------------
AB 2
CD 1
EF 2
Report should show-
AB - 1
AB - 2
CD - 1
EF - 1
EF - 2
I used running sum over group but it does not work and displays the
following-
AB - 1
AB - 1
CD - 1
EF - 1
EF - 1

Any help would be much appreciated.
Thank you in advance.
Sumit
Click to expand...

Here is my method to skip missing label positions and to repeat a
label as indicated in a record field named [QTY].

As written it will print
1 of 3
2 of 3
3 of 3
etc.
on each label.
You can change that if you wish.

First make a regular label report that correctly prints one label per
record.

It assumes a Report Record Source with a field [Qty] that contains the
number of times you wish to repeat a label.

Make sure the [Qty] field (or your equivalent field name) is included
in the Detail section.
Set it's Visible property to No.

Add an Unbound text control to the label detail section where you want
to show the label count number.
Name this control LabelCount

Next add a Report Header to the report.
Add 2 unbound text boxes to the Header.
1) Name one SkipControl
Leave it's Control Source unbound
2) Name the other SkipCounter
Set it's Control Source to:
= [Skip How Many?]

Then code the Report Header Format event as follows:
(watch out for word wrap on the longer lines):

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
[SkipControl] = "Skip"
Cancel = True
End Sub
=================
Next code the Detail Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [QTY] <= 0 Or IsNull([QTY]) Then
Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False
End If

End Sub
======
Next code the Detail Print event:
(Note that intMyPrint is Static!!)

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static intMyPrint As Integer
If PrintCount <= [SkipCounter] And [SkipControl] = "Skip" Then
Me.NextRecord = False
Me.PrintSection = False
intMyPrint = 0
Else
[SkipControl] = "No"
Me.PrintSection = True
Me.NextRecord = True

intMyPrint = intMyPrint + 1

' Print 1 of X on the label
LabelCount = intMyPrint & " of " & [QTY]
If [QTY] <= 0 Then
Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False
Exit Sub
End If

If intMyPrint Mod [QTY] = 0 Then
Me.NextRecord = True
intMyPrint = 0
Else
Me.NextRecord = False
End If
End If


End Sub
=========

When you run the report, it will ask how many missing label positions
to skip.
Then it will repeat labels as indicated in each records [Qty] field.
**Remember to change the [Qty] field to whatever you named your
field.**

Always print all of the labels starting from Page 1

Hope this helps.
Click to expand...
Click to expand...
 
Hi everyone / Hi Fred,
I have another problemwith the same label report (below); when i try to
print a specific page of my multi-paged report, irrespective of page number
chosen to print, it prints only from the first page.
If I want to print say pages 3 to 4, it prints pages 1-2. (it always starts
from 1).
(The report displayed has everything ok.)
Could this be the result due to the codes on the OnPrint section of details?
Pls advise.
Thanks in adavance.
Sumit

Sumit said:
Dear Fred,
Thankyou so much for your time.
I have been able to solve the problem by making a minor changes on my code
based on your reply.
With this, I can also skip specified number of blank records at first.

FYI, I have added the following line at two places of DetailOnPrint Section-
rpt!LabelCount = iLSCopiesCount & " of " & iLSCopiesToPrint

(LabelCount is the text box added to report as advised by you)
iLSCopiesCount is for counting the number of times record to repeat
iLSCopiesToPrint is the total number of times record to be repeated (from
table)

Following is the full code for DetailOnPrint Section in my module:
--------------------------------------
Sub ls_DetailOnPrint(rpt As Report)

On Error GoTo ls_DetailOnPrint_err

iLSCopiesToPrint = rpt![Repeat]
' [Repeat] is a text box containing the number of times for a record to
repeat. This is set to invisble in report.

If iLSBlankCount < iLSBlankRecordsToPrint Then
'Leave a blank detail section without skipping a record
rpt.NextRecord = False
rpt.PrintSection = False
iLSBlankCount = iLSBlankCount + 1
Else
If iLSCopiesCount < iLSCopiesToPrint Then
rpt.NextRecord = False
rpt!LabelCount = iLSCopiesCount & " of " & iLSCopiesToPrint
'code line added here-------------------------------------------------
iLSCopiesCount = iLSCopiesCount + 1
Else
rpt!LabelCount = iLSCopiesCount & " of " & iLSCopiesToPrint
'code line added here ------------------------------------------------
iLSCopiesCount = 1
End If
End If

ls_DetailOnPrint_exit:
Exit Sub
ls_DetailOnPrint_err:
MsgBox "Error in Label Saver subroutine ls_DetailOnPrint - " & Err & " -
" & Err.Description
GoTo ls_DetailOnPrint_exit
End Sub


fredg said:
On Wed, 13 Jul 2005 16:52:27 +0545, Sumit wrote:

Hi Everyone,

I used the code given in http://support.microsoft.com/?kbid=207664
for Repeating Report Records a Specified Number of Times (in Labels)

I introduced a new field "RepeatNumber" in my table so that the Report
Record are repeated the number of times mentioned in this field; this works
fine.

For each type of record, I need to show RecordToRepeat and count in the
report.
Example:
If my data in table is-
RecordToRepeat RepeatNumber
----------------- ---------------
AB 2
CD 1
EF 2
Report should show-
AB - 1
AB - 2
CD - 1
EF - 1
EF - 2
I used running sum over group but it does not work and displays the
following-
AB - 1
AB - 1
CD - 1
EF - 1
EF - 1

Any help would be much appreciated.
Thank you in advance.
Sumit

Here is my method to skip missing label positions and to repeat a
label as indicated in a record field named [QTY].

As written it will print
1 of 3
2 of 3
3 of 3
etc.
on each label.
You can change that if you wish.

First make a regular label report that correctly prints one label per
record.

It assumes a Report Record Source with a field [Qty] that contains the
number of times you wish to repeat a label.

Make sure the [Qty] field (or your equivalent field name) is included
in the Detail section.
Set it's Visible property to No.

Add an Unbound text control to the label detail section where you want
to show the label count number.
Name this control LabelCount

Next add a Report Header to the report.
Add 2 unbound text boxes to the Header.
1) Name one SkipControl
Leave it's Control Source unbound
2) Name the other SkipCounter
Set it's Control Source to:
= [Skip How Many?]

Then code the Report Header Format event as follows:
(watch out for word wrap on the longer lines):

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
[SkipControl] = "Skip"
Cancel = True
End Sub
=================
Next code the Detail Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [QTY] <= 0 Or IsNull([QTY]) Then
Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False
End If

End Sub
======
Next code the Detail Print event:
(Note that intMyPrint is Static!!)

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static intMyPrint As Integer
If PrintCount <= [SkipCounter] And [SkipControl] = "Skip" Then
Me.NextRecord = False
Me.PrintSection = False
intMyPrint = 0
Else
[SkipControl] = "No"
Me.PrintSection = True
Me.NextRecord = True

intMyPrint = intMyPrint + 1

' Print 1 of X on the label
LabelCount = intMyPrint & " of " & [QTY]
If [QTY] <= 0 Then
Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False
Exit Sub
End If

If intMyPrint Mod [QTY] = 0 Then
Me.NextRecord = True
intMyPrint = 0
Else
Me.NextRecord = False
End If
End If


End Sub
=========

When you run the report, it will ask how many missing label positions
to skip.
Then it will repeat labels as indicated in each records [Qty] field.
**Remember to change the [Qty] field to whatever you named your
field.**

Always print all of the labels starting from Page 1

Hope this helps.
Click to expand...
Click to expand...

Did you not read this line in my reply?
*** Always print all of the labels starting from Page 1 ***

If you only wish to print certain records, i.e. labels for 'John Doe',
filter the records first, either by using a query with criteria as
report record source,
or ....
opening the report from a command button, using DoCmd.OpenReport
"ReportName", acViewPreview, , "[FirstName] = 'John' AND [LastName] =
'Doe'"

There are different syntaxes for text and numbers and dates, as well
as if you use a control on a form to filter by.

Look up
Where Clause + Restrict records to a subset of records
in VBA help.
Click to expand...
 
Hi everyone / Hi Fred,
I have another problem with the same label report (below); when i try to
print a specific page of my multi-paged report, irrespective of page number
chosen to print, it prints only from the first page.
If I want to print say pages 3 to 4, it prints pages 1-2. (it always starts
from 1).
(The report displayed has everything ok.)
Could this be the result due to the codes on the OnPrint section of details?
Pls advise.
Thanks in adavance.
Sumit

fredg said:
Hi Everyone,

I used the code given in http://support.microsoft.com/?kbid=207664
for Repeating Report Records a Specified Number of Times (in Labels)

I introduced a new field "RepeatNumber" in my table so that the Report
Record are repeated the number of times mentioned in this field; this works
fine.

For each type of record, I need to show RecordToRepeat and count in the
report.
Example:
If my data in table is-
RecordToRepeat RepeatNumber
----------------- ---------------
AB 2
CD 1
EF 2
Report should show-
AB - 1
AB - 2
CD - 1
EF - 1
EF - 2
I used running sum over group but it does not work and displays the
following-
AB - 1
AB - 1
CD - 1
EF - 1
EF - 1

Any help would be much appreciated.
Thank you in advance.
Sumit
Click to expand...

Here is my method to skip missing label positions and to repeat a
label as indicated in a record field named [QTY].

As written it will print
1 of 3
2 of 3
3 of 3
etc.
on each label.
You can change that if you wish.

First make a regular label report that correctly prints one label per
record.

It assumes a Report Record Source with a field [Qty] that contains the
number of times you wish to repeat a label.

Make sure the [Qty] field (or your equivalent field name) is included
in the Detail section.
Set it's Visible property to No.

Add an Unbound text control to the label detail section where you want
to show the label count number.
Name this control LabelCount

Next add a Report Header to the report.
Add 2 unbound text boxes to the Header.
1) Name one SkipControl
Leave it's Control Source unbound
2) Name the other SkipCounter
Set it's Control Source to:
= [Skip How Many?]

Then code the Report Header Format event as follows:
(watch out for word wrap on the longer lines):

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
[SkipControl] = "Skip"
Cancel = True
End Sub
=================
Next code the Detail Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [QTY] <= 0 Or IsNull([QTY]) Then
Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False
End If

End Sub
======
Next code the Detail Print event:
(Note that intMyPrint is Static!!)

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Static intMyPrint As Integer
If PrintCount <= [SkipCounter] And [SkipControl] = "Skip" Then
Me.NextRecord = False
Me.PrintSection = False
intMyPrint = 0
Else
[SkipControl] = "No"
Me.PrintSection = True
Me.NextRecord = True

intMyPrint = intMyPrint + 1

' Print 1 of X on the label
LabelCount = intMyPrint & " of " & [QTY]
If [QTY] <= 0 Then
Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False
Exit Sub
End If

If intMyPrint Mod [QTY] = 0 Then
Me.NextRecord = True
intMyPrint = 0
Else
Me.NextRecord = False
End If
End If


End Sub
=========

When you run the report, it will ask how many missing label positions
to skip.
Then it will repeat labels as indicated in each records [Qty] field.
**Remember to change the [Qty] field to whatever you named your
field.**

Always print all of the labels starting from Page 1

Hope this helps.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sumif with two criteria on on difference worksheets 4
Interesting problem in excel, producing a list for aspecific date 3
Concatenate Function 11
Abs Formula 4
help with summing fields in repot 3
Populating Cells with Table Data 1
RANK - Remove repeated ranks for sorting 9
CountIf - 2 Criteria 3

Back
Top