Report Title with variable lengths (1 Viewer)

[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 10 characters and other times it could be 65 characters.

I have tried doing if then statements through VBA to adjust the text size, but you can not align where the text lays in the text box or label as far as i know. ( 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 or label, and the location of the text box or label on the report, but i am not even sure if that is possible through VBA. Any thoughts?

Here is a sample of the code that i am using to determine the font size. I am sure there is probably a better way so please feel free to suggest one.

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

 

[PNGBill]

You can automate the use of Word Documents and Access so Word Mail Merge gets it's data source from Access.

This gives you the best of both worlds.

We do a lot of Access Letters but they are simple business letters or Agreements and have very limited graphics.

The two methods we use is:
a. Build the letter text as fields in a query which when used as the Report Datasource becomes the reports control values.
This method is fiddly to set up but can give you a flowing document as each line is one Report Control or a number of lines is a Report Control.
The query field is quite big.

or b. join a number of Report Controls to make a line combined with Labels.
This is easier to setup but you sometimes don't have a nice line of text as a filed may be a different size each time where as your labels are the same size each time giving spaces etc.

The true way to get a quality Letter is still using Word.

 

[vbaInet]

The logic probably just wasn't correct:

Private Sub Report_Load()

    Select Case Len([ParentLetterTitle] & "")
        Case Is <= 11
            Me.Label1.Caption = [ParentLetterTitle]
            Me.Label1.FontSize = 66
        Case 11 To 64
            Me.Label1.Caption = [ParentLetterTitle]
            Me.Label1.FontSize = 73
        Case Is > 64
            Me.Label1.Caption = [ParentLetterTitle]
            Me.Label1.FontSize = 11
        Case Else
            ' Do nothing
    End Select

End Sub

Quick advice - use meaningful names for your labels.

And welcome to the forum by the way.

 

[titan078]

The logic probably just wasn't correct:

Private Sub Report_Load()

    Select Case Len([ParentLetterTitle] & "")
        Case Is <= 11
            Me.Label1.Caption = [ParentLetterTitle]
            Me.Label1.FontSize = 66
        Case 11 To 64
            Me.Label1.Caption = [ParentLetterTitle]
            Me.Label1.FontSize = 73
        Case Is > 64
            Me.Label1.Caption = [ParentLetterTitle]
            Me.Label1.FontSize = 11
        Case Else
            ' Do nothing
    End Select

End Sub

Quick advice - use meaningful names for your labels.

And welcome to the forum by the way.

Thanks! This will definitely be helpful going forward, and i appreciate the welcome. Any ideas one how to accomplish the title the way i would like?

 

[vbaInet]

Thanks! This will definitely be helpful going forward, and i appreciate the welcome. Any ideas one how to accomplish the title the way i would like?

How would you like the title?

As per the widest letter, you may find that W is wider than X.

 

[titan078]

We work with different Groups and we create a custom letter for them with the name of their group at the top. Example "Miami SHS Basketball presents:" The title length can range form 10 or so characters to 65 or so characters.

I am trying to come up with a way to create that title with access instead of the manual process that we currently do in word. We already record all of the information in our database so its just getting it into report form that is missing.

On a side note i can not get select case to work with the len function. If i put a number in the example you gave me works fine, but for some reason with the len function it wont work. I was thinking it might have to do with it being on load but i am not sure.

I thought about the W after I went trough the whole process I am hoping that it will still be ok though. I can not imagine a title using all W's making much sense

 

[vbaInet]

What error message does it display?

I think by counting you can somehow workaround it, so you're on the right path.

 

[titan078]

What error message does it display?

I think by counting you can somehow workaround it, so you're on the right path.

no error just does not work.

 

[vbaInet]

Move that code to the OnPrint event of the section the control is in.

 

[titan078]

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Select Case Len([ParentLetterTitle] & "")
Case Is <= 11
Me.PLTlbl.Caption = [ParentLetterTitle]
Me.PLTlbl.FontSize = 66
Case 11 To 64
Me.PLTlbl.Caption = [ParentLetterTitle]
Me.PLTlbl.FontSize = 73
Case Is > 64
Me.PLTlbl.Caption = [ParentLetterTitle]
Me.PLTlbl.FontSize = 11
Case Else
' Do nothing
End Select
End Sub

I moved the code into what i think is the proper area. I have attached it to make sure. I am still getting no results when i print. I have attached a picture of the report that printed with some annotations so that you know what the different areas are.

 

[titan078]

got it!


Private Sub Report_Load()
Dim Length As Integer
Length = Len([ParentLetterTitle])

Select Case Length
Case Is <= 11
Me.PLTlbl.Caption = [ParentLetterTitle]
Me.PLTlbl.FontSize = 66
Case 11 To 64
Me.PLTlbl.Caption = [ParentLetterTitle]
Me.PLTlbl.FontSize = 73
Case Is > 64
Me.PLTlbl.Caption = [ParentLetterTitle]
Me.PLTlbl.FontSize = 11
Case Else
' Do nothing
End Select
End Sub

 

[titan078]

The next step is figuring out how to do the title. The problem is that As the text gets bigger and samller it moves arround the page. If the text would align to the bottom of the label or text box i would have no issues, but as the text shrinks it rises to the middle of the text box or label.

Neither labels or text boxes self expand as far as i know. The only thing i can think of is setting their size property when i set the font size.

Any other thoughts?

 

[vbaInet]

Ok, what view are you using for this? Open it in Print Preview mode.

 

[titan078]

Ok, what view are you using for this? Open it in Print Preview mode.

Ya i opened it in print preview and even printed it to double check. Select case just was not playing nice with the len function. Once i moved the len function out of the select case everything played nicely with each other on the Onload event. (which is nicer cause i am sure that i will have to tweak settings for quite some time)

 

[vbaInet]

Textboxes have a Can Grow property but labels don't.

 

[titan078]

Private Sub Report_Load()
Dim Length As Integer
Length = Len([ParentLetterTitle])

Select Case Length
Case Is <= 11
[PTLtxt] = [ParentLetterTitle]
[PTLtxt].FontSize = 66
Case 11 To 64
[PTLtxt] = [ParentLetterTitle]
[PTLtxt].FontSize = 73
Case Is > 64
[PTLtxt] = [ParentLetterTitle]
[PTLtxt].FontSize = 11
Case Else
' Do nothing
End Select
End Sub


Trying it with a text box. I think the problem is that the text box's size gets checked before the onload function runs. If i have the on shrink property enabled the box diapers altogether, if i have the shrink property turned off its visible at its original size, but will not grow to meet the size of the text.

i appreciate your patience i know some of the questions i have are rudimentary!

 

[vbaInet]

Just to clarify, is the report going to produce severl pages each supposedly having varying lengths?

 

[titan078]

The report will look like the attached letter. We do fundraising "events" Each "event" gets a letter specifically designed for them from data that we enter into the database. So yes i Believe what you are saying is true, but I would pull up only one page at a time using the EventidPk.

 

[vbaInet]

I've just created a quick example for you and saved it in 2000 Access version. See attached.

 

[titan078]

Thanks! Its always nice to see how much more organized other people are. The problem i am running into now is that the Text box grows downward. Is there a way to adjust it so that it expands up.

There will be text directly below the title line and if the box grows downward it will lay over top of the old text.

I am not sure if i am making my self clear so ill put an example below. IF the x's were to grow they would cover "the Domino's Fundraiser!" instead of exapanding into the top margin.

xxxxxxxxxxxxxxxxxxxxxx
The Domino's Fudraiser!