Printing labels problem 2

[Nunina]

Hi Guys!

I have an Access app that calls a report from a form. This form contains only three fields: PO No., Part No, Labels Amount.

The report is supposed to print the amount of labels specified on the Labels Amount field. So, if

PO No     Part No    Labels Amount
1111       2222           5

then in the report I should get 5 labels for this specific PO No and Part No.

The thing is that I cannot get the report to print the labels. It just prints one of each record, instead of the amount specified in the Labels Amount.

Any help, please? My customer wants this done ASAP, and actually I'm behind schedule 2 days, just tinkering with this thing.

Thanks again, guys!!!

Nunina


Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[hymn]

I got the information off tek-tips not too sure who

SUMMARY
This article describes how to print multiple copies of the same mailing label, and how to use a partially used page where only some of the labels are available.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

back to the top
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 the 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:
2. '*********************************************************
3. 'Declarations section of the module.
4. '**********************************************************
5.
6. Option Compare Database
7. Option Explicit
8.
9. Dim LabelBlanks&
10. Dim LabelCopies&
11. Dim BlankCount&
12. Dim CopyCount&

13. Type the following functions:
14. '==========================================================
15. ' The following function will cause an input box to
16. ' display when the report is run that prompts the user
17. ' for the number of used labels to skip and how many
18. ' copies of each label should be printed.
19. '===========================================================
20.
21. Function LabelSetup ()
22. LabelBlanks& = Val(InputBox$("Enter Number of blank labels to skip"))
23. LabelCopies& = Val(InputBox$("Enter Number of Copies to Print"))
24. If LabelBlanks& < 0 Then LabelBlanks& = 0
25. If LabelCopies& < 1 Then LabelCopies& = 1
26. End Function
27.
28. '===========================================================
29. ' The following function sets the variables to a zero
30. '===========================================================
31.
32. Function LabelInitialize ()
33. BlankCount& = 0
34. CopyCount& = 0
35. End Function
36.
37. '===========================================================
38. ' The following function is the main part of this code
39. ' that allows the labels to print as the user desires.
40. '===========================================================
41.
42. Function LabelLayout (R As Report)
43. If BlankCount& < LabelBlanks& Then
44. R.NextRecord = False
45. R.PrintSection = False
46. BlankCount& = BlankCount& + 1
47. Else
48. If CopyCount& < (LabelCopies& - 1) Then
49. R.NextRecord = False
50. CopyCount& = CopyCount& + 1
51. Else
52. CopyCount& = 0
53. End If
54. End If
55. End Function

56. Open the report named MyLabels in Design view and add the following line to the OnPrint property of the detail section:
57. =LabelLayout(Reports![MyLabels])

58. Add the following line to the OnOpen property of the MyLabels report:
59. =LabelSetup()

60. 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:
61. =LabelInitialize()

62. Set the Height property for both the report header and the 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.


Hope this helps
Hymn

 

[Nunina]

Hi Hymn,

Actually, it is a report that I developed that simulates printing labels. I'm going to try your suggestion, but remember that I feed the form and the report off a table that has this information (PO No, Part No, Label Amount), since the user is the one that specifies how many labels of data he wants to print.

I'll let u know how it goes.

Thanks again.

Nunina


Issys Pouerie
Systems Support Analyst
San Juan, Puerto Rico

 

[CosmoKramer]

I know its been a while since you posted this, but, if you haven't found a solution you might want to check out this FAQ: faq703-3276

Hoc nomen meum verum non est.

 

[NorthNone]

Hymn - thanks for posting the Microsoft code. Works like a charm and saved me bunches of time searching! Have a Star!
JSouth

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[Thingol]

Hi All,

I know this has been posted some time ago, but I'm having trouble implementing this Microsoft code.

When I enter values for copyblanks that are higher than the amount of labels that fit on a page, I get an infinite number of pages. Also, the code leaves the first couple of labels empty on each page, instead of on the first page only (which is what I'd want; I usually don't have many sheets lying around that miss exactly the same amount of labels).

Any ideas on how to get this to work properly?

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[Thingol]

Hi All,

I know this has been posted some time ago, but I'm having trouble implementing this Microsoft code.

When I enter values for copyblanks that are higher than the amount of labels that fit on a page, I get an infinite number of pages. Also, the code leaves the first couple of labels empty on each page, instead of on the first page only (which is what I'd want).

I hope someone can hlep. Thanks a lot in advance.

Best regards,
Martijn Senden

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[NorthNone]

Maybe add a variable for the number of labels that fit on a regular page, deduct the skipped labels, then when the number of labels on the first page that print are exceeded (meaning you are now printing the second page or later), turn off the code that skips labels.????
It's been a while since I worked with this code and my customers only print one page at a time.
HTH
JSouth

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[Thingol]

Hi all,

Thanks JSouth. I've tried this, but it doesn't seem to work either. I have got the used label thing going well now: I used this code:

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

Public Function LabelLayout(R As Report)

[highlight]BlankCount& = BlankCount& + ((R.Page - 1) * 16)[/highlight]

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

Now I want to make the copycount thing work. When more than one copy of a record is printed, it is possible that one label for that record is one page, and the next label is on the next page. If this happens, the code restarts counting for the current record. Example:

If I would enter 10 for the amount of copies, and I would have 16 labels on a sheet, this would happen:
The first page has 10 copies of the first record, which is correct. It also has 6 labels of the second record, which is also correct. The second page should now have 4 labels of the second record, 10 labels for the 3rd record and 2 labels for the 4th record. However, using the MS code, the second page contains 10 labels for the second record and 6 for the 3rd record.

I hope this clarifies my problem. Does anyone know a solution to this problem?

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[Thingol]

Hi all,

I've solved my problem myself: the problem was in the initialize code for the form. It initialized for every page, which meant that copycount got resetted to 0 for every page. Use this code to prevent this from happening:

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

Public Function LabelInitialize()

BlankCount& = 0
If IsNull(CopyCount&) Then
   CopyCount = 0
End If

End Function

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[Thingol]

I meant report, instead of form, obviously...

Regards, Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[NorthNone]

Thanks for posting your solution back. It really helps others who read the post later.
Cheers!
JSouth

---------------------------------------
The customer may not always be right, but the customer is ALWAYS the customer.

 

[mustangcoupe]

martijn,
If the copy count is set in the module, and CopyCount& is a global variable how does it react if you try and print the report a second time right after the first one stops? Does this modification effect that?



--Todd


TechnicalUser pretending to be a programmer(shhh… the boss doesn’t know yet)

 

[Thingol]

Hm, Todd, I think you may be right. Something is still wrong!

When I run my procedure more than once, I end up getting the wrong number of labels for the first record in the query. Odd. Would you (or anyone else) happen to have an idea on how to solve this?

Thanks!

Kind regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[Thingol]

I've analyzed the problem a little more:

When I run my code, I get the correct amount of labels for each record, and the amount of records is also correct if the labels cover more than one page.

When I run the routine twice, the labels of the first record are one less than LabelCopies&. When I run it once again, the labels for the first record are two copies short. And so on.
Somehow I think the routine ends with a value for copycount that is one higher than it is when it is initialized.

Help, what's happening? I can't see what's going on.

Moreover, here's my code. I ran it using 0 for Lablelblanks and 5 for labelcopies. I ran the code using a 4 records query.

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

Public Function LabelSetup()

LabelBlanks& = Val(InputBox$("Voer het aantal labels in dat moet worden overgeslagen (als er al labels van het vel gebruikt zijn.)"))
LabelCopies& = Val(InputBox$("Hoeveel kopiën wilt u maken?"))
If LabelBlanks& < 0 Then LabelBlanks& = 0
If LabelCopies& < 1 Then LabelCopies& = 1

End Function

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

Public Function LabelInitialize()

BlankCount& = 0
If IsNull(CopyCount&) Then
   CopyCount = 0
End If

End Function

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

Public Function LabelLayout(R As Report)

BlankCount& = BlankCount& + ((R.Page - 1) * 16)

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

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[Thingol]

Hi All,

I have already solved it myself (again...)

Well, It had to do with the resetting of CopyCount&. I have removed the Initialize routine and now do the resetting in the Labelsetup function. This solves the problem. Here's the code:

'*********************************************************
'Declarations section of the module.
'**********************************************************
Option Compare Database
Option Explicit

Dim LabelBlanks&
Dim LabelCopies&
Dim BlankCount&
Dim CopyCount&
Dim R As Report


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

Public Function LabelSetup()
CopyCount& = 0
BlankCount& = 0
LabelBlanks& = Val(InputBox$("Voer het aantal labels in dat moet worden overgeslagen (als er al labels van het vel gebruikt zijn.)"))
LabelCopies& = Val(InputBox$("Hoeveel kopiën wilt u maken?"))
If LabelBlanks& < 0 Then LabelBlanks& = 0
If LabelCopies& < 1 Then LabelCopies& = 1

End Function

Public Function LabelLayout(R)

BlankCount& = BlankCount& + ((R.Page - 1) * 16)

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

Thanks for the input! I appreciate it a lot!

Best regards,
Martijn Senden.

In the Beginning there was nothing, which exploded.

--Terry Pratchett, Lords and Ladies--

 

[WillyG]

I have been working on this label printing problem and cannot get it to work properly for an Access 2002 Golf Association application I am developing. Using the original Microsoft version (found at

http://support.microsoft.com/?kbid=231801

and in the second post of this thread) the labels would print correctly as long as one printed the whole job at once. If one tried to reprint a page after the first page, the indicated number of labels would be skipped on that page. The "improved version" suggested by Thingol (Martijn Senden) looked like it should solve that problem, but for me the Print Preview looks good, but the printing process fails to skip the indicated number of labels on the first page and loses one label if two or more copies are requested. Furthermre, printing one of the pages beyond the first page results in 30 names and addresses that are not the same as those demonstrated in Print Preview.

Can anyone help me with this? Have I introduced an error somewhere? It seems to run OK and looks good in Print Preview. Just doesn't print properly.

I currently have:

OnPrint property of the detail section: =LabelLayout(Reports![rptMailingLabels]
OnOpen property of the rptMailingLabels report: =LabelSetup()

Module:

'*********************************************************
'Declarations section of the module.
'*********************************************************
Option Compare Database
Option Explicit

Dim LabelBlanks&
Dim LabelCopies&
Dim BlankCount&
Dim CopyCount&
Dim strMsg As String
Dim R As Report

' ************ Label Set-Up Function *******************

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

Public Function LabelSetup()

On Error GoTo Err_LabelSetUp

CopyCount& = 0
BlankCount& = 0
Do
	LabelBlanks& = Val(InputBox$("Enter number (0 - 29) of blank labels to skip"))
	If LabelBlanks& < 0 Then
		LabelBlanks& = 0
		Exit Do
	End If
	If LabelBlanks& < 30 Then
		Exit Do
	End If
Loop Until (LabelBlanks& < 30)
Do
	LabelCopies& = Val(InputBox$("Enter Number of Copies of Each Label to Print"))
	If LabelCopies& < 1 Then
		LabelCopies& = 1
		Exit Do
	End If
	If LabelCopies& <4 Then
		Exit Do
	End If
Loop Until (LabelCopies& <4)
Exit_LabelSetUp:
    Exit Function
Err_LabelSetUp:
        MsgBox Err.Number & ": " & Err.Description
        Resume Exit_LabelSetUp
End Function

' ***************** Label Layout Function *************

Public Function LabelLayout(R)

  BlankCount& = BlankCount& + ((R.Page - 1) * 30)  ' The last number is the number of labels per sheet
    ' The last number is the number of labels per sheet
    ' When on first page (R.Page = 1) the formula resolves to BlankCount& = 0 + (0X30) = 0
    ' When on second page (R.Page = 2) it resolves to BlankCount& = 0 + (1X30) = 30
  If BlankCount& < LabelBlanks& Then  ' On first page and Blankcount& less than number entered by user
     R.NextRecord = False  'Do not go to next record
     R.PrintSection = False  ' Do not print anything
     BlankCount& = BlankCount& + 1 ' Increment number
  Else  '  No longer at a blank label
     If CopyCount& < (LabelCopies& - 1) Then ' Print same label data until requested copies made
        R.NextRecord = False
        CopyCount& = CopyCount& + 1
     Else
        CopyCount& = 0  ' Resets copy counter for next record
     End If
  End If

End Function

My thanks to all who have struggled with this. I hope we can come up with something that works.

Bill Hayes