Making a report that can leave records blank based on user input.

I am making a database of a large video archive. In this database I need to
be able to print out the records on small MiniDV labels. This is easy now
since I print out 15 at a time on a single sheet.

But after all the existing tapes are labeled, new tapes will need to be
entered inot the database and eventually labeled. This poses a problem since
if they only need to print out 5 labels, I will have 10 labels on the page
left blank.

Is there anyway I could solve this. I was thinking about some way to prompt
the user how many spaces on the particular previously used label sheet has
been printed and then the report would put in that amount of blank records
before starting the labels that are desired.

Any information would be helpful since this is my very first database I've
ever created/worked with.

Here are two ways which not only prints any amount of
labels but also lets you skip the labels already used. One I got from a
previous post by Fred G.

Method 1

See article:
Skip Used Mailing Labels and Print Duplicates in Access 2000
at:
http://support.microsoft.com/default...roduct=acc2000

Printing Multiple Copies of the Same Label
When you click Print on the File menu, you can choose to
print multiple copies of the same report. But when you try
to print a single mailing label 20 times, Access prints one
label on each of 20 pages.

On a dot matrix printer, using single column labels, you
can work around this behavior by defining each label as a
separate page. However, you cannot use this method for
laser printers or multiple-column labels. To work around
this behavior, use the step-by-step procedure described below.

back to the top
Using Labels That Would Otherwise Be Wasted
After printing labels, you usually end up with a partially
used last page. There is no built-in mechanism in Access to
use the remaining labels on a partially used page. Access
always starts on a new page. On a dot matrix printer, you
can adjust the top of form manually. But you cannot do that
on laser printers. To solve this problem, use the
step-by-step procedure described below.

back to the top
Step-by-Step Procedure to Solve Both Problems
The Access report generator provides powerful hooks that
allow control over the finished product. By calling a
function from the OnFormat property of the report's detail
section, you can alter the MoveLayout, NextRecord, and
PrintSection properties to leave blank spaces or print
multiple copies on the same page. The following code is
generic. You can attach it to any Mailing Label report to
print multiple copies and to skip used labels if needed. To
use the example, you need to have a mailing label report
called MyLabels.

1. Create a new module, and place the following lines in
the Declarations section:

'************************************************* ********
'Declarations section of the module.
'************************************************* *********

Option Compare Database
Option Explicit

Dim LabelBlanks&
Dim LabelCopies&
Dim BlankCount&
Dim CopyCount&


2. Type the following functions:

'================================================= =========
' The following function will cause an input box 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 ()
LabelBlanks& = Val(InputBox$("Enter Number of blank
labels to skip"))
LabelCopies& = Val(InputBox$("Enter Number of Copies to
Print"))
If LabelBlanks& 0 Then LabelBlanks& = 0
If LabelCopies& 1 Then LabelCopies& = 1
End Function

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

Function LabelInitialize ()
BlankCount& = 0
CopyCount& = 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 BlankCount& LabelBlanks& Then
R.NextRecord = False
R.PrintSection = False
BlankCount& = BlankCount& + 1
Else
If CopyCount& (LabelCopies& - 1) Then
R.NextRecord = False
CopyCount& = CopyCount& + 1
Else
CopyCount& = 0
End If
End If
End Function


3. Open the report named MyLabels in Design view and add
the following line to the OnPrint property of the detail
section:

=LabelLayout(Reports![MyLabels])


4. Add the following line to the OnOpen property of the
MyLabels report:

=LabelSetup()


5. Although typically labels do not have a report
header, add a report header and footer to the report by
clicking Report Header/Footer on the View menu. Then, add
the following line to the OnFormat property of the report
header:

=LabelInitialize()


6. Set the Height property for both the report header
and report footer to 0.

When you print the report, the report calls the
LabelSetup() function, which first asks you to enter the
number of used labels to skip on the first page
(BlankCount) and then asks how many of each label you want
printed (CopyCount).

When the report header is formatted, it calls the
LabelInitialize() function, so when you switch from preview
to print, the BlankCount and CopyCount fields are set to
zero. As each label is formatted, the LabelLayout()
function adjusts the NextRecord and MoveLayout properties
to skip used labels and to print the desired duplicates.

************************************************** ******************************************
Method 2
This code comes from FredG:

Try this code. I know it works both in preview and printing.
If this is what you are already using, or if this doesn't
work, then your problem lies elsewhere.
====
First make sure your label report is properly printing a
full sheet of
labels.

Then add a Report Header to your label report.
Add 2 text boxes to the Header.
1) Name one SkipControl
Leave it's control source unbound

2) Name the other SkipCounter
Set it control Source to =[Skip How Many?]

Now code the Report Header Format event as below:

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

Next code the Detail OnPrint event:

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

End Sub

Chris

"Billy" wrote:

I am making a database of a large video archive. In this database I need to
be able to print out the records on small MiniDV labels. This is easy now
since I print out 15 at a time on a single sheet.

But after all the existing tapes are labeled, new tapes will need to be
entered inot the database and eventually labeled. This poses a problem since
if they only need to print out 5 labels, I will have 10 labels on the page
left blank.

Is there anyway I could solve this. I was thinking about some way to prompt
the user how many spaces on the particular previously used label sheet has
been printed and then the report would put in that amount of blank records
before starting the labels that are desired.

Any information would be helpful since this is my very first database I've
ever created/worked with.

Wow, thanks!
I didn't expect to get that much info! I guess I should have found the
previiously posted article in the first place, but now i have 2 methods to
try out and hopefully I'll get it to work.

Thanks again.
Billy

"Eric Blitzer" wrote:

Here are two ways which not only prints any amount of
labels but also lets you skip the labels already used. One I got from a
previous post by Fred G.

Method 1

See article:
Skip Used Mailing Labels and Print Duplicates in Access 2000
at:
http://support.microsoft.com/default...roduct=acc2000

Printing Multiple Copies of the Same Label
When you click Print on the File menu, you can choose to
print multiple copies of the same report. But when you try
to print a single mailing label 20 times, Access prints one
label on each of 20 pages.

On a dot matrix printer, using single column labels, you
can work around this behavior by defining each label as a
separate page. However, you cannot use this method for
laser printers or multiple-column labels. To work around
this behavior, use the step-by-step procedure described below.

back to the top
Using Labels That Would Otherwise Be Wasted
After printing labels, you usually end up with a partially
used last page. There is no built-in mechanism in Access to
use the remaining labels on a partially used page. Access
always starts on a new page. On a dot matrix printer, you
can adjust the top of form manually. But you cannot do that
on laser printers. To solve this problem, use the
step-by-step procedure described below.

back to the top
Step-by-Step Procedure to Solve Both Problems
The Access report generator provides powerful hooks that
allow control over the finished product. By calling a
function from the OnFormat property of the report's detail
section, you can alter the MoveLayout, NextRecord, and
PrintSection properties to leave blank spaces or print
multiple copies on the same page. The following code is
generic. You can attach it to any Mailing Label report to
print multiple copies and to skip used labels if needed. To
use the example, you need to have a mailing label report
called MyLabels.

1. Create a new module, and place the following lines in
the Declarations section:

'************************************************* ********
'Declarations section of the module.
'************************************************* *********

Option Compare Database
Option Explicit

Dim LabelBlanks&
Dim LabelCopies&
Dim BlankCount&
Dim CopyCount&


2. Type the following functions:

'================================================= =========
' The following function will cause an input box 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 ()
LabelBlanks& = Val(InputBox$("Enter Number of blank
labels to skip"))
LabelCopies& = Val(InputBox$("Enter Number of Copies to
Print"))
If LabelBlanks& 0 Then LabelBlanks& = 0
If LabelCopies& 1 Then LabelCopies& = 1
End Function

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

Function LabelInitialize ()
BlankCount& = 0
CopyCount& = 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 BlankCount& LabelBlanks& Then
R.NextRecord = False
R.PrintSection = False
BlankCount& = BlankCount& + 1
Else
If CopyCount& (LabelCopies& - 1) Then
R.NextRecord = False
CopyCount& = CopyCount& + 1
Else
CopyCount& = 0
End If
End If
End Function


3. Open the report named MyLabels in Design view and add
the following line to the OnPrint property of the detail
section:

=LabelLayout(Reports![MyLabels])


4. Add the following line to the OnOpen property of the
MyLabels report:

=LabelSetup()


5. Although typically labels do not have a report
header, add a report header and footer to the report by
clicking Report Header/Footer on the View menu. Then, add
the following line to the OnFormat property of the report
header:

=LabelInitialize()


6. Set the Height property for both the report header
and report footer to 0.

When you print the report, the report calls the
LabelSetup() function, which first asks you to enter the
number of used labels to skip on the first page
(BlankCount) and then asks how many of each label you want
printed (CopyCount).

When the report header is formatted, it calls the
LabelInitialize() function, so when you switch from preview
to print, the BlankCount and CopyCount fields are set to
zero. As each label is formatted, the LabelLayout()
function adjusts the NextRecord and MoveLayout properties
to skip used labels and to print the desired duplicates.

************************************************** ******************************************
Method 2
This code comes from FredG:

Try this code. I know it works both in preview and printing.
If this is what you are already using, or if this doesn't
work, then your problem lies elsewhere.
====
First make sure your label report is properly printing a
full sheet of
labels.

Then add a Report Header to your label report.
Add 2 text boxes to the Header.
1) Name one SkipControl
Leave it's control source unbound

2) Name the other SkipCounter
Set it control Source to =[Skip How Many?]

Now code the Report Header Format event as below:

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

Next code the Detail OnPrint event:

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

End Sub

Chris

"Billy" wrote:

I am making a database of a large video archive. In this database I need to
be able to print out the records on small MiniDV labels. This is easy now
since I print out 15 at a time on a single sheet.

But after all the existing tapes are labeled, new tapes will need to be
entered inot the database and eventually labeled. This poses a problem since
if they only need to print out 5 labels, I will have 10 labels on the page
left blank.

Is there anyway I could solve this. I was thinking about some way to prompt
the user how many spaces on the particular previously used label sheet has
been printed and then the report would put in that amount of blank records
before starting the labels that are desired.

Any information would be helpful since this is my very first database I've
ever created/worked with.

Billy,

I also have a DVD database that I designed in ms Access. It is a little
crude but effective. I would be interested in seeing yours and I could send
you mine. If you are intersted send me an email at
g a n d ha w k @ g m a i l . com
Remove the spaces


--
Have a great Day

Chris


"Billy" wrote:

Wow, thanks!
I didn't expect to get that much info! I guess I should have found the
previiously posted article in the first place, but now i have 2 methods to
try out and hopefully I'll get it to work.

Thanks again.
Billy

"Eric Blitzer" wrote:

Here are two ways which not only prints any amount of
labels but also lets you skip the labels already used. One I got from a
previous post by Fred G.

Method 1

See article:
Skip Used Mailing Labels and Print Duplicates in Access 2000
at:
http://support.microsoft.com/default...roduct=acc2000

Printing Multiple Copies of the Same Label
When you click Print on the File menu, you can choose to
print multiple copies of the same report. But when you try
to print a single mailing label 20 times, Access prints one
label on each of 20 pages.

On a dot matrix printer, using single column labels, you
can work around this behavior by defining each label as a
separate page. However, you cannot use this method for
laser printers or multiple-column labels. To work around
this behavior, use the step-by-step procedure described below.

back to the top
Using Labels That Would Otherwise Be Wasted
After printing labels, you usually end up with a partially
used last page. There is no built-in mechanism in Access to
use the remaining labels on a partially used page. Access
always starts on a new page. On a dot matrix printer, you
can adjust the top of form manually. But you cannot do that
on laser printers. To solve this problem, use the
step-by-step procedure described below.

back to the top
Step-by-Step Procedure to Solve Both Problems
The Access report generator provides powerful hooks that
allow control over the finished product. By calling a
function from the OnFormat property of the report's detail
section, you can alter the MoveLayout, NextRecord, and
PrintSection properties to leave blank spaces or print
multiple copies on the same page. The following code is
generic. You can attach it to any Mailing Label report to
print multiple copies and to skip used labels if needed. To
use the example, you need to have a mailing label report
called MyLabels.

1. Create a new module, and place the following lines in
the Declarations section:

'************************************************* ********
'Declarations section of the module.
'************************************************* *********

Option Compare Database
Option Explicit

Dim LabelBlanks&
Dim LabelCopies&
Dim BlankCount&
Dim CopyCount&


2. Type the following functions:

'================================================= =========
' The following function will cause an input box 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 ()
LabelBlanks& = Val(InputBox$("Enter Number of blank
labels to skip"))
LabelCopies& = Val(InputBox$("Enter Number of Copies to
Print"))
If LabelBlanks& 0 Then LabelBlanks& = 0
If LabelCopies& 1 Then LabelCopies& = 1
End Function

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

Function LabelInitialize ()
BlankCount& = 0
CopyCount& = 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 BlankCount& LabelBlanks& Then
R.NextRecord = False
R.PrintSection = False
BlankCount& = BlankCount& + 1
Else
If CopyCount& (LabelCopies& - 1) Then
R.NextRecord = False
CopyCount& = CopyCount& + 1
Else
CopyCount& = 0
End If
End If
End Function


3. Open the report named MyLabels in Design view and add
the following line to the OnPrint property of the detail
section:

=LabelLayout(Reports![MyLabels])


4. Add the following line to the OnOpen property of the
MyLabels report:

=LabelSetup()


5. Although typically labels do not have a report
header, add a report header and footer to the report by
clicking Report Header/Footer on the View menu. Then, add
the following line to the OnFormat property of the report
header:

=LabelInitialize()


6. Set the Height property for both the report header
and report footer to 0.

When you print the report, the report calls the
LabelSetup() function, which first asks you to enter the
number of used labels to skip on the first page
(BlankCount) and then asks how many of each label you want
printed (CopyCount).

When the report header is formatted, it calls the
LabelInitialize() function, so when you switch from preview
to print, the BlankCount and CopyCount fields are set to
zero. As each label is formatted, the LabelLayout()
function adjusts the NextRecord and MoveLayout properties
to skip used labels and to print the desired duplicates.

************************************************** ******************************************
Method 2
This code comes from FredG:

Try this code. I know it works both in preview and printing.
If this is what you are already using, or if this doesn't
work, then your problem lies elsewhere.
====
First make sure your label report is properly printing a
full sheet of
labels.

Then add a Report Header to your label report.
Add 2 text boxes to the Header.
1) Name one SkipControl
Leave it's control source unbound

2) Name the other SkipCounter
Set it control Source to =[Skip How Many?]

Now code the Report Header Format event as below:

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

Next code the Detail OnPrint event:

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

End Sub

Chris

"Billy" wrote:

I am making a database of a large video archive. In this database I need to
be able to print out the records on small MiniDV labels. This is easy now
since I print out 15 at a time on a single sheet.

But after all the existing tapes are labeled, new tapes will need to be
entered inot the database and eventually labeled. This poses a problem since
if they only need to print out 5 labels, I will have 10 labels on the page
left blank.

Is there anyway I could solve this. I was thinking about some way to prompt
the user how many spaces on the particular previously used label sheet has
been printed and then the report would put in that amount of blank records
before starting the labels that are desired.

Any information would be helpful since this is my very first database I've
ever created/worked with.