Determine the start position of each CanGrow memo field on a report when it is printed.

[DaveR]

I have a report with ten variable length memo fields. I do not want
the last memo field on each page to print if its start position is
less than 10mm from the page footer. Of course each time the report
prints, each memo field can change in height depending on the amount
of text it holds. How do I find the final print position of each memo
field on the final printed page - I need to calculate this before
preventing a field starting to print within that 10mm margin.

Any ideas would be gratefully received.

 

[Allen Browne]

Simplest way to do this would be to put the last memo into a section of its
own.

In the Sorting'n'Grouping dialog, choose a field that will change with each
record (typically the primary key), and set Yes for the Group Footer (lower
part of the dialog).
Access adds a new section to the report.
Drag the last memo field into this section.
Set the section's KeepTogether and CanGrow properties to Yes.

If there is not enough room for the whole memo field on the bottom of the
page, Access will now break the report and print the whole thing on the next
page. You can repeat the idea for the other memo fields if needed.

Because this approach gives you a new section, you could also examine the
Top property of the Report in the Format event of the Section if you did
need to know programmatically how far down the page you are at this point.

If you have ten different memo fields in one table, you may want to consider
whether it would be a better design to break them out into a separate table,
related many to one to the original one. The new table would have fields:
ID AutoNumber (primary key)
SomeID foreign key to your original table
WhatKind a field indicating the type of memo field this entry
represents
Content the actual memo field.

 

[DaveR]

Many thanks for your reply Allen. The problem is, with ten CanGrow
memo fields per record I never know which of those memo fields will be
last on the printed page; it could be Field 2 , it could be Field 10 -
depending on the amount of text in the previous memo fields. I think I
really just need a means of working out the actual START position of a
memo field as it is printed and then I can take it from there with a
page break placed programmatically just before the position of the
final memo field on the page.

Using .Top to find where the memo field starts just seems to provide
the postion of the field in the Report's design view, not its actual
final print position.

 

[Allen Browne]

Top should give you the print position on the page, but I would expect it to
give you the top of the current section, not the top of each text box.

By entering the same ID field into the sorting'n'grouping box 4 times, each
with a group header and footer, you get 8 sections, plus the detail section.
It is going to be the cleanest way.

 

[DaveR]

Thanks again Allen. That sounds a good solution if I were at the
beginning of the report design. Trouble is, I have about 12 very
"mature" reports with tons of code relating to user-defined formatting
(font,size,colour etc) being applied to different controls in
different sections - I'm not afraid of hard work but I don't have
enough time to apply your suggestions to these reports.

Anybody with any ideas on simply tracking the start print position of
a memo field in a variable length report I think would provide a
shorter route to solving this problem.

 

[Allen Browne]

AFAIK, Access does not allow you to do that.

You could possibly try some API calls to calculate the actual height needed
for the particular font metrics used on your report. Stephen Lebans does
that to simulate CanGrow on a form. If will take a great deal more work, but
you could try the approach he uses at:
http://www.lebans.com/cangrow.htm