RE: Making a report that can leave records blank based on user inp

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.aspx?scid=kb;en-us;231801&Product=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 *** 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 ***.
> >
> > 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 *** 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.
.