Controling Height of Control in Report Print Preview

[puforee]

I have a DB that is fed by an outside source. On a report I have captured a form as an object, and placed it in the Detail section of the report and sent it to the back. I have text box controls on the detail Section of the report located in the proper places on the detail section form. They are brought forward. This way, when the data is selected it will show in the proper location when the report is printed.

I use data that is loaded into my DB from another system. Using a main form I select, from a list, the data I want to print on the report. I then press a button to open the Report in Preview. The people who control the data from the other system have extended one item so it overflows the text control on the report.

I can set the control on the report to Can Grow and Can Shrink. But the control on the report grows down and is therefore out of position. I would need it to grow up vertically. Don't know if this can be done. Is there a way to anchor the bottom of the control so it can only size up?

I can't reduce the font to make it fit..it would be too small.

So, I thought of using code with IIF statements to check the length of data going into the control. If the length exceeds the control width I would increase the height of the control causing the text to wrap to 2 lines. I found some hints to use code attached to the report detail section - OnFormat event. I entered the code: Me.Course.Height = .3333 and then a MsgBox to indicate the event triggered. The event did trigger but when I checked the size of the control it had not changed.

There may be an easier way to do this but I am not sure what that would be.

Can anyone help?

 

[dhookom]

Can't you set the Me.Top? Also, do you understand your vba code probably uses twips not inches?

You could probably hide the control and use code in the Format event with Me.Print. You would need to set the appropriate top and left using CurrentX and CurrentY. This would not "wrap" so you might need to use two Me.Print statements.


Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

OK, I used Top and set the measurement to Twips. This code is attached to the Detail Section OnFormat Event, And it works but....another issue has popped up. What I did not mention before is that I have several more text controls in the report and these other text boxes are also moving. When I move the Course text box up from 2880 twips to 2640 twips all the other text boxes (7) on that horizontal line and below move down the same amount as Course moved up.

Here is the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
    Dim LResult As Long
    LResult = Len([Course])
    If LResult > 11 Then
        Me.Course.Top = 2640
    Else
        Me.Course.Top = 2880
    End If

End Sub

Any thoughts on this...please. Should I be attaching the code to the On Print event? I know you mentioned this above or would I get the same results?

 

[dhookom]

I doubt other controls are moving down if you move a control up. Are they actually farther down in the section if you measure them with a ruler?

I don't understand why you are using a form (is this an Access form) in a report and why you even need to move a control up as opposed to allowing it to grow down.

You can use the Me.Print if you want precise control.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

I am not really using an Access form. We have a form (not access) we use at work to collect information from out students. It has header information that is common to each class we teach. The students will fill out the rest of that form by hand after it is printed. Since we track the information the students provide in an analytical DB we need the header information to be exact for metrics. So, I imported the (not access) form into an Access report. That Access DB gathers the header information and places it on the report. The text boxes on the report are placed in such a position that the underlying (not Access) form seems to have the header information filled in. So, when this is complete the report is printed and shows with all the header information in the proper place over the back ground (Not Access) form. The instructor can then take that print out and duplicated it as many times as necessary to every student in the class has one. Going a little further, once the students have filled out the rest of the printed forms they are scanned into another DB to provide metrics.

So, in effect, I place a picture of a non access form in the back ground of an Access report. Then the DB adds the header information. I am trying to maintain the positioning of the header information on the report so when it is printed it shows in the correct location. Because one of the blocks of information (Course) has increased in length I need to move the text control so it will expand and still remain in the correct vertical position. That is what I am trying to accomplish.

I will let you know how it turns out.

 

[puforee]

OK. I took a more simple route. I created two text controls, Course and Course2. They are different vertical sizes. I set both there visible properties to No. When the report is opened, the On Format code checks the size of the text and if it is larger than 11 characters it makes the taller control show. If it is less, if makes the shorter control show. Both controls have the same data source. This works every time and is much simpler than messing with the control Top settings.

Sometimes I have to hit a brick wall before I figure out there is an open gate a few feet away.