Trapping textbox height, after its grown

[llkhoutx]

I'm backfilling data in a report with corresponding labels (stored in text boxes) also backfilled. Data and labels are printed side by side, rather than with columns.

Label (textboxes, not real labels) sometimes take up two rows (via the CanGrow property). I want the corresponding data field to also not only grow, but appear on the second line of the data text box.

See the attached bmp for what I'm presently getting. I want the data (Wife) to appear opposite the line ending in semi-colon.

I can't trap the texbox height on the report OnFormat event after its grown. The only thing I've thought of is to calculate the length to the label and adjust the height of the data textbox accordingly.

Anyone solved this before? Any suggestions? Thank you in advance for your response.

 

[pdx_man]

Perhaps a quick and dirty way:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Len(Me.Title) > 10 Then
        Me.FirstName.top = 180
    Else
        Me.FirstName.top = 0
    End If
End Sub

Just make sure your detail section is at least twice the height of your controls.

 

[llkhoutx]

Growing is alright, but I need to trap the growth so that I can make another textbox the same height and adjust the text in it. Making both double in height doesn't solve the problem because when the label textbox (a backfilled label in a textbox) is double height, I want the corresponding data textbox double in height and with the text (always a single line) in it at the bottom of the textbox.

Thank you for your comment.

 

[pdx_man]

Uhhh ... this doesn't make both of them double in height. It physically moves the text box down a line if the text in the first textbox is longer than 10 characters ...

You could also just concatenate the two fields in one:

=[Title] & EmbedSpc(Len([Title]) & [FirstName]

Where the EmbedSpc function would put in the number of spaces required to format the other field ...

When you say backfilled ... can you explain further?

 

[llkhoutx]

I'll explain what I mean by backfill by example.

I have 79 subreports on a report, each of which has two columns of textboxes. The left column for labels, the right column is data. Each subreport also has three possible associated textboxes, one for a major heading (for description of major category of data, 4 in all), one for a minor heading description (79 in all), and one for an index counter (with major and minor counters embedded) for each subreport record, reset each time the minor heading changes. I know the actual number of rows needed for each subreport.

I fill all these textbox (237, not counting those in the subreports which probably approximate 1000 more) when OnFormat fires for the report, the left column with a label in text, only if there is data for the the adjacent right column text box; the category text box, only if the category changes, the minor heading only on changes, etc.) I run 79 queries to create the recordsets for each subreport. This allows headers, headings, counters, data rows within the whole report to collapse when to the extent that there is no data.

This is in lieu of labels on the left, as they will not collapse when there's no data on the right. That is, label fields will not skrink. My subreports cannot be columnar.

I'm virtually complete with 79 such supreports and three header text boxes for each, all of which are filled when OnFormat fires. I'm too far gone now to do it another way now. (Each header and index textbox and the subreport are each 0.05" high on the report) so that they all fit within the allowable space, growing as necessary.

I have three labels that grow to be two rows high and the data appears on the first row, I'd like it to appear on the 2nd, but only for those three.

It takes about 15 seconds to query the data and build this report (usually 25 8.5X11 pages ) on a slow PC (400 Mhz). That's not unreasonable in my mind. Yes, I know the code cycles for each page. It's still fast. I was quite surprised.

I thought and dreamed about this a month before I started on the report. The data in each subreport is so different that I cannot use a fewer number. I build most via VBA.

Your idea of concantenation would work, except I'd have to do a lot of code to get the two column (labels and data) to line up because the user can change any of the the labels in any session. There's probably a good algorithm for this, I just haven't thought of it. I'm spedning my money to get this project completed and on the market.

Thank your for your idea.

 

[pdx_man]

ok,
and the sub above won't work because of the size of the detail section?

 

[llkhoutx]

The function would work, but I'd have to do it for all the subreport textboxes, approximately 1000, counting labels, 2000 totals.

My subreports are complete now, and I have only 3 or four label text boxes that are causing the problem.

My report is published as an "rtf" file and used in MS Word. My users can change it there if they don't like it. That's 3 or 4 lines out of 1300.

Thanks again for your comments. It is an interesting problem.

I'll use your function another time.