We help IT Professionals succeed at work.

Repeat the same label all over a page

hgoyer
hgoyer asked
on
Is it possible to repeat the same label all over a page without repeating the same record in the table source.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
what do you mean? labels are controls that are not tied to a table. do you mean text boxes?

are you trying to use "continous forms"?

please clarify...

dovholuk

Author

Commented:
I have a table with enteprises names.  

I want to print address label for a specific enteprise.  
The query select the enteprise I want.  

Then I want 30 address label on the same page for that enteprise without repeating the same record in my table.
CERTIFIED EXPERT

Commented:
i'm still confused. how can you want 30 address label on the same page without repeating it?

are there 30 contacts for each enterprise???

can you put a sample of what you want here or on the net somewhere?

thanks,

dovholuk

Author

Commented:
In fact, if the query select one enteprise, you will have one label. I don't want to add 30 times the same enteprise in my table to have 30 label of the same enteprise.

I couls send you by email an exemple.
CERTIFIED EXPERT

Commented:
please do. dovholuk@yahoo.com

dovholuk

Commented:
possible in report but send it to me also..
devthaa@yahoo.com
CERTIFIED EXPERT

Commented:
well it looks to me like you are 100% correct. are you trying to print out 30 labels on one page? is that what you're trying to do?

like:

label 1 | label2 | label 3
----------------------------
label 4 | label5 | label 6
----------------------------
label 7 | label8 | label 9
----------------------------
label 10| label11| label 12
----------------------------
label 13| label14| label 15
----------------------------
label 16| label17| label 18
----------------------------
label 19| label20| label 21
----------------------------
label 22| label23| label 24
----------------------------
label 25| label26| label 27
----------------------------
label 28| label29| label 30

cause that's what you're heading towards. it looks right so far, the only problem is with your query. it selects too few records.

make sense???

dovholuk

Commented:
you have to keep on repeating the controls on the report for 30 times to print id 1 30 times as all 30 cotrols  will be bound to same record.

Author

Commented:
Dovholuk: I don't want to select more enteprise I just want to repeat the labal for the enteprise seected 30 times.

Devtha: I already did that but I wanted another solution for this problem. because you have to modify the report each time you want a different type of label.

Commented:
I am in a listening mode....
:-)

Commented:
If you need just one page, you could use a form with a white background and place the labels as needed, all referring to the same fields....

Just add a print-form button and change it's property so it won't be visible on the print.

Otherwise I would create a table with thirty rows with the same ID.
Now you update that table before printing with the ID needed and use for the report a query that's joined with this "30" table.
The report itself can be just one mainform with a subform thats having the columns set to e.g. 3.

Clear ?

Nic;o)
Commented:
http://support.microsoft.com/support/kb/articles/Q231/8/01.ASP

This is the article:


ACC2000: How to Skip Used Mailing Labels and Print Duplicates

--------------------------------------------------------------------------------
The information in this article applies to:

Microsoft Access 2000
Microsoft Visual Basic for Applications

--------------------------------------------------------------------------------
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

For a Microsoft Access 2002 version of this article, see Q299024.


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.

NOTE: This article explains a technique demonstrated in the sample file, RptSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

Q231851 ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center
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 see the following page on the World Wide Web:
http://www.microsoft.com/partner/referral/
For more information about the support options available from Microsoft, please see the following page on the World Wide Web:
http://support.microsoft.com/directory/overview.asp



MORE INFORMATION

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.
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.
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.
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&
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
Open the report named MyLabels in Design view and add the following line to the OnPrint property of the detail section:


   =LabelLayout(Reports![MyLabels])
Add the following line to the OnOpen property of the MyLabels report:


   =LabelSetup()
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()
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.
CERTIFIED EXPERT

Commented:
ok... so you DO want the EXACT same label, 30 times on one report...

i would do something along the lines that nico is thinking,
1.) i would create a table that was used for temporary storage,
2.) run a macro or vba that inserts the record you want 30 times into that table.
3.) open the report as you have it (with a different query) that referenced the "new" table
4.) when done, (or before you start) i would delete the temporary records.

the best bet would be this. use a UNION ALL query to select the same 30 records. use this as your report's rowsource:

select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]
union all select * from [QRY-Witch enteprise]

dovholuk

Commented:
Nico that is what I said to repeat controls for 30 address bound to same fields...

Commented:
Just a table with:
ID
1
1
1
..(as many rows as needed)

The query:
currentdb.execute "UPDATE ID FROM tbTempID SET ID= " & me.ID

The print query just needs a join on the tblTempID and will show 30 mathches and "add" the address when selected...

Nic;o)

Author

Commented:
Thanks to all of you

I was looking for a solution easy to adapt to all my label report.  That's why I selected that answer.

Commented:
Nico your sol would have been lot easier with a minor correction to SQL

currentdb.execute "UPDATE tbTempID SET ID= " & me!id

Commented:
True, devtha, normally I use the query editor for the syntax...

Nic;o)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.