Labels

[Ken]

Hi,

I have a database to print labels and all is well with that as long as I
want to print all the labels. A page of labels is 4 wide and 10 down the
page. I also want to be able to query the database, select a label and print
a page of labels of that one item.

Problem - How do I set up the report, using the query, to print a page of
one item (40 labels) from the database or can I? If I just query the
database, all is get a single record and open the report, all is get is the
one label.

What I guess I have to do at this point is run the query, select the record
for the label and build a module to run and populate another table 40 times
with the selected record to enable the report to print a page of labels.

Question, once I run the query and the record from the main table is
selected, where is that data stored and how do I access it to provide the
module for populating the other table?

If there is a better way, I'm all for it.

Thanks and have the great day.

 

[Allen Browne]

Create a new table with just one field named (say) CountID, type Number.
Mark it as primary key. Save the table as tblCount.

Enter 40 records in the table (1 to 40.)

Create a query that has the table you want (the data for the labels) as well
as tblCount. There must be no line joining the 2 tables in the upper pane of
the query design window. Since there is no join, you get every possible
combination, i.e. 40 rows for each label. Save the query.

Open the report in design view, and change its RecordSource property to this
new query.

The report now prints 40 of every label. If you want 40 of one label, you
can add criteria to the query. If you want fewer labels, type the number you
want in the Criteria under the tblCount.CountID field in the grid.

 

[pietlinden]

just wondering... what happens if you do it the way MS said to do it
originally (uses events in the Report). i guess I should try it, huh?
(Gee, there's a novel concept!)

 

[Allen Browne]

You probably know that the report events do not fire reliably unless you
print/preview the entire report in order:
http://allenbrowne.com/ser-39.html

 

[Ken]

Hi Allen,
I created the table, entered 40 in the only record and added the table and
my label data table to the query. I set the record source in the report but
still only get one label. In my query, I set the criteria so I can select the
label to print and entered set the tblCount field to <=40.

I apparently missed something in the translation. Do you have a clue as to
what that may be? Thanks for your help.

Ken

 

[Allen Browne]

Does the query show a record for each label (not just a record for each or
your original records)?

Yes: Then set the RecordSource of the report to this query.

No: Switch the query to SQL View (View menu in query design), copy the SQL
statement and paste it here.

 

[Ken]

My query only shows one record--the one I requested when replying to the
criteria statement (like[enter label description]).

 

[Allen Browne]

Post the SQL statment when you get a chance.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My query only shows one record--the one I requested when replying to the
criteria statement (like[enter label description]).

Does the query show a record for each label (not just a record for each
or
your original records)?

Yes: Then set the RecordSource of the report to this query.

No: Switch the query to SQL View (View menu in query design), copy the
SQL
statement and paste it here.

 

[Ken]

Here is SQL:

SELECT CDMDept301.[Item #], CDMDept301.[CDM Description], tblCount.CountID
FROM CDMDept301, tblCount
WHERE (((CDMDept301.[Item #]) Like [Enter Item #]));

Post the SQL statment when you get a chance.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

My query only shows one record--the one I requested when replying to the
criteria statement (like[enter label description]).

Does the query show a record for each label (not just a record for each
or
your original records)?

Yes: Then set the RecordSource of the report to this query.

No: Switch the query to SQL View (View menu in query design), copy the
SQL
statement and paste it here.

Hi Allen,
I created the table, entered 40 in the only record and added the table
and
my label data table to the query. I set the record source in the report
but
still only get one label. In my query, I set the criteria so I can
select
the
label to print and entered set the tblCount field to <=40.

I apparently missed something in the translation. Do you have a clue as
to
what that may be? Thanks for your help.

Ken
:

Create a new table with just one field named (say) CountID, type
Number.
Mark it as primary key. Save the table as tblCount.

Enter 40 records in the table (1 to 40.)

Create a query that has the table you want (the data for the labels)
as
well
as tblCount. There must be no line joining the 2 tables in the upper
pane
of
the query design window. Since there is no join, you get every
possible
combination, i.e. 40 rows for each label. Save the query.

Open the report in design view, and change its RecordSource property
to
this
new query.

The report now prints 40 of every label. If you want 40 of one label,
you
can add criteria to the query. If you want fewer labels, type the
number
you
want in the Criteria under the tblCount.CountID field in the grid.


I have a database to print labels and all is well with that as long
as
I
want to print all the labels. A page of labels is 4 wide and 10 down
the
page. I also want to be able to query the database, select a label
and
print
a page of labels of that one item.

Problem - How do I set up the report, using the query, to print a
page
of
one item (40 labels) from the database or can I? If I just query the
database, all is get a single record and open the report, all is get
is
the
one label.

What I guess I have to do at this point is run the query, select the
record
for the label and build a module to run and populate another table
40
times
with the selected record to enable the report to print a page of
labels.

Question, once I run the query and the record from the main table is
selected, where is that data stored and how do I access it to
provide
the
module for populating the other table?

 

[Allen Browne]

Well, that makes sense, Ken.

The query will ask you for the [Enter Item #] when you run the query. If you
enter a value that matches something in the item # column, you should get 40
rows in the query (assuming there are 40 records in tblCount).

If [Item #] is a Number type field (not a Text field when you open
CDMDept301 in design view), choose Parameters on the Query menu (in query
design view), and declare the parameter so it matches. Use the matching data
type, e.g.:
[Item #] Long

Other than that, I can't see what else you could do to get this query
working. It is clear that you have removed the criteria on CountID, which
makes sense to get the query working.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is SQL:

SELECT CDMDept301.[Item #], CDMDept301.[CDM Description], tblCount.CountID
FROM CDMDept301, tblCount
WHERE (((CDMDept301.[Item #]) Like [Enter Item #]));

Post the SQL statment when you get a chance.

My query only shows one record--the one I requested when replying to
the
criteria statement (like[enter label description]).

:

Does the query show a record for each label (not just a record for
each
or
your original records)?

Yes: Then set the RecordSource of the report to this query.

No: Switch the query to SQL View (View menu in query design), copy the
SQL
statement and paste it here.

Hi Allen,
I created the table, entered 40 in the only record and added the
table
and
my label data table to the query. I set the record source in the
report
but
still only get one label. In my query, I set the criteria so I can
select
the
label to print and entered set the tblCount field to <=40.

I apparently missed something in the translation. Do you have a clue
as
to
what that may be? Thanks for your help.

Ken
:

Create a new table with just one field named (say) CountID, type
Number.
Mark it as primary key. Save the table as tblCount.

Enter 40 records in the table (1 to 40.)

Create a query that has the table you want (the data for the
labels)
as
well
as tblCount. There must be no line joining the 2 tables in the
upper
pane
of
the query design window. Since there is no join, you get every
possible
combination, i.e. 40 rows for each label. Save the query.

Open the report in design view, and change its RecordSource
property
to
this
new query.

The report now prints 40 of every label. If you want 40 of one
label,
you
can add criteria to the query. If you want fewer labels, type the
number
you
want in the Criteria under the tblCount.CountID field in the grid.


I have a database to print labels and all is well with that as
long
as
I
want to print all the labels. A page of labels is 4 wide and 10
down
the
page. I also want to be able to query the database, select a
label
and
print
a page of labels of that one item.

Problem - How do I set up the report, using the query, to print a
page
of
one item (40 labels) from the database or can I? If I just query
the
database, all is get a single record and open the report, all is
get
is
the
one label.

What I guess I have to do at this point is run the query, select
the
record
for the label and build a module to run and populate another
table
40
times
with the selected record to enable the report to print a page of
labels.

Question, once I run the query and the record from the main table
is
selected, where is that data stored and how do I access it to
provide
the
module for populating the other table?

 

[Ken]

Thanks Allen, I found the mistake. I initially created 40 entries in tblCount
but then got mixed up, deleted them and had one record w/40 in it.

Thanks again for your patience/professionalish and have a great week.

Well, that makes sense, Ken.

The query will ask you for the [Enter Item #] when you run the query. If you
enter a value that matches something in the item # column, you should get 40
rows in the query (assuming there are 40 records in tblCount).

If [Item #] is a Number type field (not a Text field when you open
CDMDept301 in design view), choose Parameters on the Query menu (in query
design view), and declare the parameter so it matches. Use the matching data
type, e.g.:
[Item #] Long

Other than that, I can't see what else you could do to get this query
working. It is clear that you have removed the criteria on CountID, which
makes sense to get the query working.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Here is SQL:

SELECT CDMDept301.[Item #], CDMDept301.[CDM Description], tblCount.CountID
FROM CDMDept301, tblCount
WHERE (((CDMDept301.[Item #]) Like [Enter Item #]));

Post the SQL statment when you get a chance.

My query only shows one record--the one I requested when replying to
the
criteria statement (like[enter label description]).

:

Does the query show a record for each label (not just a record for
each
or
your original records)?

Yes: Then set the RecordSource of the report to this query.

No: Switch the query to SQL View (View menu in query design), copy the
SQL
statement and paste it here.

Hi Allen,
I created the table, entered 40 in the only record and added the
table
and
my label data table to the query. I set the record source in the
report
but
still only get one label. In my query, I set the criteria so I can
select
the
label to print and entered set the tblCount field to <=40.

I apparently missed something in the translation. Do you have a clue
as
to
what that may be? Thanks for your help.

Ken
:

Create a new table with just one field named (say) CountID, type
Number.
Mark it as primary key. Save the table as tblCount.

Enter 40 records in the table (1 to 40.)

Create a query that has the table you want (the data for the
labels)
as
well
as tblCount. There must be no line joining the 2 tables in the
upper
pane
of
the query design window. Since there is no join, you get every
possible
combination, i.e. 40 rows for each label. Save the query.

Open the report in design view, and change its RecordSource
property
to
this
new query.

The report now prints 40 of every label. If you want 40 of one
label,
you
can add criteria to the query. If you want fewer labels, type the
number
you
want in the Criteria under the tblCount.CountID field in the grid.


I have a database to print labels and all is well with that as
long
as
I
want to print all the labels. A page of labels is 4 wide and 10
down
the
page. I also want to be able to query the database, select a
label
and
print
a page of labels of that one item.

Problem - How do I set up the report, using the query, to print a
page
of
one item (40 labels) from the database or can I? If I just query
the
database, all is get a single record and open the report, all is
get
is
the
one label.

What I guess I have to do at this point is run the query, select
the
record
for the label and build a module to run and populate another
table
40
times
with the selected record to enable the report to print a page of
labels.

Question, once I run the query and the record from the main table
is
selected, where is that data stored and how do I access it to
provide
the
module for populating the other table?