Printing Lables

[Homer]

-- I have a data base that creates reports from the information input by the
user. These reports are in the form of a book. I have a form that allows the
user to input information for a header. I need to create a button that prints
the header information as a label for the book. I am using Avery 5164 labels
(two columns, three labels per column) and found the label wizard in 'new
report' but because normally I only use one label per printing I need a way
to tell Access which position on the Avery label to print the information to
so we can use the whole sheet.


What The Bold Print Giveth; The Fine Print Taketh Away.

 

[Gina Whipp]

Homer,

I use the below... (Wish I could remember where I got it from!) Place the
below in a module.

NOTE: Change the form and text box name in the module to correspond to the
name of your from and text box name.

***START CODE
Option Compare Database
Option Explicit

Dim intLabelBlanks&
Dim intLabelCopies&
Dim intBlankCount&
Dim intCopyCount&
Function MailLabelSetUp()
intLabelBlanks& = Val(Forms![frmMailingLabels]![txtSkip])
'Tells you how many to skip
intLabelCopies& = Val(Forms![frmMailingLabels]![txtHowMany])
'Tells you how many of the label you want to print
If intLabelBlanks& < 0 Then intLabelBlanks& = 0
If intLabelCopies& < 1 Then intLabelCopies& = 1
End Function
Function MailLabelInitialize()
intBlankCount& = 0
intCopyCount& = 0
End Function
Function MailLabelLayout(R As Report)
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
***END CODE

Then...

Place =MailLabelInitialize() in the ReportHeader - On Format
Place =MailLabelLayout([Reports]![YourReportName]) in the ReportDetail - On
Print

 

[fredg]

-- I have a data base that creates reports from the information input by the
user. These reports are in the form of a book. I have a form that allows the
user to input information for a header. I need to create a button that prints
the header information as a label for the book. I am using Avery 5164 labels
(two columns, three labels per column) and found the label wizard in 'new
report' but because normally I only use one label per printing I need a way
to tell Access which position on the Avery label to print the information to
so we can use the whole sheet.

What The Bold Print Giveth; The Fine Print Taketh Away.

First create a label report.
Then add a Report Header.
Add 2 unbound controls to the Header.
Name one "SkipControl"
Leave it's control source blank.

Name the other control "SkipCounter"
Set it's control source to:
=[Skip how many?]

Then code the report's Detail Print event¡K

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount <= [SkipCounter] And [SkipControl] = "Skip" Then
Me.NextRecord = False ' Skip missing labels on sheet
Me.PrintSection = False
Else
[SkipControl] = "No" ' Print labels
Me.PrintSection = True
Me.NextRecord = True
End If

End Sub

And code the Report Header's Format event¡K

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
[SkipControl] = "Skip"
Cancel = True
End Sub

Run the label report. Enter the number of label positions you wish to
skip.

 

[Homer]

--
What The Bold Print Giveth; The Fine Print Taketh Away.

-- I have a data base that creates reports from the information input by the
user. These reports are in the form of a book. I have a form that allows the
user to input information for a header. I need to create a button that prints
the header information as a label for the book. I am using Avery 5164 labels
(two columns, three labels per column) and found the label wizard in 'new
report' but because normally I only use one label per printing I need a way
to tell Access which position on the Avery label to print the information to
so we can use the whole sheet.

What The Bold Print Giveth; The Fine Print Taketh Away.

First create a label report.
Then add a Report Header.
Add 2 unbound controls to the Header.
Name one "SkipControl"
Leave it's control source blank.

Name the other control "SkipCounter"
Set it's control source to:
=[Skip how many?]

Then code the report's Detail Print event…

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount <= [SkipCounter] And [SkipControl] = "Skip" Then
Me.NextRecord = False ' Skip missing labels on sheet
Me.PrintSection = False
Else
[SkipControl] = "No" ' Print labels
Me.PrintSection = True
Me.NextRecord = True
End If

End Sub

And code the Report Header's Format event…

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
[SkipControl] = "Skip"
Cancel = True
End Sub

Run the label report. Enter the number of label positions you wish to
skip.

I thank both of you for the help. I used fredq's technique because I
understood the code better and liked the prompt for how many to skip. It
works great, Thanks again.

 

[Gina Whipp]

Can't blame you... I like his code better too!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

--
What The Bold Print Giveth; The Fine Print Taketh Away.

-- I have a data base that creates reports from the information input
by the
user. These reports are in the form of a book. I have a form that
allows the
user to input information for a header. I need to create a button that
prints
the header information as a label for the book. I am using Avery 5164
labels
(two columns, three labels per column) and found the label wizard in
'new
report' but because normally I only use one label per printing I need a
way
to tell Access which position on the Avery label to print the
information to
so we can use the whole sheet.

What The Bold Print Giveth; The Fine Print Taketh Away.

First create a label report.
Then add a Report Header.
Add 2 unbound controls to the Header.
Name one "SkipControl"
Leave it's control source blank.

Name the other control "SkipCounter"
Set it's control source to:
=[Skip how many?]

Then code the report's Detail Print event.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If PrintCount <= [SkipCounter] And [SkipControl] = "Skip" Then
Me.NextRecord = False ' Skip missing labels on sheet
Me.PrintSection = False
Else
[SkipControl] = "No" ' Print labels
Me.PrintSection = True
Me.NextRecord = True
End If

End Sub

And code the Report Header's Format event.

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As
Integer)
[SkipControl] = "Skip"
Cancel = True
End Sub

Run the label report. Enter the number of label positions you wish to
skip.

I thank both of you for the help. I used fredq's technique because I
understood the code better and liked the prompt for how many to skip. It
works great, Thanks again.