Get Access Report Line Count?

  • Thread starter Thread starter Alan Z. Scharf
  • Start date Start date
A

Alan Z. Scharf

1. Is there a way to get the total line count of a report, not just the page
count?

2. My client wants a report of varying actual length to stay within two
printed pages.

3. Hence, I have a 1-pt font reduction if report greater than two pages.

4. Sometimes, depending on length, the 1-pt font reduction is not enough.

5. I would really like to have a line count so I can step the point size
down by 2-pts depending on total line count.

Is this at all possible?

Thanks very much.

Alan
 
Alan said:
1. Is there a way to get the total line count of a report, not just the page
count?

2. My client wants a report of varying actual length to stay within two
printed pages.

3. Hence, I have a 1-pt font reduction if report greater than two pages.

4. Sometimes, depending on length, the 1-pt font reduction is not enough.

5. I would really like to have a line count so I can step the point size
down by 2-pts depending on total line count.
Click to expand...


Well, it's going to take a lot of code, but I think it's
doable. First create a text box named txtLineCnt in the
detail section. Set its control source to the expression
=1 and RunningSum property to Over All. Next, make sure you
use the report's Pages property in a page header or footer
text box (the usual = Page & " of " Pages will do).

Now add a module level variable to the declarations part of
the report's module:

Dim TotalLines As Long

And use the report Footer section's Format event procedure
to set the variable's value:

TotalLines = txtLineCnt

With all that in place, the report Header section's Format
event can use that value to determine if/how much to change
the size of the controls:

If Me.Pages > 0 Then
font size = some function of TotalLines

and all the code you'll need to set every control's FontSize
property. Use Can Shrink to make the text boxes shrink to
the font's smaller size.
 
I forgot to mention that this is a report of a number of large text fields.

It isn't a record detail form, so I can't just put a counter variable in the
detail part.

Thanks.

Alan
 
Marshall,

Thanks very much for your detailed prply.

Unfortunatley, I forgot to indicated that this was a report of 8-9 large
text boxes, not deltail lines.

Hence, I can't use running sum.

The finished report appearance more closely resembles a word document.

I was trying to get a total line count before the page prints, just as can
get a total page count before printing.

Regards,

Alan
 
Alan said:
I forgot to indicated that this was a report of 8-9 large
text boxes, not deltail lines.

The finished report appearance more closely resembles a word document.

I was trying to get a total line count before the page prints, just as can
get a total page count before printing.
Click to expand...


Well, that does clarify the problem ;-)

In this case, the first thing you need to do is download the
TextHeightWidth code at www.lebans.com. This allows you to
determine how much vertical space a textbox will require
given its Font, FontSize, etc. property settings.

You can then calculate the total height of all the text
boxes and check to see if they fit within the total
available height of two pages (less margins). If not,
reduce their font size and check again until they do fit.
I don't know enough about you report's layout to be
specific, but the general idea is along the lines of this
air code:

For lngTestSize = 10 To 4 STEP -1
textbox1.FontSize = lngTestSize
textbox2.FontSize = lngTestSize
. . .
lngTotalHgt = fTextHeight(textbox1) + _
fTextHeight(textbox1) + _
. . .
' Does it fit into 19 inches
If lngTotalHgt < 19 * 1440 Then Exit For
Next lngTotalHgt

That does not take into account any space between the text
boxes or whatever else you may have going on, so you'll
probably have to adapt it to your situation.
 
Marshall,

Thanks again for your detailed reply. I will try it out.

An even easier solution would be for me to tell my client to have their
staff reduce wordiness, but unfortunately that's not an option!

Regards,

Alan
 
Back
Top