How can I print a label a certain number of times--the number is stored in a field?

[DanicaDear]

I have a perfectly working form and report, so I don't have a problem (for a change). I desire to take something slick and make it slicker, but I don't know how.

I have a form set up which you enter a CUST_NUM (or multiple CUST_NUMs separated by commas) and the form passes those customer numbers to a report which is setup for labels to print shipping labels. The report is designed to allow the user to skip labels (that were used off the sheet previously, so you can start the printing down the sheet a ways) and also print however many copies of the label the user wants. I'm not brilliant, I got the idea and code from this link:
http://support.microsoft.com/kb/299024 I did EXACTLY what it says in this link with no changes. It works perfectly. I will post my code just in case you don't want to look at the link:

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

      Option Compare Database
      Option Explicit

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

      '==========================================================
         ' 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


Private Sub Report_Load()
DoCmd.ShowToolbar "Ribbon", acToolbarYes
End Sub

Private Sub Report_Close()
DoCmd.ShowToolbar "Ribbon", acToolbarNo
End Sub

What I desire to do is where I'm stuck. Each customer needs a different number of labels printed. The number needed is stored in tblCUSTOMERS, field [NUMBER OF BOXES]. I want to take that number stored, multiply it by 2, and have the report print the appropriate number of labels for each customer requested.
For example:
Customer 100 needs 3 boxes. The "3" is stored in the table. Print 6 labels.
Customer 101 needs 5 boxes, "5" is stored in the table, print 10 labels.
On the form that passes the CUST_NUM to the report, I want to enter "100, 101". The report will open and ask me to "Enter Number of blank labels to skip" but NOT ask user to "Enter Number of Copies to Print", and of course, print the right number of labels for every customer I put on the form.

This is way over my head. Can someone help?
THANKS THANKS THANKS!!!!!

[TheSmileyCoder]

You can modify the report to accept an openArgs argument, in your case it should be the customer ID (just 1 at a time), then instead of using inputbox to get the printvalue from the user, you could get the amount of labels by using a DLookup to your tblCustomers.



The glorified approach is to create a table, tblPrintout with 1 field, ID_Customer, to which you bind a continous form. The form could hold an unbound textbox, indicating how many labels to skip, and a bound combobox (bound to ID_Customer) with lookup to tblCustomer, allowing you to select 1 or more customers, by using a combobox (Much more failsafe then typing numbers by hand....)
You can then clear the table when the form closes or opens, depending on your preferences.

EDIT: This is not really simple either way, as it requires changes to quite a few objects, and not just the report. Sorry that I can't give a more simple solutoin.

[ADezii]

@DanicaDear - Here is how what you are requesting can be accomplished in 2 easy steps:

1. In the LabelSetup() Function, REM Out Code Lines #3 and #5 as illustrated below. You won't need them.
   

   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

2. Add the following Line of Code to the LabelLayout() Function (Code Line #2), customized to your specific Table/Field Names.
   

   Function LabelLayout(R As Report)
   LabelCopies = DLookup("[NUMBER OF BOXES]", "tblCustomers", "[CompanyName] = '" & _
                 Reports!MyLabels![CompanyName] & "'") * 2
   
   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. Logic - The LabelLayout() Function is called from the Print() Event of the Detail Section for every Record. Within this Function, you need to perform a DLookup() on the [NUMBER OF BOXES] Field based on the [CompanyName], or other Unique Identifier, in order to Print the proper Number of Labels (times 2) for each Record.
4. I've created a Demo for you that says everything, and should be very close to your existing structure. Download it, and let us know how you make out.

[NeoPa]

It seems that ADezii's (very good) solution is likely to query the table for every label. To avoid this extra overhead I've knocked up a similar version (same assumptions etc) which determines that a new customer is being processed whenever CopyCount& = 0 (but only after all the blank labels have been skipped) :

Public Sub LabelLayout(R As Report)
  With R
    .NextRecord = False
    If BlankCount& < LabelBlanks& Then
      .PrintSection = False
      BlankCount& = BlankCount& + 1
      Exit Sub
    End If

    If CopyCount& = 0 Then _
      LabelCopies = DLookup("[NUMBER OF BOXES]", _
                            "[tblCustomers]", _
                            "[CompanyName] = '" & .[CompanyName] & "'") * 2
    If CopyCount& < (LabelCopies& - 1) Then
      CopyCount& = CopyCount& + 1
    Else
      CopyCount& = 0
      .NextRecord = True
    End If
  End With
End Sub

PS. I also converted the procedure to a subroutine as there appeared to be no value returned anyway, and certainly no type defined.

PPS. Good to hear from you again Danica. Always a pleasure :-)

PPPS. Congratulations on asking a fairly complex question so clearly.

[ADezii]

@NeoPa - Excellent point, NeoPa, one which I hadn't considered.

[Rabbit]

Allow me to offer a non code approach.

tblNumbers

IDField
1
2
3
4
5
6

SELECT *
FROM tblCustomers, tblNumbers
WHERE tblCustomers.NumToPrint * 2 <= tblNumbers.IDField

[NeoPa]

Rabbit:
Allow me to offer a non code approach.

Absolutely. A clever approach too.

It will however, still need the code for the skipping of the blanks. Because of that, I'd go for the code approach (personally), but the illustration is always worth seeing.

It's good for people to see the clever sorts of things that can be done with SQL logic, and you're probably one of the best at that Rabbit. I know I've picked up a few SQL tips from you in my time.

[Rabbit]

Thanks NeoPa. I like to put out alternative approaches if I see one.

I don't know if this will work as I don't have Access at home, but maybe you could do this to skip records

SELECT tblCustomers.*
FROM tblCustomers, tblNumbers
WHERE tblCustomers.NumToPrint*2 <= tblNumbers.IDField
   AND CustomerID NOT IN
   (SELECT TOP [Enter Amount To Skip] FROM tblCustomers ORDER BY CustomerID)
ORDER BY CustomerID

[ADezii]

@Rabbit - I'm a little confused on one point, what exactly does the [IDField] in tblNumbers represent, and what should its Values be? How does this Field relate back to the [NumToPrint] Field in tblCustomers? Thanks.

[NeoPa]

SQL JOINs explains how the tables are being linked together ADezii. Look at the bit about Cartesian Products.

You may then understand why a simple table of records with values of ascending integers helps in this situation.

The WHERE clause ensures that only the correct number of records occur for each customer. This obviates the need for the code to stop at the same record for multiple labels. The [IDField] value need never be printed.

@Rabbit: I don't think that logic can work, as the blank records are not even customer related. Unless I'm missing something.

[Rabbit]

I must admit that I misunderstood what was being skipped. I suppose you could use a subquery to keep a running count but that's not what SQL was meant for. In SQL server though, there's a row_number function.

[NeoPa]

The whole recordset needs to start being processed only after all the blank labels are skipped.

It's an added flexibility to a label printing process because it's so common for sheets of labels to be half-used by a short run. To avoid wastage the program allows the operator to skip the label positions on the sheet whach have already been printed (and peeled off).

[Rabbit]

Ahh. So that's what they want. I guess you could do something like

SELECT "" AS CompanyName, etc. 
FROM tblNumbers
WHERE IDField <= [Number to Skip]
UNION
...

But that would only work if there isn't other extraneous stuff in the label.

[NeoPa]

So you could. So you could. A neat solution I feel.

It may leave the creation of the SQL to be a bit fiddly, depending on the fields involved (as they would have to come first before the real data). You'd also need to be careful to ensure the blank data presents as the same field types I expect. It could be done though. As I say, a neat SQL based solution.

[Rabbit]

The problem with it, as you say, is its inability to be flexible. But also, I'm drunk. And the drunken style SQL technique isn't always correct.