Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA to adjust a text control font size on a report

VBA to adjust a text control font size on a report

VBA to adjust a text control font size on a report

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:

CODE -->

If (Len([Combined2] < 82)) Then
    MsgBox Len([Combined2])
        With Me.[Combined2:]
        .FontSize = 11
        End With
        With Me.[Combined2:]
        .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?



RE: VBA to adjust a text control font size on a report

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?

CODE -->

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

RE: VBA to adjust a text control font size on a report


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

RE: VBA to adjust a text control font size on a report

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).

RE: VBA to adjust a text control font size on a report

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close