Variable Report Title in Access 2010

[Titan078]

We have a fundraising business and use Access as our do everything database. We currently create customized letters to send to our customers that have their schools or teams name at the top. Currently we do this in MS word with word art.



I would like to start creating these letter with Access, but Access does not support Word Art and i cant figure out how to create a variable title field. Sometimes the title (school or team name) can be as short as 12 characters and other times it could be 60 characters.

I have tried doing if them statements through VBA to adjust the text size, but you can not align where the text lays in the text box. ( If the text is small it will float in the middle of the box, but if it is large then it will Take up the whole space. I am running out of ideas. I think the next approach i might take is to define the text size, the size of the text box, and the location of the text box on the report, but i am not even sure if that is possible through VBA. Any thoughts?

[ajetrumpet]

so you're using reports to send these letters out? and if you're using textboxes to store the titles, why? they are much more difficult to work with than simple labels. one thing maybe to consider would be to use a label and just dynamically change the caption? I believe they self-expand, so worrying about resizing them would be non-existent.

[Titan078]

Thanks for the reply! I was excited to try your idea out this morning and I figured out how to dynamically change the caption of the labels, but they do not appear to be self expanding.

I am using access 2010 and if i shrink the label box down they are not expanding. I tried making the box big and they are aligning to the top. If the text aligned to the bottom it would not be a problem, but the smaller the text gets the higher it rises.

I know in Excel you define where the text is located in a cell, but i am not sure whether you can do it in Access or not.

On a side note when coming up with a if then statement to change the font size i came up with


Private Sub Report_Load()
Dim PLT As String
PLT = [ParentLetterTitle]

If Len([ParentLetterTitle]) > 64 Then
Me.Report.Label1.Caption = [ParentLetterTitle]
Me.Report.Label1.FontSize = 11
Else

If Len([ParentLetterTitle]) > 10 Then
Me.Report.Label1.Caption = [ParentLetterTitle]
Me.Report.Label1.FontSize = 66
Else

If Len([ParentLetterTitle]) > 9 Then
Me.Report.Label1.Caption = [ParentLetterTitle]
Me.Report.Label1.FontSize = 73

End If
End If
End If
End Sub


I tried to Dim [ParentLetterTitle] as PLT, but it was not working so i dropped it for right now. Is there a better way to write this? In order to figure out the font size in relation to the number of characters i went into word and typed "XXX" across the screen and changed the font size to determine the number of characters that would fit on a line. I went under the assumption that the X was the widest character in the font that i chose.

Thanks for all the help i was a psych major so most coding is foreign to me!

[ajetrumpet]

i guess I forgot to mention that the report might have to be opened in design view to get the property changed. if that is the case, you might be out of luck. I'm sure there is a workaround to this, but right now I can't think of any.

[Titan078]

Thanks for the attempt!