How to print duplicates of one label?

[Bosve]

Hi
I have made a program for printing labels i access 2003 and now the only thing I have left is how to print multiple records of one label?. .

I have a very simple database with one table, one form and one report. I would like to have a control box in the form for how many labels I want to print. I have been searching on internet for tutorials and find some but as I am new to Access and programming the tutorials have been very difficult to follow.

 

[SteveSchapel]

Bosve,

One "rough & ready" way to do this is to make a table, just one field, Number data type, and enter a sequential set of numbers into this field, up to what you expect will be the maximum number of labels you will ever want in any print run.

Then, add this table to the query that your labels report is based on, no Joins, and add the number field from the table to the query output. And then, in the Criteria of this field, refer to the textbox on the form where you will enter the number of labels required, using syntax the equivalent of:
<=[Forms]![NameOfYourForm]![NameOfTheTextbox]

There, that should do it.

 

[Bosve]

Bosve,

One "rough & ready" way to do this is to make a table, just one field, Number data type, and enter a sequential set of numbers into this field, up to what you expect will be the maximum number of labels you will ever want in any print run.

Then, add this table to the query that your labels report is based on, no Joins, and add the number field from the table to the query output. And then, in the Criteria of this field, refer to the textbox on the form where you will enter the number of labels required, using syntax the equivalent of:
<=[Forms]![NameOfYourForm]![NameOfTheTextbox]

There, that should do it.

Can this be done without the query since the report is based on a table?

 

[DCrake]

A query is just another way of displaying a table and far more versatile. Simply create a new select query and add the original table and the numbers table as indicated by Steve. Save the query and just redirect the record source of the lables from the table to the query.

 

[Bosve]

A query is just another way of displaying a table and far more versatile. Simply create a new select query and add the original table and the numbers table as indicated by Steve. Save the query and just redirect the record source of the lables from the table to the query.

Done.

How do I "...add the number field from the table to the query output"?

 

[Bosve]

Well i managed somehow to follow instructions but the result is strange. Printing from the form doesn't work at all but if I doubleclick on my report "rptLabels" a input box comes up "Enter Parameter Value" Forms!frmLabels!HowManyLabels and there I can enter the number of labels i want and I get a report with 5 of the labels which are first in my table and all the rest of the labels i have in the table.

 

[Bosve]

Bosve,

One "rough & ready" way to do this is to make a table, just one field, Number data type, and enter a sequential set of numbers into this field, up to what you expect will be the maximum number of labels you will ever want in any print run.

Then, add this table to the query that your labels report is based on, no Joins, and add the number field from the table to the query output. And then, in the Criteria of this field, refer to the textbox on the form where you will enter the number of labels required, using syntax the equivalent of:
<=[Forms]![NameOfYourForm]![NameOfTheTextbox]

There, that should do it.

Ok now I got it almost working except it prints 5 labels of the record i want plus all the rest of the records that are in the table. And if I choose 5 labels of some other record it first prints 5 labels of the first record and then it prints 5 labels of the record i wanted?

 

[ghudson]

Check out the code I posted in the Skippping used mailing labels thread. It will do what you want.

 

[SteveSchapel]

Bosve,

I assumed you wanted multiple copies of the same label. I thought that's what we are talking about. Can you let us know whether all the lables are supposed to be individual, or if you want multiple copies of each different label, or if you want multiple copies of just a single label?

 

[Bosve]

Bosve,

I assumed you wanted multiple copies of the same label. I thought that's what we are talking about. Can you let us know whether all the lables are supposed to be individual, or if you want multiple copies of each different label, or if you want multiple copies of just a single label?

Multiple copies of just a single label, and I almost managed to do that by following your example. The issues that I have now is:

1. I can print for example 3 labels and they come in right order in the report but after those 3 labels it prints all the other labels i have in the table

2. If I want to print 3 labels from for example fifth row in my table, then it prints first 3 labels from the first row and then it prints 3 labels from the fifth row and then it prints all the rest of the labels.


Sorry about misunderstanding english is not my native language.

 

[SteveSchapel]

Bosve,

Thanks for the further explanation.

You need to have some way of designating which label you want printed.

Are you able to get the SQL view of the query that the report is now based on, and copy/paste that SQL into your reply here? THat will probably help us to understand your data a bit better.

And in addition, perhaps you could comment on how you can select the required label for printing. I suppose a common way for this to be done is that it is the record related to the current record on a form that is open at the time. Another way is for the required record to be selected in an unbound combobox on a form, and then for this combobox to be referenced in the Criteria of the report's query.

So, this is certainly achievable, and actually quite easy, but need more information in order to advise specifically.

 

[Bosve]

Bosve,

Thanks for the further explanation.

You need to have some way of designating which label you want printed.

Are you able to get the SQL view of the query that the report is now based on, and copy/paste that SQL into your reply here? THat will probably help us to understand your data a bit better.

And in addition, perhaps you could comment on how you can select the required label for printing. I suppose a common way for this to be done is that it is the record related to the current record on a form that is open at the time. Another way is for the required record to be selected in an unbound combobox on a form, and then for this combobox to be referenced in the Criteria of the report's query.

So, this is certainly achievable, and actually quite easy, but need more information in order to advise specifically.

The qryMogel from SQL view:

SELECT tblMogel.ArtikelNR, tblMogel.Namn, tblMogel.Vikt, tblMogel.Symboler, tblCount.Count
FROM tblMogel, tblCount
WHERE (((tblCount.Count)<=[Forms]![frmMogel]![HowManyLabels]));

I select the required record in the form with command buttons "back" and "forward". The form has three textboxes which are bound to the table tblMogel.

I am sure once I get it working I am going to think "was it so easy?"

 

[SteveSchapel]

Bosve,

Ok, well I am really just trying an intelligent guess here. But am I correct that the field ArtikelNR would identify the record that you want printed to the labels? If so, then we try it like this... in the design of the query, in the Criteria of the ArtikelNR column, enter like this:
[Forms]![frmMogel]![ArtikelNR]

Then try the labels again. Let's see how that goes.

 

[Bosve]

Bosve,

Ok, well I am really just trying an intelligent guess here. But am I correct that the field ArtikelNR would identify the record that you want printed to the labels? If so, then we try it like this... in the design of the query, in the Criteria of the ArtikelNR column, enter like this:
[Forms]![frmMogel]![ArtikelNR]

Then try the labels again. Let's see how that goes.

Yes the ArtikelNR is my primary key and it identifies the record I want to print.

Next problem is that if I want 4 labels of a certain record the program prints 4 labels of the record i want + 4 labels of each record I have in my table

 

[SteveSchapel]

Bosve,

Well, that means that the query is not working. You added the Criteria I suggested for the ArtikelNR, right? The form is open to the correct record? And that query is set up as the Record Source of the lables report? How are you going about producing the labels at this stage? Click a button on the form, or something else? Is the form a single view form or a continuous form? Can you either attach the database file to your reply, or else re-post the SQL of the query as you now have it?

We'll get there soon! I can't really imagine why it's not working properly, but there must be a simple explanation.

 

[Bosve]

I attached my database to the reply as I am sure it will provide you with the info you need better than I can.

 

[AccessJunkie]

Change the SQL of your query to the following:

SELECT tblMogel.ArtikelNR, tblMogel.Namn, tblMogel.Vikt, tblMogel.Symboler, tblCount.Count
FROM tblMogel, tblCount
WHERE (((tblMogel.ArtikelNR)=[Forms]![frmMogel]![Artikel nr]) AND ((tblCount.Count)<=[Forms]![frmMogel]![ArtikelNR]));


That should do it. The query will only display the record currently displayed on your form by matching the ArtikelNR field (the [ArtikelNR nr] control on your form).

When the label report opens, you'll see just that record repeated based on the number you provide.

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

 

[SteveSchapel]

Bosve,

Jeff is correct (of course!)

Note that this is the exact same advice that I gave a few posts back, where I said:
"In the design of the query, in the Criteria of the ArtikelNR column, enter like this:
[Forms]![frmMogel]![ArtikelNR]

Sorry, I thought you had done that already.

 

[Bosve]

Bosve,

Jeff is correct (of course!)

Note that this is the exact same advice that I gave a few posts back, where I said:
"In the design of the query, in the Criteria of the ArtikelNR column, enter like this:
[Forms]![frmMogel]![ArtikelNR]

Sorry, I thought you had done that already.

It works! Thank you SteveSchapel and AccessJunkie for your patience and understanding.