Text box that won't shrink or grow 1

[Corsica]

In the 'detail' part of one of my forms I have placed one text box. I've set the form property 'default view' to 'continuous forms' and set both the 'detail' and text box properties 'can shrink' & 'can grow' to 'yes'.

But when I look at the form in form view I find that the text boxes do not grow such that long text strings that are seen.

Because the idea is that all of the information that must be presented be seen after the form is opened, without the user having to use the keyboard, if I'm not able to solve this 'problem' than I'll have to restrict the text string length that can be put into the relevant field. This will have far reaching implications...

Does anyone have any ideas regarding how I might solve this 'problem'?

Thanks
Corsica

 

[Eupher]

CanGrow and CanShrink only apply when the form is printed or displayed in print preview, not in form view.

Ken S.

 

[Eupher]

What you want to do can be done, but not with continuous forms. Except when you use of conditional formatting (which doesn't have the flexibility to do the job you want), changes made to a control on a continuous form are reflected in all the records.

Your best bet is to set the font of the textbox to a monospaced font like Courier New - that way you can easily determine exactly how many characters will fit on one line of the textbox. Then you'll have to create a formula that increases or decreases the height of the textbox according to multiples of the length of one line. If you don't want the user to have to scroll up and down in the form, you'll also need to add/subtract this multiplier to the windowheight, insideheight, and maybe detail section of the form. Give me a few minutes and I may be able to hack together a bit of code for you...

Ken S.

 

[Eupher]

Corsica,

Here's a link to some code that will do what you want.

http://www.lebans.com/cangrow.htm

Ken S.

 

[Corsica]

Thanks Ken, for your helpful replies.

The only possible problem with your suggested solution is that then I'll have quite a lot of space between the text strings that are much shorter than the longest one in the set.

-Am I thinking along the right lines...?

Thanks again.
Corsica

 

[Eupher]

Not at all. The code from the link I posted will dynamically resize the textbox and the detail section to fit the amount of text in the field.

Then you'll have to create a formula that increases or decreases the height of the textbox

I think Mr. Lebans' code is actually a bit of overkill - it uses API calls to generate a textbox and detail section height based on the font being used. But it works quite well and is a very good example of the technique.

Ken S.

 

[Eupher]

Corsica,
I misspoke a bit in my previous post. You would indeed need to dynamically change the size of the form itself if you don't want lots of empty space on the form. The Lebans code doesn't do that. But here's a bit of code from a similar problem that may get you pointed in the right direction:

Dim intMainHeaderHeight As Integer
Dim intMainDetailHeight As Integer
Dim intMainFooterHeight As Integer
Dim intSubHeaderHeight As Integer
Dim intSubDetailHeight As Integer
Dim intSubFooterHeight As Integer
Dim intNewMainDetailHeight As Integer
Dim intOldSfrmHeight As Integer
Dim intNewSfrmHeight As Integer
Dim intSfrmDiff As Integer
Dim Rs As Recordset

intSubHeaderHeight = Me!frmSubForm.Form.Section(acHeader).Height
intSubDetailHeight = Me!frmSubForm.Form.Section(acDetail).Height
intSubFooterHeight = Me!frmSubForm.Form.Section(acFooter).Height

intMainHeaderHeight = Me.Section(acHeader).Height
intMainDetailHeight = Me.Section(acDetail).Height
intMainFooterHeight = Me.Section(acFooter).Height

intOldSfrmHeight = Me!frmSubForm.Height

Set Rs = Me.frmSubForm.Form.RecordsetClone

If Rs.RecordCount < 11 Then
    intNewSfrmHeight = (intSubDetailHeight * Rs.RecordCount) + _
    intSubHeaderHeight + intSubFooterHeight
    Else
        intNewSfrmHeight = (intSubDetailHeight * 10) + _
        intSubHeaderHeight + intSubFooterHeight
        Me!frmSubForm.Form.ScrollBars = 2
End If
Set Rs = Nothing
intSfrmDiff = intOldSfrmHeight - intNewSfrmHeight
Me!frmSubForm.Height = intNewSfrmHeight
intNewMainDetailHeight = intMainDetailHeight - intSfrmDiff
Me.InsideHeight = intMainHeaderHeight + intNewMainDetailHeight + intMainFooterHeight

This example resizes a subform control as well as the main form depending on the number of records in the subform's recordset. But the same principle could be applied to a form and textbox control pretty easily. You just have to work out the height metric for the textbox control.

HTH,

Ken S.

 

[Corsica]

Thanks very much! But won't each text box have the same height?

Corsica

 

[Eupher]

No! That's the whole point - to dynamically resize the height based on how much data is in the field.

Ken S.

 

[Eupher]

Corsica,

I've played around with this a bit and I'm starting to believe that the Lebans solution might be the best. It certainly is possible to dynamically resize both the textbox *and* the form, but from a user's point of view this can be really annoying - because the navigation buttons move with the size of the form. So as the form changes size, you're constantly having to move the mouse around to find the record navigation buttons - a real drag if you're trying to move through the records quickly. So I've come back to Lebans example, where he chooses a form size that works for most instances, then if the field size grows beyond that limit, scroll bars come into play.

Ken S.

 

[Corsica]

Hi, I need to show, on the one form, the contents of one column of data, each datum in a text box, that's why I had used 'continuous forms'. I can see that the code above may help me to dynamically set the size of the form, detail section and objects on the form, for example a text box. But I have one text box having one record source. Thus I don't see how I can display the contents of the one column of data contained in the underlying table without using 'continuous forms' and if I use continuous forms then how can resizing the text box 'on_open' or 'on_load' solve the problem?

I'm confused.
Corsica

 

[Eupher]

Hi, Corsica,

As I said in an earlier post, I don't think you can dynamically size your textbox if you're using continuous forms - a change in one instance of the form affects all. Conditional formatting gives you some options in that regard (backcolor, forecolor, bold, etc.), but does not extend to resizing controls.

If you must see all the rows within a single view (like a spreadsheet) you could use a single form, but dynamically add unbound textboxes to the form of the proper size, and programatically fill them with the data. Would require lots of code, I'm afraid.

Ken S.

 

[Corsica]

Thanks.

I think that I would want to write the required code, and it would add a new level of interest for me, if you get what I mean.

But, would you please tell me where you would be placing such code. Would it be 'on_load' or 'on_open' of the form, or somewhere else?

Thanks again.
Corsica

 

[Eupher]

Either event should work.

Ken S.