Solved Fit the size of a box to that of a text box in an Access report

[zelarra821]

First of all, I've asked in this section because the answer that I think might work would be with VBA.

Then, excuse me that my program is in Spanish. I will try to explain it as well as possible so that you understand me.

I am creating a report for a budget. They have passed me the design, and I have to adjust as much as possible. This is what I have so far:

As you can see, in the detail ("Detalle") I add each of the articles that will make up the budget. In this case, they are not articles, but are the characteristics ("Caracteristicas"), because they are dedicated to the sale of windows and the like. Well, in the standard budget each feature, with an associated image, is framed. But of course, I cannot know what height it will take in each case, since it depends on whether there are more or less characteristics. So I have both the detail and the characteristics as self-extending. However, I can't make the text box self-expanding.

A solution to my problem would be to know how much the characteristics text box measures at the end of the height, and give that value to the box (with a small margin, of course). This is what I have tried using the Height property of the textbox in VBA, but I output the value of the textbox height without autoextending, so it is not worth it.

Do you know how I can solve it?

Thanks.

 

[CJ_London]

perhaps this link will help

AutoSize TextBox

 

[zelarra821]

Hi. Thanks. First of all, I can't open the database because I have Access 2016. On the other hand, I'm not looking to auto-adjust a text box, but rather a box, just plain. For the text box there is a setting in the preferences, but not for the box.

 

[CJ_London]

why not just show the border for your textbox?

if you want code - probably the report detail format event it would be something like

boxcontrol.height=textcontrol.height

 

[zelarra821]

Okay, you're right. I have failed to add that detail. I can't add a border to the text box because that detail has to frame the features and an image.

Then, I cannot put textcontrol.height because it returns the height that I put it. That is, if I make it 0.556 cm high, and that text box automatically expands, that textcontrol.height returns 0.556 cm. I explain? So, I don't care either. All that I had already thought about. So I was saying if there is no way that Access tells me how much the text box is enlarged.

Thank you!

 

[Pat Hartman]

BOTH the control (textbox) and the Section need to have the AllowShrink and AllowGrow properties set to yes.

If removing the border interferes with the shrink/grow, put the border back but make it the same color as the background so you can't see it.

 

[zelarra821]

Hello Pat. The first paragraph if I understand it. In fact, I have them in yes.

Now, I don't understand what you mean by the second paragraph. Could you explain it to me again?

Thank you.

 

[Pat Hartman]

You said that you had shrink/grow correctly set but you also said that you had turned off the control's border. Therefore, having the border off, might be what causes the shrink/grow problem. So, try setting the border to solid but make the border color the same color as the section background. The border will be there but you won't see it. It's like writing in white ink on white paper.

 

[zelarra821]

Okay. Now I understand what you say. But what I don't understand is what is that for. I have tried to see how high it gives me with the active edge of the Features text box, but the color of the detail background, but it still gives me the same value as the height that I set. As I said, I have it set to 0.556cm, and it returns that value (in twips). Maybe I didn't understand why you proposed that to me.

Thank you.

 

[Deleted member 147267]

This is what I have tried using the Height property of the textbox in VBA, but I output the value of the textbox height without autoextending, so it is not worth it.

I don't understand that statement. You retrieve the textbox height (which probably only expands in print or print preview mode) thus the value is the design height, so it doesn't work because the height value you want to apply to a box (not a textbox as you seem to be saying) is the expanded value? I don't think you can do that. You said

I'm not looking to auto-adjust a text box, but rather a box, just plain

so I'm going to ignore the replies about what to do with a textbox and say that you'd need to estimate the number of lines based on characters or items that you seem to be showing in the textbox, then adjust the box height accordingly.

 

[zelarra821]

Hello Micron.

You are right.

So how do I estimate the number of lines based on the characters I display in the text box and then adjust the height of the box accordingly?

 

[Deleted member 147267]

If you're just typing away and letting the lines wrap like you would when typing in Word, then based on the font characteristics (size, bold or no) you would estimate that n characters would fit on a line. If you use the Len function you can get the number of characters (e.g. 250) and divide by the number that will fit across your textbox width (e.g.50) and estimate that there are 5 lines. You'd need to figure out the height in twips of a line (e.g. 300) and figure that the box will need to be 5 x 300 twips. This would only be a hap-hazard guess because you're likely not using a fixed width font and bold will mess it up further.

If you're inserting a line for each characteristic (?) and you know that you have 5, then you already know that you have 5 lines. times 300. You would need some kind of data count and I have no idea what setup you have for that.

It must be real important to contain the textbox and an image in some kind of frame to go to all this trouble. Perhaps you should contain these elements in a subform and let that be the "frame" that expands instead?

 

[zelarra821]

Effectively. With a subreport it is solved.

Let's see if I explain. The budget is based on two tables. One main, and one a subtable. In the main one, I collect general budget data; and in the subtable, the different concepts of the budget, with the price, the quantity, etc. That is reflected in a form with a subform. And in the report, to solve it, I have to add a subreport with the characteristics and the image, and relate that subreport to the main one through the ID of the subtable, so that it shows me a subreport for each of the concepts or characteristics. I explain?

Thank you all very much for your interest. You have helped me a lot.

 

[The_Doc_Man]

I don't even know if this works, but maybe...

When sizing the containing (outer box), make its size relative to the bottom of the text box AFTER you have populated it? Rather than trying for an absolute position.

Also, if the section can auto-expand, make the size of your border-box relative to the bottom of the section.

This is a shot in the dark. I have no idea how or even IF it will work because I have never had success with this problem myself.

 

[zelarra821]

Thanks, The_Doc_Man

 

[theDBguy]

Hi. Here's another idea that may or may not work. If you're merely putting an outer border around your textbox, I might try using a Layout and use the Gridlines to act as borders. Not exactly like this, but it should give an idea of what I mean.

Report Gridlines

I don't normally create reports in my databases because I prefer to export the data into Excel or other format, so that the user can further...

thedbguy.blogspot.com

 

[zelarra821]

Thanks, theDBguy

 

[Deleted member 147267]

If you cannot solve it with those suggestions, perhaps if you could provide an image of the desired result it would help us to help you. Or post an image and a zipped copy of the db so we have something to work with.

 

[The_Doc_Man]

Good call, DBG. I was thinking about that. I surmise the real trick will be to keep the two boxes aligned.

 

[Deleted member 147267]

zellara821 - when you have a solution from one of many proposals, it is good to say which one you accepted or if you even modified a solution. It helps others who may have the same or similar issue in the future.