VBA to adjust a text control font size on a report

[puforee]

In a DB I have a report that is opened from a Button. No problem with this part. How ever some fields on the report are populated from a query. I have one text field on the report called Combined2, where the text in the field can vary in length up to about 100 characters. The field is not physically long enough for the 100 characters. I cannot use the Can Grow Format because it grows down and interferes with another field. So, I want to control the size of the font. I have calculated the max length of the field size = 82 characters.

In my internet research I have encounter this code:

    If (Len([Combined2] < 82)) Then
    MsgBox Len([Combined2])
        With Me.[Combined2:]
        .SetFocus
        .FontSize = 11
        End With
     Else
        With Me.[Combined2:]
        .SetFocus
        .FontSize = 8
        End With
    
    End If

When I put this code in the Report Current event it does not work.

Is what I am trying to do even possible? If so, what report event should I use to trigger the setting of the font size.

Or,is there a better way to do this?

Thanks,

John

 

[sxschech]

It's been a while so you may need to experiment as the code goes either in the On Format or On Print event of the section where the textbox is located. Assuming it is in the detail section, you would click on the detail bar in design view. Is the colon a typo in your textbox name?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If (Len([Combined2] < 82)) Then
        Me.[Combined2].FontSize = 11
    Else
        Me.[Combined2].FontSize = 8
    End If
End Sub

 

[puforee]

sxschech

Tried your code. Compile Error "Method or data member not found". And,the compile does not like Me.[Combined2].fontSize = 11
Just in case, I am using Access 2013

Any suggestions?

Thanks John

 

[sxschech]

May not be able to get back to it till this evening. In the meantime, be sure that the textbox name on the report and the name in the code are correct (identical).

 

[sxschech]

I ran a report using one of my db files and the font size adjustment works for me. Please verify the name of the text box on the report and use that in the code. Go into design view of the report. Single click on the text box in question, bring up the properties window and look at the "OTHER" tab. If the field name is the same as the textbox name, try prefixing the textbox name with txt. For example if the field name from the query/table is called LotsOfText, then change the text box other property to be txtLotsOfText. Then in the code refer to it as me.txtLotsOfText. I'm not suggesting to use LotsOfText as the name, just for an example.