Can Shrink - Can Grow

[MikeAngelastro]

Hi,

It's time for me to understand how these two properties really work.

First off, I have never seen "Can Shrink" work, but I have seen "Can Grow" work. What I usually do to get around this is to set both of these properties to true and then make the text box as short as possible. The result is that it grows if it contains any data. However, it seems that, if I make it too short, it won't grow. In the end, there appears to be a gap between upper and lower text boxes, when the text box in between has no data. It tends to look normal when all the text boxes have data.

My question is this: What other properties do I have set in order to get these two properties to work as I think they should. That is, when it is time to shrink, they shink to nothing and the upper and lower text boxes actually touch. Any thoughts?

Thanks,

Mike

 

[Pat Hartman]

In addition to setting the canShrink/CanGrow properties for individual controls, you also need to set the properties to yes for the section that holds the controls. In order for shrink to work, every control on a print line must be empty or invisible and no controls may overlap. If that is the case you will end up with

FirstName LastName
Addr1
Addr2
City, State, Zip

shrinking to three lines if Addr2 is empty.

The properties ONLY apply to the virtical space occupied by a control. They do NOT apply to horizontal space so fields such as:

Prefix FirstName LastName Suffix

will NOT shrink to

FirstName LastName

if prefix and suffix are empty.

 

[MikeAngelastro]

Thanks Pat,

Based on your reply I played with a simplified report and learned a great deal. The results were not good for the typical sales orders, invoices, POs, etc. These are examples of when bill-to and ship-to fields are side-by-side. This pretty much negates the can-grow/can-shrink properties for these types of documents.

Any tricks you know to get around this limitation?

Thanks again,

Mike

 

[Pat Hartman]

Rather than using individual fields stacked one below another in the detail section, you can concatenate the fields of address with a CrLf concatenated at appropriate places. It'll look something like:

The BillToAddress field:
=BillToCompanyName & vbCrLf & IIf(IsNull(BillToAddr2), BillToAddr1, BillToAddr1 & vbCrLf & BillToAddr2) & vbCrlf & BillToCity & ", " & BillToState & " " & BillToZIP


The ShipToAddress field:

=ShipToCompanyName ....

With the fields built this way, they will only expand the field virtically by enough lines to hold the address.

 

[Rich]

Hope I'm not stepping on your toes Pat, but shouldn't it be & Chr(13) & Chr(10) & in this case?

 

[MikeAngelastro]

Thanks Pat.

Now I am able to better format format reports.

I did bump into one more difficulty regarding these two properties. If I have two fields one above the other where one or both could be empty, the shrink does not work. The reason I have this issue is because we are using the following fields:
Supplier
Contact Name
Suite
Street
City, ST, and ZIP

Both the Contact Name and Suite fields can be empty. In this situation neither field shinks, even if both are empty.

Is this a case where I will have use your formula with the vbCRLF constant?

Mike

 

[MikeAngelastro]

Hi,

I decided to use the formula in one can-shrink/can-grow text box. It works quite well. But I find that when the text box appears on the report - due to having data to display - it pushes all of the controls below it down by the amount of its height. I guess there is no way around this feature of reports with text boxes that can shrink or grow.

By the way the vbCRLF worked just fine.

Mike

 

[MikeAngelastro]

One more comment:

The behavior I referred to in my previous reply caused one more gotcha. I had put a line on the report to show the user where to fold the document so that the complete address shows through the envelope's window. Guess what! When the text box in the address area grows, this line moves down the page and is no longer correctly located. I just got rid of the line.

Is there a way to make a line like this reliable?

Mike

 

[Pat Hartman]

You are simply going to have to play with the concatenation to get it to include just the fields that have values. You can build the string piecemeal and just append fields as necessary. Once the field is built, you can back up and remove the last two characters if they are a dangling carrage return and line feed.

The Vertical space also includes the space between the rows. So, if you have 6 rows with .1 Vertical space between each control, you end up with .5" of Vertical space even if all the rows end up empty and get shrunk out of existance. With this in mind, you want to use as few controls as necessary so you can limit the amount of non-shrinkable virtical space.

I'm glad the vbCrLf constant worked. I thought I had seen it somewhare. I prefer to use these VB and Access constants because they are easy to read and remember and you can frequently just guess at what they might be. Every time I see Chr(13) & Chr(10) I have to figure out what it means.

 

[KevinM]

Try this method....

http://support.microsoft.com/default.aspx?scid=kb;en-us;95390

 

[Rich]

Is there some reason why vbCrLf will only work in vba in my version of access?

 

[KevinM]

No, AFAIK you can't use it directly in a controlsource of a text box.

Don't know about beyond A2k though.

 

[Rich]

Which was what I thought, ' must have read the responses wrongly

 

[MikeAngelastro]

Thanks for all of your suggestions.

From now on I will use one text box that can grow and shrink as needed. I can assemble the formula for it as I assemble a SQL statement that is based on the contents of specified fields.