Shrinking text boxes and pictures?!?! help please 1

[DarkOne72]

I have been trying to find the answer to this for awhile and no luck.There are 2 questions I have:

1) I have a report that is created based on a search criteria. Once it finds the data it merges it in with a pre-set letter if you will. The problem is that someof the words that come back are longer then others so I have to have enough room to show the whole word and it fits nicely, but if a small word is found there is a big gap between them and it looks crappy.

EXAMPLE: You live in FERNANDINA BEACH, FL 32056
(this one is okay)
You live in STARKE , FL 32223
(this isn't, theres the gap)

The CAN GROW and CAN SHRINK DOESN't WORK EVEN WHEN I PRINT IT. I saw something that someone had and it was like a mail merge but didn't understand how to use anything out of it.

2) How do you make a picture go behind the text without making it a background picture. I want to just insert a picture and make it behind text.

Thanks for your help...
DarkOne

 

[DarkOne72]

Okay, I have finally gotten the pictures to work right. The only thing left now is to find out how to keep from having the gaps between my text boxes depending on the data that is retrieved.
Please someone help!

DarkOne

 

[BillPower]

Can you post the Code and or SQL that bring these Fields together ("Merge&quot. Sounds like you might need to use Trim, LTrim or RTrim to lose these spaces. Also, the Format and type of Field the above is in, in your Report.

Regards

Bill

 

[JoyInOK]

The following workaround will likely need to be tweaked as far as the numbers, which represent the position of the controls on the report. I use it to properly format an envelope based on the length of the text in the given field. It works better with a fixed-width font like Courier (where an 'm' is the same width as an 'i') than with a variable-width font like Times New Roman. I use the Tag property of the controls to cycle through them.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


Dim ctlControl As Control
Dim blnGotCareOfAddress As Boolean
blnGotCareOfAddress = False
If ([C/O Address]) <> vbNullString Then
blnGotCareOfAddress = True
End If
For Each ctlControl In Controls
If ctlControl.Tag = &quot;ClaimantFirst&quot; Then
ctlControl.Width = (Len(ctlControl.Text) * 144) + 144
Dim intNameRight As Integer
intNameRight = ctlControl.Left + ctlControl.Width
End If
If ctlControl.Tag = &quot;ClaimantLast&quot; Then
ctlControl.Left = intNameRight
ctlControl.Width = (Len(ctlControl.Text) * 144) + 144
intNameRight = ctlControl.Left + ctlControl.Width
End If
If ctlControl.Tag = &quot;Suffix&quot; Then
ctlControl.Left = intNameRight

End If

If ctlControl.Tag = &quot;Address&quot; Then
If blnGotCareOfAddress = True Then
ctlControl.Top = 3500
Else
ctlControl.Top = 3250
End If
End If

If ctlControl.Tag = &quot;City&quot; Then
If blnGotCareOfAddress = True Then
ctlControl.Top = 3750
Else
ctlControl.Top = 3500
End If

ctlControl.Width = (Len(ctlControl.Text) * 144) + 144
Dim intCityRight As Integer
intCityRight = ctlControl.Left + ctlControl.Width
End If
If ctlControl.Tag = &quot;State&quot; Then
If blnGotCareOfAddress = True Then
ctlControl.Top = 3750
Else
ctlControl.Top = 3500
End If

ctlControl.Left = intCityRight
End If

If ctlControl.Tag = &quot;Zip&quot; Then
If blnGotCareOfAddress = True Then
ctlControl.Top = 3750
Else
ctlControl.Top = 3500
End If

ctlControl.Left = intCityRight + 576
End If


Next
End Sub

If you really want a nice looking letter based on your data from Access, check out the FAQ on Automating Word Mail Merge From Access. Word automatically adjusts field lengths and suppresses blank lines in addresses.

 

[DarkOne72]

Ok, do I just change like the address and city and all to the boxes I have on there?
Also, where do I put this long code? I am not good on that portion of it.

Thanks for the help, I really appreciate it.

Rick

 

[DarkOne72]

I tried it and its not working, still all the same size like before, maybe I did something wrong though cause I am not sure what to exactly do, but I changed some of the fields like compliant or something. Myabe a little more description if you don't mind.

Thanks again.

Rick

 

[BillPower]

Hi DarkOne72,

Strange why you couldn't just post details as requested, then carry on the Thread as if I didn't exist. Your problem lies in merging your data, not the Format of the Report.

My email is billpower@cwcom.net , send a copy of your DB (without any sensitive data) over the weekend and I'll have a look at it, posting any suggestions here.

Bill

 

[JoyInOK]

DarkOne72, if Bill hasn't already fixed you up, did you go through each control and give it a descriptive Tag (on the Other tab in Properties), then adjust the code to your tags and your control names?
Also, let's add some error trapping so we'll get a message about what's wrong. Add this line just before the first Dim statement

On error goto ErrorHandler

And these two lines just before End Sub

Exit Sub
ErrorHandler:
MsgBox &quot;Error # &quot; & Err.Num & &quot; occurred. &quot; & Err.Description, vbokonly, &quot;Error&quot;

 

[BillPower]

Hi Rick,

I took on a lot more Threads than it turned out I could handle this weekend. Am just having some dinner and will look at yours and a couple of others that I've promised to look at and get back to you later on.

Have you tried JoyInOK's last suggestion.

Finally, can you talk me through the sequence of opening and running this Report. Not sure if I've got something missing in the DB you sent. I can't see any SQL or code that brings the &quot;Address&quot; fields together.

Bill

 

[DarkOne72]

Bill,

Once the switchboard loads, goto the &quot;search database&quot; and then just hit the button on right that says &quot;Submit Search to goto Print Reminder Cards&quot; it will bring up the reoprt that I am having trouble with.
As far as the other suggestion, No I haven't done that because there isn't actually an error an d there isn't any sql in that area for what I am doing that I know of. Its just that I don't know how to make it to where the fields automatically adjust themselves to fit side by side from wording instead of having a big gap in between them.

Thanks for everyones help,

Rick

 

[Ymesei]

Rick,

Am I right in assuming that you have each field from your record displayed in a separate control on your report with the static text as text boxes in between the records? If that's the case, short of JoyInOk's elegant solution Access can't adjust the position of controls to the left and right based on field size.

Your best bet would be to merge all your fields together with the static text and have it all display in one control that CanGrow.

For example, create a textbox with the record source as

=(&quot;You live in &quot; & [yourCity] & &quot;, &quot; & [yourState] & &quot;.&quot

Another way to do it would be to have the SQL source of your report return the body of your letter as a single string with all the records merged in already. Then just create a single textbox with the single field as the record source.

HTH,
Will

 

[BillPower]

Hi Rick,

Ymesei has hit it on the head, but doesn't have the advantage of seeing your Report to give an exact example.

Leave the Customer and Address as it is unless you want them to end with a Comma, if so then change:

Customer Control Source to:
=[CustomerName] & IIf(Not IsNull([CustomerName]),&quot;, &quot
Adds a comma as long as there is a Customer Name.

Address Control Source to:
=[Address] & IIf(Not IsNull([Address]),&quot;, &quot
Adds a comma as long as there is a Address.

Consolidate the City, State and Zip like this:
=[City] & IIf(Not IsNull([City]),&quot;, &quot & [State] & [ZipCode]
Adds a comma after City as long as there is a City .

This will make Addresses come out like this:

Rick Rielli,
123 Any Road,
Fernandina Beach, FL 32224

I think you might be a little confused over the Can Grow and Can Shrink Properties, these apply to the Height of the Control, not the Width.

I will Email you back your DB, I have highlighted the new Controls in Blue.

Well done to Ymesei for your suggestion.

Regards

Bill

 

[BillPower]

Sorry JoyInOK,

Well done for your code too. I don't know if Rick tried it, but I'm sure it would work if he doesn't like the other suggestions.

Good Luck

Bill

 

[DarkOne72]

You guys are the best! I want to thank you all for your help, everything works great! Special thanks to Bill for doing what he did and Ymesei for the awesome suggestion!
I could not have done it without you all.

Rick

 

[JoyInOK]

'Twas a team effort, and I'm sure that anyone having the same problem will get lots of good ideas from this thread. A star for Ymesei; I had forgotten that I'd used the concantonation technique before, too, but only on a problem involving a recordset that could have from one to twenty records.

One more tip to add in there: if you're concantonating and want to put the entire address in the same field for use in merging to a Word document (as I've had to do to properly format a legal document requiring the parties address to be on one side of the page and the case number on the other side of the page, seperated by a vertical column of )'s, you can insert the Chr(9) for tabs and Chr(13) for carriage returns. Looks like weird symbols in your Access field, but properly formats in Word.