Labels - Printing varying numbers

[MJ]

Is it possible to print varying numbers of labels from Access?

[Tom van Stiphout]

On 9 Mar 2006 19:48:18 -0800, "MJ" <je*******@cox.net> wrote:

Yes.
For i = 0 to Rnd(1000)
Docmd.OpenReport "myLabelReport"
Next i

Or clarify your question.

-Tom.

Is it possible to print varying numbers of labels from Access?

[MJ]

I am trying to find out if it is possible to make labels to put on
products as they arrive for inventory purposes--that are listed in a
database. Can a table or query allow you to print 10 labels for one
item, 5 of another, etc.--instead of 1 label each or a whole sheet of
one.

[Tom van Stiphout]

On 9 Mar 2006 20:07:55 -0800, "MJ" <je*******@cox.net> wrote:

I'm assuming a sheet has several labels.
One option is to print a sheet for each product and stick it in a
binder. User locates that page in the binder, takes off as many labels
as needed, and prints new sheet if ran out.

Alternatively you can create a query that returns 10 rows of product A
and 5 rows of product B, and use that query for your label report.
This seems to be what you want to do. To accomplish the "several
duplicate rows" part, I would use a recordset on a "temporary" table,
and use rs.AddNew to create as many rows as I needed:
For i = 1 to Me.MyQuantityField
rs.AddNew
rs.ProductID = Me.cboProducts
'etc.
rs.Update
Next i

-Tom.

I am trying to find out if it is possible to make labels to put on
products as they arrive for inventory purposes--that are listed in a
database. Can a table or query allow you to print 10 labels for one
item, 5 of another, etc.--instead of 1 label each or a whole sheet of
one.

[MJ]

Thanks!

[Ken Mylar]

I don't recall where I got this function, but it alllows you to skip
labels (if your sheet is missing some already) and also enter how many
you would like.

Create the following Module:
************************************************** ************************
Option Compare Database
Option Explicit

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

'================================================= =========
' The following function will cause an inputbox to
' display when the report is run that prompts the user
' for the number of used labels to skip and how many
' copies of each label should be printed.
'================================================= ==========

Function LabelSetup()
intLabelBlanks = Val(InputBox$("Enter Number of blank labels to
skip"))
intLabelCopies = Val(InputBox$("Enter Number of Copies to
Print"))
If intLabelBlanks < 0 Then intLabelBlanks = 0
If intLabelCopies < 1 Then intLabelCopies = 1
End Function

'================================================= ==========
' The following function sets the variables to a zero
'================================================= ==========

Function LabelInitialize()
intBlankCount = 0
intCopyCount = 0
End Function

'================================================= ==========
' The following function is the main part of this code
' that allows the labels to print as the user desires.
'================================================= ==========

Function LabelLayout(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
************************************************** *********************************

Then on your label report's OnOpen event put:

=LabelSetup()

Thanks,
Ken

[Allen Browne]

The approach in this function is unreliable.

Despite the fact that it is published in several books and even in one of
Microsoft's knowledgebase article, you will find that you get unreliable
results if you just print (say) page 5 of a label run. The problem is that
the report events may not fire for the intervening pages that were not
printed, so what you end up with is the wrong set of labels and possibly the
wrong count of labels.

There is another solution that relies in generating the records dynamically
in the report's query (using a Cartesian product) instead of relying on the
report's events. Details in:
Printing a Quantity of a Label
at:
http://allenbrowne.com/ser-39.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ken Mylar" <km****@elch.org> wrote in message
news:11**********************@e56g2000cwe.googlegr oups.com...

I don't recall where I got this function, but it alllows you to skip
labels (if your sheet is missing some already) and also enter how many
you would like.

Create the following Module:
************************************************** ************************
Option Compare Database
Option Explicit

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

'================================================= =========
' The following function will cause an inputbox to
' display when the report is run that prompts the user
' for the number of used labels to skip and how many
' copies of each label should be printed.
'================================================= ==========

Function LabelSetup()
intLabelBlanks = Val(InputBox$("Enter Number of blank labels to
skip"))
intLabelCopies = Val(InputBox$("Enter Number of Copies to
Print"))
If intLabelBlanks < 0 Then intLabelBlanks = 0
If intLabelCopies < 1 Then intLabelCopies = 1
End Function

'================================================= ==========
' The following function sets the variables to a zero
'================================================= ==========

Function LabelInitialize()
intBlankCount = 0
intCopyCount = 0
End Function

'================================================= ==========
' The following function is the main part of this code
' that allows the labels to print as the user desires.
'================================================= ==========

Function LabelLayout(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
************************************************** *********************************

Then on your label report's OnOpen event put:

=LabelSetup()

Thanks,
Ken