Duplicating labels for certain records in a report 
Author Message
 Duplicating labels for certain records in a report

I have a report that I have setup which will change the background of
the report based on the value of a certain field.  I was able to
achieve this using a simple select case statement in the report's
OnPrint event.  Now what I would like to do is print a set of black
labels based on the quantity field.

So basically each record in a report is based on a color.  For a
certain color if the value of a text field is = DLE  and the quantity
is 6 I would need to print 6 black labels and 1 white label for that
color.  If the field is = to anything other than DLE, I would just
need to print 1 white label.

The trick is this I would have to do this for everyrecord in the
report.

Any help would really be appreciated.



Fri, 22 Oct 2004 06:12:12 GMT  
 Duplicating labels for certain records in a report
Here is some code for making more than 1 copy of a label.
You shold be able to tweak it to meet your needs.

--
Joe Fallon
Access MVP


Quote:
> I have a report that I have setup which will change the background of
> the report based on the value of a certain field.  I was able to
> achieve this using a simple select case statement in the report's
> OnPrint event.  Now what I would like to do is print a set of black
> labels based on the quantity field.

> So basically each record in a report is based on a color.  For a
> certain color if the value of a text field is = DLE  and the quantity
> is 6 I would need to print 6 black labels and 1 white label for that
> color.  If the field is = to anything other than DLE, I would just
> need to print 1 white label.

> The trick is this I would have to do this for everyrecord in the
> report.

> Any help would really be appreciated.



Fri, 22 Oct 2004 07:14:06 GMT  
 Duplicating labels for certain records in a report
(Hit the send button a bit early. Here is the code.)

Here is one method:
Create a Module and paste this code into it:

Option Compare Database

    Dim intLabelBlanks As Integer
    Dim intLabelCopies As Integer
    Dim intBlankCount As Integer
    Dim intCopyCount As Integer

Function LabelInitialize()

   intBlankCount = 0
   intCopyCount = 0

End Function

Public Function NumberOfCopies(R As Report, intLabelBlanks As Integer,
intLabelCopies As Integer)

    If intLabelBlanks < 0 Then intLabelBlanks = 0
    If intLabelCopies < 1 Then intLabelCopies = 1

    If intBlankCount < intLabelBlanks Then
      R.NextRecord = False
      R.PrintSection = False
      intBlankCount = intBlankCount + 1
    Else
      If intCopyCount < (intLabelCopies - 1) Then
        R.NextRecord = False
        intCopyCount = intCopyCount + 1
      Else
        intCopyCount = 0
      End If
    End If

End Function

Then,
Create a form and add an unbound textbox called LabelSkip with a default
value of zero.

Add another unbound textbox called QtyToPrint with a default value of 1.

Add a Print Preview button to the form to call your report using the OnClick
event:
Private Sub Print_Preview_Click()
On Error GoTo Err_Print_Preview_Click

    DoCmd.OpenReport "YourReportName", acPREVIEW

Exit_Print_Preview_Click:
    Exit Sub

Err_Print_Preview_Click:
    MsgBox Error.Description
    Resume Exit_Print_Preview_Click

End Sub

In the Report Header OnFormat event use:
=LabelInitialize()

In the Detail OnPrint event of your report use:
=NumberOfCopies([Reports]![Your
ReportName],[Forms]![YourFormName]![LabelSkip],[QtyToPrint])

If you print a laser label, then to re-use your sheet, just skip position 1
by changing the value of LabelSkip to 1.
To print multiple labels for 1 person, change QtyToPrint to the number of
labels you want.

LabelSkip and QtyToPrint both need to be on the Report design, but you can
make them invisible.

--
Joe Fallon
Access MVP


Quote:
> I have a report that I have setup which will change the background of
> the report based on the value of a certain field.  I was able to
> achieve this using a simple select case statement in the report's
> OnPrint event.  Now what I would like to do is print a set of black
> labels based on the quantity field.

> So basically each record in a report is based on a color.  For a
> certain color if the value of a text field is = DLE  and the quantity
> is 6 I would need to print 6 black labels and 1 white label for that
> color.  If the field is = to anything other than DLE, I would just
> need to print 1 white label.

> The trick is this I would have to do this for everyrecord in the
> report.

> Any help would really be appreciated.



Fri, 22 Oct 2004 07:14:41 GMT  
 Duplicating labels for certain records in a report
Quote:

> (Hit the send button a bit early. Here is the code.)

> Here is one method:
> Create a Module and paste this code into it:

> Option Compare Database

>     Dim intLabelBlanks As Integer
>     Dim intLabelCopies As Integer
>     Dim intBlankCount As Integer
>     Dim intCopyCount As Integer

> Function LabelInitialize()

>    intBlankCount = 0
>    intCopyCount = 0

> End Function

> Public Function NumberOfCopies(R As Report, intLabelBlanks As Integer,
> intLabelCopies As Integer)

>     If intLabelBlanks < 0 Then intLabelBlanks = 0
>     If intLabelCopies < 1 Then intLabelCopies = 1

>     If intBlankCount < intLabelBlanks Then
>       R.NextRecord = False
>       R.PrintSection = False
>       intBlankCount = intBlankCount + 1
>     Else
>       If intCopyCount < (intLabelCopies - 1) Then
>         R.NextRecord = False
>         intCopyCount = intCopyCount + 1
>       Else
>         intCopyCount = 0
>       End If
>     End If

> End Function

> Then,
> Create a form and add an unbound textbox called LabelSkip with a default
> value of zero.

> Add another unbound textbox called QtyToPrint with a default value of 1.

> Add a Print Preview button to the form to call your report using the OnClick
> event:
> Private Sub Print_Preview_Click()
> On Error GoTo Err_Print_Preview_Click

>     DoCmd.OpenReport "YourReportName", acPREVIEW

> Exit_Print_Preview_Click:
>     Exit Sub

> Err_Print_Preview_Click:
>     MsgBox Error.Description
>     Resume Exit_Print_Preview_Click

> End Sub

> In the Report Header OnFormat event use:
> =LabelInitialize()

> In the Detail OnPrint event of your report use:
> =NumberOfCopies([Reports]![Your
> ReportName],[Forms]![YourFormName]![LabelSkip],[QtyToPrint])

> If you print a laser label, then to re-use your sheet, just skip position 1
> by changing the value of LabelSkip to 1.
> To print multiple labels for 1 person, change QtyToPrint to the number of
> labels you want.

> LabelSkip and QtyToPrint both need to be on the Report design, but you can
> make them invisible.

> --
> Joe Fallon
> Access MVP

Thanks!  Worked like a charm.  Only one more thing.  I need to make
sure the first label white and the rest black.  So if the quantity is
6 it should print 5 black labels and 1 white label.  Can this be done?

- Show quoted text -

Quote:


> > I have a report that I have setup which will change the background of
> > the report based on the value of a certain field.  I was able to
> > achieve this using a simple select case statement in the report's
> > OnPrint event.  Now what I would like to do is print a set of black
> > labels based on the quantity field.

> > So basically each record in a report is based on a color.  For a
> > certain color if the value of a text field is = DLE  and the quantity
> > is 6 I would need to print 6 black labels and 1 white label for that
> > color.  If the field is = to anything other than DLE, I would just
> > need to print 1 white label.

> > The trick is this I would have to do this for everyrecord in the
> > report.

> > Any help would really be appreciated.



Fri, 22 Oct 2004 17:37:51 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. I am getting duplicate records, I mean everything is duplicate even Access record number

2. Duplicate Records for Labels

3. Duplicating single record label

4. duplicate labels in Crystal Reports

5. Grouping in Label report to remove duplicates

6. How to print certain records in bold using Crystal Reports 4.6

7. Crystal Reports and duplicate records

8. Unwanted duplicate records in reports

9. How to create a certain number of buttons / labels

10. Duplicate Mailing Labels

11. Duplicate Labels

12. HOW DO I GROUP AND SUM CERTAIN RECORDS IN A SUBFORM

 

 
Powered by phpBB® Forum Software