Re-sizing on the hoof

[DaveMere]

Hello all

I have a report set up to print out a sheet of address labels. It takes the various address fields into text boxes and then order them thus :

Name
Address1
Address2
Address3
Address4

I'm required to format the report to print out to a specific label sheet (3 x 6 on an A4 sheet). However, some address fields contain so many characters that the text wraps and over-writes the field below. I could increase the height of the text boxes, but then this would leave a gap when the field size did not take up two lines. How can I set the text box height to respond to the number of characters contained in the field?

Dave

 

[marko]

Try using "Can grow" and "Can shrink" in textbox properties.

 

[DaveMere]

Yeah, that works for individual text boxes, but causes a text box to over-write the box below it. I want to extend them as a group. Apparently this has been possible in the past

 

[suepowell]

Hi

When I am printing an address I always try to use just 1 text field, so any address lines that are not used don't leave gaps in the finished label. This is especially likely if you have fields called town, county, postcode etc.

I use the iif statement to get what I want.

In the record source of the single field I would put the following:

=[name] & Chr(13) & Chr(10) & [Address1] & IIf(IsNull([Address2]),"",[Address2]& Chr(13) & Chr(10)) & IIf(IsNull([town]),"",[town] & Chr(13) & Chr(10)) & IIf(IsNull([county]),"",[county] & Chr(13) & Chr(10)) & IIf(IsNull([postcode]),"",[postcode])

the chr(13) and chr(10) are carrage return line feed so you get a new line between each line of the address (I got this from the forum a while ago)

what the above gives you is:

name, new line
address1, newline (I assume the address always has at least one line)
if address2 has a value address2 and newline but if it is empty then nothing
repeat for all the possible address fields.

It is a bit fiddly, but once you get the method it is quite straightforward.

In your case I would use the above method and set the text box to the actual size of the label I wanted, setting the cangrow property to no as the box is already as big as it can be.

Hope this helps and doesn't put you off completely.

PS I have found that you can't take one of the text boxes you already have on the form and change it's datasource to a calculated datasource, it always gives error as the result, but copy the same expression to a new text box and it works.
This is probably a feature!

Sue.

 

[DaveMere]

That's an interesting solution.

Problem though - the characters Chr(10) and Chr(13) appear as

ĕĕ

rather than space and carriage return.

I assume I'm missing something?

Dave

 

[suepowell]

Which version of Access are you using?

I have been using 2000 and XP and the characters seems to work with them.

When I do this in access 97 I just put a carriage return in the expression and it works OK.
If you are using 97 you may need to put shift return to put a new line in the text box rather than move to the field.

Hope this helps

Sue