Text Boxes shrink/grow

[SheepDog]

I know there is a property for a text box called can shrink and can grow. My question is I have a report that has text boxes lined up horizontally and want them to grow or shrink according to the text in them because I am getting text cut off. I tried the can shrink and can grow and it made the text jump to the next line which I did not want to happen.

Any suggestions to improve this??

 

[scriverb]

Both of these properties allow for the text control to grow or shrink vertically so that no blank lines are displayed.

ACCESS Help on Can Grow property set to Yes

The section or control grows vertically so that all data it contains can be printed or previewed

ACCESS Help on Can Shrink property set to Yes

The section or control shrinks vertically so that the data it contains can be printed or previewed without leaving blank lines.

What exactly are you wanting the control to do if you have more characters than can be displayed.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SheepDog]

I want the text box to grow/shrink horizontally.

 

[scriverb]

There is aproblem there because your textbox1 may grow to 5 inches and grow over the top of textbox 2. What are you going to do about that.

The other problem is the Font that you are using in your text. If you use a fix width font you can expand the width by analyzing the number of characters and then setting the width based upon the number of "twips" that they represent. You see the width property shows up as inches but is modified in "twips" or 1/1440" per twip. This will take a little bit of manipulation on your part but the basic code for this is the following:

Me![TextBox1].width = Len(Me![TextBox1] * [red]X[/red]) + [blue]X[/blue]

This will set the width to the number of characters times the number of twips per charactere plus adding on a few at the end for a little extra white space. You would put this in the Reports OnFormat of the Detail Section event procedure.

Post back if you have more questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SheepDog]

Here is what I put is this correct?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me![Text67].Width = Len(Me![TextBox1] * X) + X
Me![Text69].Width = Len(Me![TextBox1] * X) + X
Me![Text71].Width = Len(Me![TextBox1] * X) + X
Me![Text85].Width = Len(Me![TextBox1] * X) + X
Me![Text87].Width = Len(Me![TextBox1] * X) + X
Me![Text89].Width = Len(Me![TextBox1] * X) + X
End Sub

 

[scriverb]

No, use this with the appropriate substitutions for the X's. You will have to experiment with the selected Font and the number of twips per character. The red X is you calculated number of twips per character and the blue X is the extra space that you want past the last character.

Me![Text67].Width = Len(Me![TextBox1] * [red]X[/red]) + [blue]X[/blue]
Me![Text69].Width = (Len(Me![Text69]) * [red]X[/red]) + [blue]X[/blue]
Me![Text71].Width = (Len(Me![Text71]) * [red]X[/red]) + [blue]X[/blue]
Me![Text85].Width = (Len(Me![Text85]) * [red]X[/red]) + [blue]X[/blue]
Me![Text87].Width = (Len(Me![Text87]) * [red]X[/red]) + [blue]X[/blue] 
Me![Text89].Width = (Len(Me![Text89]) * [red]X[/red]) + [blue]X[/blue]

Sorry about the bad syntax in my previous posting.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SheepDog]

Can you give me an example of what value goes in the red X and the blue X so I know where to start?

 

[SheepDog]

I am using Arial as the font and 9 as the font size

 

[SheepDog]

I used this and got a type mismatch error:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me![Text67].Width = Len(Me![Text67] * 1) + 1
Me![Text69].Width = Len(Me![Text69] * 1) + 1
Me![Text71].Width = Len(Me![Text71] * 1) + 1
Me![Text85].Width = Len(Me![Text85] * 1) + 1
Me![Text87].Width = Len(Me![Text87] * 1) + 1
Me![Text89].Width = Len(Me![Text89] * 1) + 1
End Sub

 

[scriverb]

Here are some links to help you understand the problem of expanding the width of the textbox. The fixed width font has each character taking up the same amount of space/width. The proportional font has each characters width being different depending upon the room necessary to display the font. You need to be using a fixed-width font to be able to do this.

http://www.webopedia.com/TERM/F/font.html

http://www.webopedia.com/TERM/F/fixed_pitch.html

http://www.webopedia.com/TERM/P/proportional_font.html

Try using Courier or Times New Roman fonts for this process.

Give the control a value of let's say 10 characters and then assign a value of 5000 twips to the width. See if the size of the text box is wide enough to just barely show the 10 characters. Keep working with this until you figure out the number of twips necessary for the ten characters with no white space after it. Now divide that by 10 and you have the value for your Red X in the code provided. The blue x can be 300 or whatever to give you a little extra room after the string is displayed.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

Me![Text67].Width = (Len(Me![Text67]) * 1) + 1
Me![Text69].Width = (Len(Me![Text69]) * 1) + 1
Me![Text71].Width = (Len(Me![Text71]) * 1) + 1
Me![Text85].Width = (Len(Me![Text85]) * 1) + 1
Me![Text87].Width = (Len(Me![Text87]) * 1) + 1
Me![Text89].Width = (Len(Me![Text89]) * 1) + 1

The value 1 here should be changed to the values in previously indicated that you determine. 1 is 1/1440". 1440 twips per inch.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SheepDog]

I have this code in detail section-properties-On Format


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me![Text67].Width = Len(Me![Text67] * 5000) + 10

End Sub

I keep getting type mismatch.
The textbox name is Text67
Where does the problem lie?

 

[scriverb]

You are missing a right paren:

me![Text67] = (Len(Me![Text67][red][b])[/b][/red] * 5000) + 10

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SheepDog]

Now I get an error that says overflow with this code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me![Text67].Width = (Len(Me![Text67]) * 5000) + 10

End Sub

 

[scriverb]

You have chosen to use 5000 twips for each character. Since one inche is 1440 then you are alloting 3.472 inches per character. If you have 100 characters you are trying to expand the text box to 342.7 inches. Seems like a lot. Let's initiall try 140 twips which means about 10 characters per inch.

Me![Text67].Width = (Len(Me![Text67]) * [red]140[/red]) + 10

We want to only work on a small number of characters at first. So make sure that your record only has 10 characters and let's see how it looks.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SheepDog]

Got it I understand. You sure cleared things up with the numbers since I had no idea where to start with as far as a number.

 

[scriverb]

Let me know how this works out for you.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SheepDog]

As far as the characters, the text boxes contain people's first and last name, address, city, state, e-mail address.
As you know these can change in character length greatly so how do you set this up to deal with a variety of character lengths?

 

[scriverb]

I usually don't worry about the width being perfect. Because you want to print them all on one line I would print this report out in landscape layout. Make the Left and Right Margin .3". This will give you the maximum size of the printing area. Create your controls with an approximate size that you think are necessary and leave just a little space between each control. There should be plenty of room for the fields that you thinking about.

You can run a few test queries to see the maximum number of characters for a field with a query like the following:

SELECT Top 1 A.[Name], Max(Len(A![Name])) AS NumberOfCharacters
FROM [I]yourtablename[/i]
GROUP BY A.[Name]
ORDER BY Max(Len(A![Name])) DESC;

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]