Shrik to fit or AutoResizeFont

[saqassemi]

Hello, I have a report that has some textboxes with fixed size but some values are bigger than textboxes. so I find a code that change the size of fonts but there is a problem with empty fields.
I attached a sample file that has two report, the first run good but the second has an error because of empty field.
Please help me with this problem! Thanks

 

[isladogs]

You seem to be doing the Access equivalent of using a hammer to crack a nut.

Why not just increase the width of your textbox.

If you really want to do this, enclose the code section with

If Len(strText)>0 Then

Do Until code here....

End if

Tested and it works

However, I think the approach is flawed as you will see if you enter a very long text string of say 200 characters.
The text will fit but the font size will be so small (1pt?) as to be unreadable

 

[James Dickinson]

First set the can grow property of the text box on the report to true then your vba code should have "NZ(strText)" which will, in the case that you have null value in your table, set the text value as vbnullstring instead and your problem will go away.

i.e. Do Until TextHeight(Nz(strText)) < ctl.Height - (ctl.Height * 0.26)

AND Do Until TextWidth(Nz(strText)) < ctl.Width

Alternatively simply make the report text field "eName" larger by stretching it out in design mode

 

[saqassemi]

You seem to be doing the Access equivalent of using a hammer to crack a nut.

Why not just increase the width of your textbox.

If you really want to do this, enclose the code section with

If Len(strText)>0 Then

Do Until code here....

End if

Tested and it works

However, I think the approach is flawed as you will see if you enter a very long text string of say 200 characters.
The text will fit but the font size will be so small (1pt?) as to be unreadable

Thanks Ridders
Shrink to fit is a useful option for fixed table designs. I know can shrink/grow in textbox option but many people don't like to change the fixed design for a few records that are longer than usual.
If you surf the internet for this option you see many people have the same question with no answer. Thanks for your answer
I attached the file with adding your code to solve the problem for other people that search on google to download.

 

[isladogs]

You're welcome.

Using Nz would work equally well either in the way suggested by James Dickinson or using

If Nz(strText,"")<>"" Then

Do Until ....

End If

 

[The_Doc_Man]

The problem is that for reasons only known to the Access Gods, there is no form equivalent to the report ability to compute the size of a given text string if you supply the font name and font size. It is entirely possible that they use something from the printer driver's bag a tricks to determine this information. The problem of auto-sizing fonts has come up in this forum many times but the answer usually comes down "make a guess and just swing with it."

I'm pretty sure that if you use a non-proportional font, you make your problem harder. So that means that Courier and Lucida Console will work well, but any font that supports kerning is going to make your computation a living Hell.

 

[arnelgp]

Here is the modified version. Test it.

 

[James Dickinson]

Or limit the character length that can be entered for the report to something reasonable and you won't have to do any resizing at all