Printing mailmerge document for single record from a form

[Clare]

I have a forum with 2 subforms containing information about external
examiners, fees, expenses and the course and college they are responsible for.

There are a number of standard letters which have to be sent out to each
external, for example confirmation that the exam report has been recieved and
the fees will be paid, request for the exam report if it's late.

Currently someone puts into the database the date the fee and expenses
request and exam report were received and how much they want. Then the
standard letter is filled in from word by hand. Reports and expense claims
come in a few at a time over a period of a few months two or three times a
year and the number is increasing rapidly. I would ideally like to automate
this somehow.

I would like to be able to create a report which fills in the externals
address, the course they are the external for, the college, the amount of fee
and the expenses they are claiming, just for the record that is showing in
the form. Some sort of a handy button for each standard letter. I have had a
browse through the internet and this forum and may have found the answer in
this code

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

My question is this. As the report will be filling in spaces in a letter is
there any way of sending this one letter to a mailmerge word document that is
fairly simple for the data inputer? As the fields course title and college
will vary considerably in length if I just use a standard access report there
could be some rather large gaps which could look strange in a letter. In a
mailmerge however everything all around would shrink or grow to accommodate
this.

Would this be a simple operation? If might have to change the layout and
text of the letter to accommodate the difference in length for the different
fields.

 

[Jeff Boyce]

Clare

Actually, you can use the control and section properties of CanGrow and
CanShrink to "close up" empty spaces in an Access-only report, just like you
would with your mail merge.

If your letter is fairly simple, and fairly standardized, you may not need
the additional formatting capabilities of Word, and could do it all in
Access reports.

Instead of creating one button per report, consider using a listbox to list
available reports, and a single command button to launch the selected
report.

 

[Clare]

Jeff,

I did have a go at the can grow but it only seems to grow vertically not
horizontally, this has made the address neater though. But I did manage to
solve this by using one text box for the body of the letter and inserting the
data in the expression builder by concatenating it all together. I had been
using separate boxes for the data and placing them at the right point over
the text box for the letter body which is where my extra white space came in.

If anyone else is searching and has the same problem this is how I solved
it, you need to use the expression builder or just type this into the text
box to insert dynamic data into a body of text whilst keeping the formatting
quite neat.

="Thank you very much for sending your claim forms for fees in connection
with your external examining for the " & [DegreeText] & " " &
[SubjectMajorText]

 

[Jeff Boyce]

Clare

CanGrow/CanShrink only apply to the vertical dimension (if they governed
horizontal, the page width could balloon out of bounds).

Concatenating text and fields together sounds like it worked in your case.

Regards

Jeff Boyce
<Access MVP>