How can I print form data in a standard letter



I have a Access2002sp3 database. Data is input via a form into a table and
sometimes (at the time of input) we need to print the data out in a standard
letter format (basically like a mail merge with a standard letter template
and data coming from the single record in the table). So far we've been
doing it by exporting the whole table to Excel and then doing a mail merge
selecting only the records that we want printed. Very tedious !!! Is there
some way I can put the standard letter into an Access report and then just
click on a control button on the input form to print the particular record's
data in the standard letter ?



Peter Hibbs


You can with a bit of Automation, I use this method all the time. I
refer to them as Cover Letters, that is a letter sent to a specific
client or supplier or whatever.

To try this out just create a new Code Module and paste the code below
into it. Check for word wrapping and correct where necessary (usually
shows as red text). Save the module as modCoverLetterCode (or whatever
you prefer, just make sure you don't use the name of any routine in
the database).

Option Compare Database
Option Explicit

Public Sub PrintLetter(vID As Long, vSource As String, vDestination As
String, vSaveName As String)

'Open Word and copy record info to Word doc
'Entry (vID) = ID of record to be printed
' (vSource) = Pathname and filename of Word document to print
' (vDestination) = Folder location in which to save document
' (vSaveName) = Name of saved document
'Exit Word document displayed on screen with fields filled in

Dim ObjWord As Word.Application
Dim rst As DAO.Recordset
Dim fld As DAO.Field

On Error GoTo ErrorCode

'Start Word and create new doc
Set ObjWord = New Word.Application
ObjWord.ScreenUpdating = False
ObjWord.Documents.Add vSource

'Copy each field data from qryCoverLetter to Word doc
Set rst = CurrentDb.OpenRecordset("SELECT * FROM qryCoverLetter
WHERE SID = " & vID)
For Each fld In rst.Fields
ReplaceText ObjWord, "[" & fld.Name & "]",
Next fld
Set rst = Nothing

'Set up save location and set Save flag false
ObjWord.ChangeFileOpenDirectory vDestination
ObjWord.ActiveDocument.Saved = False

'Set up default save filename
With ObjWord.Dialogs(wdDialogFileSummaryInfo)
.Title = vSaveName
End With

'Display Word doc and make active
ObjWord.ScreenUpdating = True
ObjWord.Visible = True
Set ObjWord = Nothing
Exit Sub

MsgBox Err.Description

End Sub

Public Sub ReplaceText(obj As Word.Application, vSource As String,
vDest As String)

'Replace all occurences of vSource with vDest in Word doc

obj.ActiveDocument.Content.Find.Execute FindText:=vSource, _
ReplaceWith:=vDest, Format:=True, _

End Sub
While you are in the code module set a reference to :-

Microsoft Word 11.0 Object Library

in Tools -> References, (actually the number there will probably be
10.0 for Access 2002).

Now create a new query and call it qryCoverLetter (or whatever). The
query should define each field from the table (or tables) that you
want to display in the Word document and should also contain a field
which can be used to define one record. For example a AutoNumber field
(or any unique field) would be used to return one record from the
query. Here is an example of a typical query from the Northwind
database for the Suppliers table.

SELECT Suppliers.SupplierID AS SID, Suppliers.CompanyName AS SCN,
Suppliers.ContactName AS SCO, Suppliers.ContactTitle AS SCT,
Replace([Address],Chr(13) & Chr(10),Chr(11)) AS SAD,
Suppliers.PostalCode AS SPC, Suppliers.Country AS SCY
FROM Suppliers;

What you should do is allocate a short code which you will embed in
the Word document for each field that you want to display. For
example, I have used SID for the SupplierID, SCN for the Company Name
and so on. You don't have to use three letter codes but it makes it
easier to design the Word document and test the code if you do. In the
case of the Address field which may have CR/LF codes embedded you will
need to replace those codes with Chr(11) (as shown above) so that it
displays correctly in the Word document. Also, if you have Date fields
or Currency fields in the query you will need to use the Format
function to display the data properly. You will probably have to
change the code above if you use a different ID code, i.e. field SID
represents the unique ID of the record, you need to replace that with
your field name or code.

Now create a Word document template which is a standard Word document
and wherever you want to display a field from the database you would
enter the control code from the query enclosed in square brackets. So
to display the Company Name in the document you would used [SCN],
something like this :-

The company name is [SCN] for this company.

When you run the routine and the document is loaded, the code above
will replace every occurrence of [SCN] with the company name from the
query and repeat that for the other codes, of course.

To display the completed Word document on screen you would add a
button to the form which is displaying the record you want to use, the
Suppliers form in this example, and then in the button Click event you
would use the code something like this :-

PrintLetter Me.SupplierID, _
"C:\Temp\Cover Letter Control Codes.doc", _
"C:\Temp", _
"Name of Document.doc"

where Me.SupplierID is the unique ID of the record to display.

"C:\Temp\Cover Letter Control Codes.doc" is the full pathname and
filename of the cover letter template document.

"C:\Temp" is the location of the folder where you would save a copy of
the completed document (although you may not want to save it but most
companies usually do).

"Name of Document.doc" is the name of the document that you want the
saved copy to have (if you need this).

Of course, in a working system you would probably have a List box on a
pop up form which shows a list of template documents that the user can
choose from and also some code to create a new different filename for
the saved copy. I usually use the template filename plus today's date
as the filename for the saved copy.

This is a very basic system but should get you started. You can add a
few bells and whistles when you have the code working. If you need any
further information then email me at (e-mail address removed)
(leave off the last 6 characters, of course).


Peter Hibbs.


I have looked at Kallal's Super Easy Word Merge and it still seems to me
that there must be an easier way of doing it. If I basically copied my
standard letter into an Access Report using the input form fields as the
standard letter fields that vary with each record, can't I simply go via a
control button to the "report" and fill it in with the last record data,
then print it ? ... Roger

John W. Vinson

I have looked at Kallal's Super Easy Word Merge and it still seems to me
that there must be an easier way of doing it. If I basically copied my
standard letter into an Access Report using the input form fields as the
standard letter fields that vary with each record, can't I simply go via a
control button to the "report" and fill it in with the last record data,
then print it ? ... Roger

That will work, but it's either limited or difficult. It works fine if the
table field data can be put in a fixed textbox on the report; the report need
not have a box around it, and its CanGrow and CanShrink properties will let it
expand or contract vertically, but if you're trying to fit a variable length
name into a paragraph of running text, it's a bit of work.

That is, you can get a paragraph like

Thank you, Pieter , for your help.

by using "Thank you, " in one Label control and ", for your help." in another,
with a textbox bound to VolunteerName in between; but to get a more natural
looking output, you must construct a concatenation of constant and variable
text in a calculated field in the query.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question