auto size a text box in report

[bmlimited]

In my report, I have two text fields beside each other. The one on the left is a reference to a field in a table and the one on the right contains a string with formulas. I want the field on the left to grow or shrink horizontaly with the text size and I want the field to it's right to float back and forth with it. Am I crazy or is there an answer to this?
Thanx so much in advance

 

[lupins46]

There nothing available in properties to do this automatically.
You would have to programmatically determine the length of the text and then set the width of the textbox. You would put the code in the format event procedure of which section of the report the data was being shown.

I can't follow why the left hand box is affected by anything you do to the width of the right-hand box.

 

[Glohamar]

You will only be able to do your first request as far as I know. Open the report in design view and look at the properties of the text box that you want to grow or shrink. You will see there are option for it to grow and shrink called Can Grow and Can Shrink. Just set them to yes.

Now I do not think you will be able to make the other text box float as your first one grows and shrinks, I would just put it underneath maybe.

 

[sxschech]

Here is a method that will allow the left col to increase in size horizontally (width) and the right will float back and forth with it. I put comments in the code, so hopefully it is still readable. You will need to change the hard coded values per your text box widths and space between boxes, if any. Remember to replace items in red with your actual text box names/widths. Paste this code into the code window of your report. (This was tested using Access 2000)

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    'Adjusts width of Left Col to fit text and moves second
    'col over as needed.
    'Values are in twips
    'twips = inches * 1440
    'inches = twips / 1440
    
    Dim varLeftCol As Variant
    Dim varSecondColWidth As Variant

    varLeftCol = Me.[red]txtLeftCol[/red]
    
    'Default Width of Col (Value is from Properties window)
    varSecondColWidth = [red]2685.024[/red]
    
    'Resize Left Col
    Me.[red]txtLeftCol[/red].Width = TextWidth(varLeftCol)
    
    'move Second Col over while keeping same amount of space between
    'LeftCol and Second Col -- in my test report, 288 was value of
    'space between Left Col and start of Second Col.
    Me.[red]txtSecondCol[/red].Left = TextWidth(varLeftCol) + [red]288[/red]
    
    'This next line is only necessary if you want to keep right
    'hand side of second col to remain constant rather than having
    'second col move over so that it extends beyond its existing
    'space on the right
    'Example if not used:
    '[This is the text]  [This is the box     ]
    '[This is more text than before]  [This is the box     ]
    '[Here less text than before]  [This is the box     ]
    
    'Example if used:
    '[This is the text]  [This is the box             ]
    '[This is more text than before]  [This is the box]
    '[Here less text than before]  [This is the box   ]
    
    'If you don't need, then comment out this next line
    Me.[red]txtSecondCol[/red].Width = varSecondColWidth - (TextWidth(varLeftCol) - 1440)
End Sub