report/# label question 2

[mustangcoupe]

I have a report that I used the wizard to create. I now want to use a query as the source.. no problem. But In the query I am passing a #. This is the number of labels I need to print with this information. ( all labels will be the same) What is the best way to do this...
Also I would like to ask the user either in a form or from a pop up where to start the labels (if they are reusing a sheet) is this possiable?

--Todd


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

 

[hymn]

I got this from Ms
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

 

[mustangcoupe]

Thanks Hymn, I need to modify it a bit but I think it will work...

--Todd


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

 

[mustangcoupe]

Ok, it works (like it is suposed to...)what I have is a query for my recordsource. It passes the "wanted" values into the report... but I think my report or record source is setup wrong... I am passing in Job number, Customer Name, receiving date, and sum(quantity). (the query may have more then item passed in) the sum(quantity) is the total number of labels printed.

So

1, Acme, Inc., 10/15/2004, 15
1, Acme, Inc., 10/15/2004, 15
1, Acme, Inc., 10/15/2004, 15

may be the data from the query but, I want to print it 15 times, If I get the 15 to pass to the code above then it would print 45 of them...

--Todd


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

 

[hymn]

Have you thought of using Select Distinct or Select Distinct Row in your query

Hope this helps
Hymn

 

[mustangcoupe]

acutally no I have no experance with those I will take a look... What I ended up comming up with is that I added a unbound text field in the footer of my subform summing the quantity controls and then on the report unbound the controls I then populated them from VB (I dont close my form so on form load... control_1=form!Job_form!customer ect....)

But I will have a look at the two select statements..



--Todd


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

 

[mustangcoupe]

Select Distinct worked perfectly... thanks Hymn now I have to add a form to "select" the label to start printing on... some people may count down some people may count across.

thanks again hymn



--Todd


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

 

[hymn]

Pleasure

I never used it It was a possibility with one db but it never came off
I use the two sites below for help when I am stuck they might be worth bookmarking

http://www.kayodeok.btinternet.co.uk/favorites/kbofficeaccesshowto.htm

http://www.mvps.org/access/toc.htm

Hope this helps
Hymn