mailing label report generates too many pages & too many copies

[Scott Billings]

I am trying to properly format a report to print mailing labels. I have used
the mailing label wizard to setup the initial report. I have used the code
solutions on this news group to skip used labels on a sheet and to select how
many copies of the label. I actually only need one copy of the label hen I
print, it's just the positioning of the label that is the important issue.
The report is being generated from four text boxes on a form and called when
the user clicks a command button. In the Detail section the following
provides the data:

=Trim([Forms]![frmApplicantData]![AppFirstName] & " " &
[Forms]![frmApplicantData]![AppMI] & " " &
[Forms]![frmApplicantData]![AppLastName])
=Trim([Forms]![frmApplicantData]![AppAddress])

I have the code in the report header and detail sections to ask for the
amount of labels to skip and number of copies. I enter the number to skip and
the copy amount(which is always one) then the print preview displays some 126
pages of the same label. I'm totally stumped. Please help!!!

 

[Steve Schapel]

Scott,

If you look at the Properties for the report, what do you have set there
for the Record Source property? If I understand you correctly, this
should be blank.

 

[Scott Billings]

It is blank. As shown in the two trim statements, the record source is from
the fields on a form. I even th\ried using the code in the example from MS
Knowlege Base# 231801. It still produces the same results. --- 125 pages of
the same label.

Scott,

If you look at the Properties for the report, what do you have set there
for the Record Source property? If I understand you correctly, this
should be blank.

--
Steve Schapel, Microsoft Access MVP

I am trying to properly format a report to print mailing labels. I have used
the mailing label wizard to setup the initial report. I have used the code
solutions on this news group to skip used labels on a sheet and to select how
many copies of the label. I actually only need one copy of the label hen I
print, it's just the positioning of the label that is the important issue.
The report is being generated from four text boxes on a form and called when
the user clicks a command button. In the Detail section the following
provides the data:

=Trim([Forms]![frmApplicantData]![AppFirstName] & " " &
[Forms]![frmApplicantData]![AppMI] & " " &
[Forms]![frmApplicantData]![AppLastName])
=Trim([Forms]![frmApplicantData]![AppAddress])

I have the code in the report header and detail sections to ask for the
amount of labels to skip and number of copies. I enter the number to skip and
the copy amount(which is always one) then the print preview displays some 126
pages of the same label. I'm totally stumped. Please help!!!

 

[Steve Schapel]

Scott,

I have never done what you are trying here, with the data for the label
reading directly off form controls, so I am not sure. The next thing I
would try is to use a simple, one record table/query as the Record
Source for the report, and put a hidden control bound to this
table/query on the report Detail. Should force just a single label.
See how that goes.

 

[Scott Billings]

I can certaintly give that a try. I've been trying to run the mailing label
report that way because when the label is needed, it's ususlly because the
user is in that specific record and needs to create a mailing label to
respond to a job applicant. You see I wrote this database for our HR
department so they can track job applicants, job postings and such. Now, if I
were to use a one record table, at the command button on the form that calls
the mailing label report, would I have it populate the table just before
calling the report? When you say a "hidden" control, what are you referring
to? Is it similar to the trim statement?

 

[Steve Schapel]

Scott,

I can certaintly give that a try. I've been trying to run the mailing label
report that way because when the label is needed, it's ususlly because the
user is in that specific record and needs to create a mailing label to
respond to a job applicant.

I had made the assumption that the form controls you were referring to
in your labels report are *unbound* controls. It now appears I may be
wrong about this. If there is an actual data record shown on the form,
and these are bound controls, then I would recommend another approach.
I assume that the table that contains these records has a Primary Key
field of some sort. For our purposes, I will assume it is named
ApplicantID. I would make a query, based on this table, with the
equivalent of this in the Criteria of the ApplicantID filed...
[Forms]![frmApplicantData]![ApplicantID]
And I would make this query as the Record Source of the report.
That way, your address control on the report can be bound directly to
the AppAddress field in the query, and the name control can have its
Control Source like...
=[AppFirstName] & " " & [AppMI] & " " & [AppLastName]

... Now, if I
were to use a one record table, at the command button on the form that calls
the mailing label report, would I have it populate the table just before
calling the report?

No, This can be a "nonsense" table, just one field and one record, if
you like, it doesn't need to do anything except provide a "dummy" record
source for the report.

When you say a "hidden" control, what are you referring
to? Is it similar to the trim statement?

No, I mean set the Visible property of the textbox to No.

 

[Scott Billings]

Thanks for your input Steve. I managed to figure it out. I created a one
record table. Next I created an update query that sets the fields in the one
record table to blank. Then I created another update query that sets the
fields in the table to the values of the text boxes on the form-- First, MI,
Last and Address. I then created a mailing label report that simply uses the
values of the fields in the one record table. I did use the code from the
knowledge base article (KB231801). I set all the values just as instructed in
the article with one exception-- I eliminated the references to asking how
many copies are needed because the amount is always 1. On the command button
of the form to call the print the mailing lable, on the click event I had it
run the two queries then the report. The use us only asked how many blank
labels to skip and then the label shows up in print preview only once like
should and in the correct positon. The user then onlt needs to be sure they
have a sheet of labels in the manual ffed tray of the printer and print the
requested label.

All is well!! Thanks

Scott,

I can certaintly give that a try. I've been trying to run the mailing label
report that way because when the label is needed, it's ususlly because the
user is in that specific record and needs to create a mailing label to
respond to a job applicant.

I had made the assumption that the form controls you were referring to
in your labels report are *unbound* controls. It now appears I may be
wrong about this. If there is an actual data record shown on the form,
and these are bound controls, then I would recommend another approach.
I assume that the table that contains these records has a Primary Key
field of some sort. For our purposes, I will assume it is named
ApplicantID. I would make a query, based on this table, with the
equivalent of this in the Criteria of the ApplicantID filed...
[Forms]![frmApplicantData]![ApplicantID]
And I would make this query as the Record Source of the report.
That way, your address control on the report can be bound directly to
the AppAddress field in the query, and the name control can have its
Control Source like...
=[AppFirstName] & " " & [AppMI] & " " & [AppLastName]

... Now, if I
were to use a one record table, at the command button on the form that calls
the mailing label report, would I have it populate the table just before
calling the report?

No, This can be a "nonsense" table, just one field and one record, if
you like, it doesn't need to do anything except provide a "dummy" record
source for the report.

When you say a "hidden" control, what are you referring
to? Is it similar to the trim statement?

No, I mean set the Visible property of the textbox to No.

 

[Steve Schapel]

Excellent, Scott. I feel obliged to say that this is way more
complicated than it needs to be, but I am glad to know it's working for
you now.