Automating Access

Sending the Current Record to Word

The following example uses bookmarks in a Microsoft Word document to mark the locations where you want to place data from a record on a Microsoft Access form.

Creating a Microsoft Word Document

1 Start Microsoft Word and create the following new document:

First Last

Address City, Region, PostalCode

Dear Greeting,

Northwind Traders would like to thank you for your employment during the past year. Below you will find your photo. If this is not your most current picture, please let us know.

Photo

Sincerely,

Northwind Traders

2 Create a bookmark in Microsoft Word for the words "First," "Last," "Address," "City," "Region," "PostalCode," "Greeting," and "Photo":

a Select the word "First."
b On the Insert menu, click Bookmark
c In the Bookmark Name box, type "First," (without the quotation marks) and then click Add.
d Repeat steps 2a through 2c for each of the remaining words, substituting that word for the word "First" in steps 2a and 2c.

3 Save the document as C:\My Documents\MyMerge.doc, and then quit Microsoft Word.

Sending Data to Microsoft Word from a Microsoft Access Form

1 Start Microsoft Access and open the sample database Northwind.mdb.

2 Set a reference to the Microsoft Word 9.0 Object Library. To do so, follow these steps:
a Open any module in Design view.
b On the Tools menu, click References.
c Click Microsoft Word 9.0 Object Library in the Available References box. If that selection does not appear, browse for Msword9.olb, which installs by default in the C:\Program Files\Microsoft Office\Office folder.
d Click OK.
e Close the module.

3 Open the Employees form in Design view.

4 Add a command button to the form and set the following properties:
Command Button:
Name: MergeButton
Caption: Send to Word
OnClick: [Event Procedure]

5 Set the OnClick property of the command button to the following event procedure.
NOTE: In the following sample code, you must remove the comment from one line of code as indicated, depending on your version of Microsoft Access.

Private Sub MergeButton_Click()
On Error GoTo MergeButton_Err
Dim objWord As Word.Application
' Copy the Photo control on the Employees form.
DoCmd.GoToControl "Photo"
DoCmd.RunCommand acCmdCopy
Start Microsoft Word.
Set objWord = CreateObject("Word.Application")
With objWord
' Make the application visible.
.Visible = True
' Open the document.
.Documents.Open ("c:\my documents\mymerge.doc")
' Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("First").Select
.Selection.Text = (CStr(Forms!Employees!FirstName))
.ActiveDocument.Bookmarks("Last").Select
.Selection.Text = (CStr(Forms!Employees!LastName))
.ActiveDocument.Bookmarks("Address").Select
.Selection.Text = (CStr(Forms!Employees!Address))
.ActiveDocument.Bookmarks("City").Select
.Selection.Text = (CStr(Forms!Employees!City))
.ActiveDocument.Bookmarks("Region").Select
.Selection.Text = (CStr(Forms!Employees!Region))
.ActiveDocument.Bookmarks("PostalCode").Select
.Selection.Text = (CStr(Forms!Employees!PostalCode))
.ActiveDocument.Bookmarks("Greeting").Select
.Selection.Text = (CStr(Forms!Employees!FirstName))
' Paste the photo.
.ActiveDocument.Bookmarks("Photo").Select
.Selection.Paste
End With
' Print the document in the foreground so Word
' will not close until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=False
' Close the document without saving changes.
objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
' Quit Microsoft Word and release the object variable.
objWord.Quit
Set objWord = Nothing
Exit Sub

MergeButton_Err:
' If a field on the form is empty
' remove the bookmark text and continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next
' If the Photo field is empty.
ElseIf Err.Number = 2046 Then
MsgBox "Please add a photo to this record and try again."
Else
MsgBox Err.Number & vbCr & Err.Description
End If
Exit Sub
End Sub

6 Save the Employees form and open it in Form view.
7 Click the Send To Word button to start Microsoft Word, merge data from the current record on the form into MyMerge.doc, print the document, and then close Microsoft Word.
NOTE: When you use this method of inserting text into a Word Document, you are deleting the bookmark when you insert the record field content. If you need to reference the text that you entered into the document, you mustbookmark it. You can use the following sample to add the bookmark "Last" to the text inserted from record field "LastName."

.ActiveDocument.Bookmarks("Last").Select
.Selection.Text = (CStr(Forms!Employees!LastName))
' add this line to reapply the bookmark name to the selection
.ActiveDocument.Bookmarks.Add Name:="Last",Range:=Selection.Range