Roger,
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
DoEvents
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 & "]",
Nz(rst.Fields(fld.Name))
Next fld
rst.Close
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
.Execute
End With
'Display Word doc and make active
ObjWord.ScreenUpdating = True
DoEvents
ObjWord.Visible = True
ObjWord.Application.Activate
Set ObjWord = Nothing
Exit Sub
ErrorCode:
Beep
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, _
Replace:=wdReplaceAll
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).
HTH
Peter Hibbs.
