I have created a report in MS Access that serves as a shipping label. It contains a customerparts field that varies in length, so if the part# are too long and get cut off because they are longer than the text box. I was wondering if there was a way to automatically change the size of the text depending on how many characters were being displayed.

I cannot use the can grow property because it moves fields off of my label.

Because your label is a set size and you cannot set the CAN GROW properly, the only other suggestion is to make your font size smaller for that particular field in your label so that the largest value will always fit. Just make sure the font isn't TOO small to read.

If you only want the font to shrink for long numbers, you can see how long the number is by using the LEN() function on it, and then change its font for those long ones and reset it for the short ones.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Is there a way to automate this process using VBA in the On Open Event of the report.

    Dim length As Long

    length = Forms![Custom Labels]![txtCusPart].SelLength

    If length >= 15 Then
        With Me![Text19]
            .FontSize = 33
    End If

I would like to use something like above, but I can not get this to work and I am not sure if you can even do this. I need to add a few more length criteria but this would be the basis of what I would like to to.

The length comes from a form that is used to make the report which is open when the form is created.

The SelLength property is not the length of the text in the textbox of a report field. That applies to the number of characters highlighted in a textbox of a form. You want the length function or LEN().

First, at the desired larger font, see which labels get cut off and determine at which length that happens, e.g. the no. of characters is 11 or longer. So anything ten characters or less is not cut off.

Then on the ON FORMAT event for that section of the report (e.g. the detail section) you'd have

If Len(Nz(Me.txtMyTextbox, "")) > 10 Then
    Me.txtMyTextBox.FontSize = 8
Else
    Me.txtMyTextBox.FontSize = 10
End If

I just chose 8 and 10, but you can choose other numbers.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Greg
Thanks for all your help, I think its working now.
:)

Not to kibbutz, now that it is working, but one preemptive thing I have done is to create a long and short description field for a part, for example, and then use the short version for labels. It is more commonly done with state names, where short are used for mailing labels and long for letterhead.

So:

tblPart
PartID
PartDescLong - text(255)
PartDescShort - text(2)

Then you know your labels, mailing labels etc, will always be preformatted. That way you don't have to rely on luck as to where the text string is truncated. Ex:

LongDesc: Maryland
ShortDesc: MD
Truncation of Long: Maryl
No truncation of Short.

Admittedly a design issue.

HTH

HTH


mmcdonal

Look it up at: http://wrox.books24x7.com