Hi Dougie.
I have posted the code below for you to look at but I'm just wondering
if you are making life more difficult for yourself than it needs to
be.
As I understand it, you have a table with a list of buildings and you
want to send a report (i.e. your form created in Word) to the owner of
a building when it catches fire. Would it not be easier to create an
Access report, with the relevant fields which looks similar to your
Word form, and then just print, e-mail or fax the report to the client
(I believe it is possible to fax a report although I have never tried
it myself). This would be a lot easier than trying to do it via Word,
or maybe I'm missing something.
Anyway, in your database I suggest you create a new form called
frmLetter (or whatever) and add a button called cmdPrint as well as
any other buttons, text boxes, etc that are required. Open the form's
code module and paste the code below into the module (watch out for
any line wrapping errors, i.e. lines that show in red). Make sure that
the references include the DAO 3.6 and Word Object libraries (see the
code for more details).
Create a Word document and save it to disk (I used C:\Temp\Test
Letter.doc for this example). You need to allocate a unique code for
each field that you want to show in the document, for example, you
could use [FN] for the FirstName field, [LN] for the LastName field
and so on. I normally use the square brackets around each code because
it is unlikely that you would use these characters in a letter. Now
write your standard letter in Word, placing these codes where you need
fields from the table.
In the Click event of the cmdPrint button enter the following code :-
PrintLetter ID, "C:\Temp\Test Letter.doc"
The ID value should be the unique ID of the client record, i.e. an
AutoNumber field for example. You could pass this value to the
frmLetter form from your main form using the OpenArgs variable.
The second parameter is obviously the full path and filename of your
Word document. Normally you would have some sort of list box on the
form which allows you to select a letter template from a list of
documents but I would 'hard wire' this value to start with.
In the code below substitute the table name (tblMembers) for your
table that holds the client info. Also substitute the field names and
control codes that you have allocated in the record set code.
When you click the frmPrint button the code will load Word and then
replace any control codes in the document with the field values from
the table. You can then print or fax the document as normal. One piece
of advice, if anything goes wrong while you are testing it out, always
make sure that Word has closed properly by checking for an occurrence
of WINWORD.EXE in the Windows Task Manager.
If you have any date fields in the document you can use the Format
function to change the date format. For example -
ReplaceText objWord, "[DB]", Format(Nz(rst!DoB),"dd mmmm yyyy")
Here is the code :-
'===================
Public Sub PrintLetter(vID As Long, vFilename As String)
'Print personalised letter using MS Word
'Entry (vID) holds unique ID of record to be printed
' (vFilename) holds path & filename of document to be printed
'Ensure that the following references are active in Tools >
References...
'Microsoft DAO 3.6 Object Library
'Microsoft Word xx.0 Object Library (xx = your version of Word)
Dim objWord As Word.Application
Dim rst As Recordset
On Error GoTo ErrorCode
'Start MS Word and open specified document
Set objWord = New Word.Application 'create instance of Word
objWord.Documents.Add vFilename 'open document (vFilename)
objWord.ScreenUpdating = False 'turn off screen updates
'Fetch data for specified record from table (i.e. tblMembers) and copy
to Word doc
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblMembers WHERE
ID = " & vID)
ReplaceText objWord, "[FN]", Nz(rst!FirstName)
ReplaceText objWord, "[LN]", Nz(rst!LastName)
ReplaceText objWord, "[TP]", Nz(rst!Telephone)
ReplaceText objWord, "[FX]", Nz(rst!Fax)
ReplaceText objWord, "[DB]", Nz(rst!DoB)
ReplaceText objWord, "[TD]", Date
rst.Close
Set rst = Nothing
objWord.ActiveDocument.Saved = True 'stops Word requesting
save doc on quit
objWord.ScreenUpdating = True 'turn on screen updates
objWord.Visible = True 'display document on
screen
Set objWord = Nothing
Exit Sub
ErrorCode:
objWord.Quit 'close Word if error
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
'==================
Hope that helps, let us know how you get on.
Peter Hibbs.
Thanks Peter, I would like the code. This seems to be rapidly spiralling out
of control as I thought it would be alot easier. All we have is a form that
we FAX off when we raise a fault on a Fire Alarm panel to the buidlings
owner. I was trying to make our life easier by just selecting the building
from a drop down list and have all the other info filled in autogomagically
but it is starting to look like it is more complicated than it should be.
Maybe my skills aren't quite there yet but I will keep trying until I find
that little clue that makes it all make sense. Cheers
Peter Hibbs said:
Dougie
The way I do it on my databases is to use MS Word to create the letter
which gives you all the formatting facilities in Word and then copy
the Access record field data into the letter using Automation. For
example, I would enter a code in the Word document like [FN] which
would then be replaced with the First Name field from the database
record.
You would need to do this with some VBA code, if you want some code to
do this just post back again.
Peter Hibbs.
I have a word document (form) with text boxes that I want to populate based
on the results of an Access form with a combo box that uses a look-up query.
This is not really a mail merge question as I only want to print one Word
form with the information from the database selection. For example, the Word
form will contain text boxes for name, phone, fax, etc. and will then be
printed to FAXMaker (but that is another question in itself).
Am I going about this the right way and how do I do it? Importing the Word
Doc into an Access report is not working as it seems to become a bitmap and
the cells in the document don't expand anymore.
