Printing labels with subtotals - fixing size

  • Thread starter Thread starter Julia Holman
  • Start date Start date
J

Julia Holman

I am trying to design a database for a local show. I need to print labels
for the envelopes that contain the prize money. Several entrants will win
several prizes. I have made a report which sorts by entrants and lists all
the prizes they have each won and totals the amount their individual
envelopes should contain. I have got it into columns so it will print on a
sheet of labels BUT, I cant get them all the same size as they would be if
they were address labels - so they don't line up. Can anyone kindly help me
get them so they all print to the same size. I have tried to alter the
settings in page layout, and have set column height so it either is or isn't
the same as detail, but neither seems to help.

Local show is next week, help!

Julia
 
To print on labels, your printout needs to be a fixed size. For address
labels, this is usually handled by one record per label. The combined height
of the report can't exceed the height of a label. To handle this, the Can
Grow and Can Shrink of each section in the report needs to be set to No. You
can let controls within a section shrink, but not let it grow beyond the
height of the section (section can't grow) or you'll run off the bottom of
the label. If you let the section shrink, you'll finish the label too soon
and start printing the next label too high. However, if you try printing
more than one record per label, the detail section won't grow, it'll be
repeated, once for each record. This will also push you off the bottom of
the label. Are you trying to print just the subtotal or are you also
printing each person's multiple winnings as one record each?
 
This is exactly the problem! I need to print the details and the sub total.
As in: -

Mrs J Smith

(class number) 1 (Class name) Embroidery (Prize) First
£1.25
8 Knitting
Commended .25
20 Photograph
Third .50

Total
£2.00

I have got the sections set to can't grow - but they take no notice of me!
It seems to me that the problem occurs when I add the total section footer.
I have tried creating an address label and then copying my fields without
success.

Many thanks for your interest.

Julia
 
As stated in the previous message, the Detail section won't "grow" if you've
told it not to, but it will REPEAT multiple times, once of each record,
pushing you off the bottom of the label. I don't know of any way around this
behavior other than to come up with another way of displaying your data. To
fit on labels you have to be able to force the data into a restricted print
area. One possiblity would be to shrink the controls and font point size so
that the maximum possible records for each winner will fit on a single
label. If you have fewer records, you would need to print extras but with
"white" text so that they don't show. This would fill the labels for those
who didn't have the maximum number of winnings. To do this, use a query to
feed the form, add a table that has one field in it to the query. This field
should be a number field. Fill the table with the number 1 to the maximum
number of possible winnings, (i.e. 1-10). This will force 10 records for
each winner, regardless of the number of winnings. Set the textbox to be a
calculated control similar to

="Class Name " & [ClassName]
In the Format event of the Detail section, set the fore color of the textbox
to match the back color if the field is Null. It will require and Else
statement in the If statement to change it back again if the field isn't
Null.
 
Wow. We are on the edge of my abilities here! Will have a go and report
back.

Thanks

Julia

Wayne Morgan said:
As stated in the previous message, the Detail section won't "grow" if you've
told it not to, but it will REPEAT multiple times, once of each record,
pushing you off the bottom of the label. I don't know of any way around this
behavior other than to come up with another way of displaying your data. To
fit on labels you have to be able to force the data into a restricted print
area. One possiblity would be to shrink the controls and font point size so
that the maximum possible records for each winner will fit on a single
label. If you have fewer records, you would need to print extras but with
"white" text so that they don't show. This would fill the labels for those
who didn't have the maximum number of winnings. To do this, use a query to
feed the form, add a table that has one field in it to the query. This field
should be a number field. Fill the table with the number 1 to the maximum
number of possible winnings, (i.e. 1-10). This will force 10 records for
each winner, regardless of the number of winnings. Set the textbox to be a
calculated control similar to

="Class Name " & [ClassName]
In the Format event of the Detail section, set the fore color of the textbox
to match the back color if the field is Null. It will require and Else
statement in the If statement to change it back again if the field isn't
Null.

--
Wayne Morgan
MS Access MVP


Julia Holman said:
This is exactly the problem! I need to print the details and the sub total.
As in: -

Mrs J Smith

(class number) 1 (Class name) Embroidery (Prize) First
£1.25
8 Knitting
Commended .25
20 Photograph
Third .50

Total
£2.00

I have got the sections set to can't grow - but they take no notice of me!
It seems to me that the problem occurs when I add the total section footer.
I have tried creating an address label and then copying my fields without
success.

Many thanks for your interest.

Julia


No.
You
Click to expand...
bottom
of will
win lists
all
Click to expand...
print
on be
Click to expand...
Click to expand...
 
I'm afraid I am struggling with this. I have added a new table, just one
field, numbers 1-10. I have added this to the query (which already draws
from four tables, and, as you said it would, it replicates each record in my
query to a total of 10. The records are identical except for the number from
the new table. When I now go to the report, they all appear there, thus each
prize appears 10 times and the total is multiplied by 10.

I understand exactly what your idea is, and think it could work, but I need
surely to get rid of the prize amounts on the new (white) records, leaving
only the real (black) ones and don't see how to do that without weeding them
all out by hand. There will be too many winners and prizes for that to be
worth doing.

I am also out out my depth in the colouring business. I'm afraid I don't
know about Else statements and If statements. Am entirely ready to learn if
it is not too tedious to tell me how to do it.

Am feeling rather dim! Sorry

Julia


Wayne Morgan said:
As stated in the previous message, the Detail section won't "grow" if you've
told it not to, but it will REPEAT multiple times, once of each record,
pushing you off the bottom of the label. I don't know of any way around this
behavior other than to come up with another way of displaying your data. To
fit on labels you have to be able to force the data into a restricted print
area. One possiblity would be to shrink the controls and font point size so
that the maximum possible records for each winner will fit on a single
label. If you have fewer records, you would need to print extras but with
"white" text so that they don't show. This would fill the labels for those
who didn't have the maximum number of winnings. To do this, use a query to
feed the form, add a table that has one field in it to the query. This field
should be a number field. Fill the table with the number 1 to the maximum
number of possible winnings, (i.e. 1-10). This will force 10 records for
each winner, regardless of the number of winnings. Set the textbox to be a
calculated control similar to

="Class Name " & [ClassName]
In the Format event of the Detail section, set the fore color of the textbox
to match the back color if the field is Null. It will require and Else
statement in the If statement to change it back again if the field isn't
Null.

--
Wayne Morgan
MS Access MVP


Julia Holman said:
This is exactly the problem! I need to print the details and the sub total.
As in: -

Mrs J Smith

(class number) 1 (Class name) Embroidery (Prize) First
£1.25
8 Knitting
Commended .25
20 Photograph
Third .50

Total
£2.00

I have got the sections set to can't grow - but they take no notice of me!
It seems to me that the problem occurs when I add the total section footer.
I have tried creating an address label and then copying my fields without
success.

Many thanks for your interest.

Julia


No.
You
Click to expand...
bottom
of will
win lists
all
Click to expand...
print
on be
Click to expand...
Click to expand...
 
You may be able to handle the color business by using Conditional
Formatting. With the report open in design mode, right click the text boxes
that you will want to hide if there isn't a value for them and choose
conditional formatting. Set the text color to match the background color if
there is no data (i.e. Null). If there will be some text boxes on that row
that show data (so they themselves aren't Null) you can set the Conditional
Formatting to an Expression to refer to the value of the control that will
have a Null value.
 
This is turning into a saga!

Have discovered conditional formatting - thank you - and got it to work.

BUT - I still have the problem that I described in my last message. All the
records in the query that were produced by the addition of the new table
(which has no relationships or joins, which I assume is right) are identical
to the first, "real" record (except for the number generated by the new
table), so the conditional formatting applies/disapplies to all of them. (I
am using Access 2000.) I can't ,obviously, change the data in the query to
delete the prize amount or class name in order to produce a null that will
trigger the conditional formatting. Anyway, going through the data in order
manually to alter each new record would be impractical. I don't see how I
can distinguish the new records from the old ones.

Or perhaps..... each record has 9 matches which are numbered 2-9, whereas
the "real" record is numbered 1. So if I can

1. Get the query (or a new query?) to return a zero prize amount where the
number is 2-9, but leave the actual prize amount in where the number is 1
2. Get the report to conditionally format all the text boxes [Class Number]
[Class Name] [Prize] [Prize Amount] to white where the number is 2-9

Then I have done it haven't I?

Is this possible? I can see that I would have to do something more
complicated than conditional formatting to get all those text boxes to
return white formatting when the condition was met.

Thanks for sticking with this, I will send you a pot of blackcurrant jam
from the Show... are you in the States? Perhaps a Ruskin Lace Floppy Disk
holder would be easier to post......

Julia
 
Will you zip up the file and send it to me at (e-mail address removed)? Attach a
note on where I should look.
 
Ok, I probably started you down the wrong path. I modified the report using
modifications to this KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;299024&Product=acc2002#2

It will limit the detail to 10 and only 10 lines, regardless if the winner
has more or fewer records than that. I've set the Fore Color of the controls
to match the Back Color for the rows that you don't want to see. The total
height of your report is now the height of the group header + 10 * the
height of the detail section + the height of the footer. This equation will
need to be the same height as the label's height in order for everything to
fit properly.

Normally, when printing labels such as this, you would go across then down,
but with there being multiple records for each label, this won't work.
You'll have do go down then across. At the moment, you will get about 5.52
labels vertically ((page height - margins)/label height). Since this isn't a
whole number, the sizing isn't correct, but will also depend on the size of
your labels.

Hopefully, this will give you an idea that you can adjust to fit the labels
you are using. I compacted the file before I zipped it, so it's down to 48k
if you're on your dial-up connection.


--
Wayne Morgan
MS Access MVP


Julia Holman said:
This is turning into a saga!

Have discovered conditional formatting - thank you - and got it to work.

BUT - I still have the problem that I described in my last message. All the
records in the query that were produced by the addition of the new table
(which has no relationships or joins, which I assume is right) are identical
to the first, "real" record (except for the number generated by the new
table), so the conditional formatting applies/disapplies to all of them. (I
am using Access 2000.) I can't ,obviously, change the data in the query to
delete the prize amount or class name in order to produce a null that will
trigger the conditional formatting. Anyway, going through the data in order
manually to alter each new record would be impractical. I don't see how I
can distinguish the new records from the old ones.

Or perhaps..... each record has 9 matches which are numbered 2-9, whereas
the "real" record is numbered 1. So if I can

1. Get the query (or a new query?) to return a zero prize amount where the
number is 2-9, but leave the actual prize amount in where the number is 1
2. Get the report to conditionally format all the text boxes [Class Number]
[Class Name] [Prize] [Prize Amount] to white where the number is 2-9

Then I have done it haven't I?

Is this possible? I can see that I would have to do something more
complicated than conditional formatting to get all those text boxes to
return white formatting when the condition was met.

Thanks for sticking with this, I will send you a pot of blackcurrant jam
from the Show... are you in the States? Perhaps a Ruskin Lace Floppy Disk
holder would be easier to post......

Julia

You may be able to handle the color business by using Conditional
Formatting. With the report open in design mode, right click the text boxes
that you will want to hide if there isn't a value for them and choose
conditional formatting. Set the text color to match the background color if
there is no data (i.e. Null). If there will be some text boxes on that row
that show data (so they themselves aren't Null) you can set the Conditional
Formatting to an Expression to refer to the value of the control that will
have a Null value.

--
Wayne Morgan
MS Access MVP


in number
from thus
each weeding
them learn
if
Click to expand...
Click to expand...
 
Back
Top