This shows you how to filter contacts, then merge the filtered records to Word documents.
Access Archon #130
Introduction
Thanks to John Lehmus for suggesting this article, which combines the fancy filtering of Access Archon #129 with the four types of merging featured in my Code Sample #24, so that you can filter contacts in various ways, and then merge the filtered records to Word documents, using your choice of the Doc Properties, Bookmarks, Mail Merge or TypeText methods.
The sample database, Filter and Merge.mdb, has a main menu and two forms, one with a subform. The fpriContacts form, which can be opened from the large Contacts button on the main menu, is a standard form with one record per contact, and a button to create a Word letter to the current contact, using the Doc Properties method.
Figure A. The Contacts form, with a button for creating a Word letter.
The main menu has two combo boxes for selecting a Word document and a single contact. When you click the large Word button to the left of these controls, a Word document for the selected contact is created, also using the Doc Properties method.
Figure B. The main menu, with controls for selecting a Word document for a selected contact.
The Other Forms drop-down list on the main menu has only one selection, Filter Contacts. This selection is the form that lets you create and apply a filter to the Contacts recordset, and also select a merge type and merge document. To filter the contacts, click the Filtered Records option in the Records option group, select a value in one or more of the drop-down Filter combo boxes, and click the Create and Apply Combined Filter command button. The datasheet is requeried to show only the records that match the query. Figure C shows this form with a Last Meeting Date of 12/15/1998 selected for filtering the contacts.
Figure C. The Filter Contacts form, with a Last Meeting filter selected (click on image to see full size version)
I won’t discuss the techniques used to create and apply the filters here; see Access Archon #129 for that information.
The Select Merge Type/Template combo box has qryMergeLetters as its row source. This query has a calculated field, MergeType, used to convert Yes/No fields in the tlkpLetters table to a string representing the merge type:
MergeType: IIf([Merge]=True,”Mail Merge”,IIf([TypeText]=True,”TypeText”,IIf([DocProps]=True,”Doc Properties”,IIf([Bookmarks]=True,”Bookmarks”))))
The MergeType field is referenced in code when the Word documents are created, and it is also displayed before the template name in the drop-down list.
The Create Word Documents command button does the merge to Word; it creates multiple documents (one per contact) in some cases (letters and one-up labels for the Doc Props, Bookmarks and TypeText methods,) and one document for others (#5160, #5161 and #5162 labels for the TypeText method and all selections for the MailMerge method).
Which Method to Use?
Each method has its advantages and disadvantages, but in my opinion the Doc Properties method is best in most cases.
Bookmarks
Some Word users prefer bookmarks to custom doc properties, because they are more familiar and accessible (custom doc properties are located on a page of the Word document’s properties sheet). For most purposes, bookmarks work as well as custom doc properties, with one exception: a single custom doc property can be displayed in multiple DocProperty fields in the document (very useful for displaying the name and address in both a letter and an envelope, for example, as I do in the Doc Properties letters), while you have to write the same information to multiple bookmarks to get the same result with bookmarks.
Doc Properties
With this method, a new document is created from a template for each Access record, and the document’s custom doc properties are filled with data from an Access record. Word custom doc properties belong to individual documents, so data written to them from code is stored in the document itself, not in a separate data file. This has several advantages over mail merge:
- You don’t need a separate data source in a specific location to view the merged data in the document, thus avoiding the problems that occur when a data source is deleted, renamed or moved.
- No instance of Access is opened when a Word document is opened, which saves memory, and lets users who don’t have Access work with the documents.
- Users can easily modify the merged data or letter text for individual letters.
- Each letter is a separate document, which makes them easier to file, print or send as email attachments.
Mail Merge
Mail Merge creates a single document, which makes it more difficult to pick out and modify a single record, but if you need to merge a great number of records, you need to use this method. To avoid problems with slow links between Access and Word, I export the data to merge to a text file, and use the text file as the mail merge data source.
For more information on mail merge, see Access Archon #92, “Mail Merge Mysteries”.
TypeText
For labels, name badges and other such documents, I use methods of the Word Selection object to move from cell to cell in a Word table: MoveRight to move to the next cell, and TypeText to paste data from an Access record to that cell. The resulting document is a plain text Word document, easy to modify as needed before printing.
VBA Code
The event procedure for the cmdCreateWordDocs command button is listed below, with explanation.
Private Sub cmdCreateWordDocs_Click()
On Error GoTo ErrorHandler
Dim cbo As Access.ComboBox
Dim doc As Word.Document
Dim i As String
Dim intCount As Integer
Dim intSaveNameFail As String
Dim prps As Object
Dim rstTable As DAO.Recordset
Dim strAddress As String
Dim strDBPath As String
Dim strDocName As String
Dim strDocsPath As String
Dim strDocType As String
Dim strJobTitle As String
Dim strLongDate As String
Dim strMergeType As String
Dim strName As String
Dim strNameAndJobTitle As String
Dim strSaveName As String
Dim strSaveNamePath As String
Dim strShortDate As String
Dim strTable As String
Dim strTemplatePath As String
Dim strTest As String
Dim strTestFile As String
Dim strTextFile As String
Dim strValue As String
Dim strWordTemplate As String
‘Check that a letter has been selected
strWordTemplate = Nz(Me![cboSelect].Value)
Set cbo = Me![cboSelect]
If strWordTemplate = “” Then
MsgBox “Please select a document”
cbo.SetFocus
cbo.Dropdown
GoTo ErrorHandlerExit
Else
‘The bound column is the template file name
strWordTemplate = cbo.Value
‘The merge type is in the 3rd column of the list (Column (2) in zero-based numbering);
‘it is used in the Select Case statement that does the merge
strMergeType = cbo.Column(2)
Debug.Print “Merge type: ” & strMergeType
End If
‘Set global Word application variable; if Word is not running,
‘the error handler defaults to CreateObject
Set pappWord = GetObject(, “Word.Application”)
‘Substitute whatever date formats you want below – but bear in mind that strShortDate
‘is used for creating the save name, so it can’t contain characters that are not
‘allowed in file names
strLongDate = Format(Date, “mmmm d, yyyy”)
strShortDate = Format(Date, “m-d-yyyy”)
‘The Word documents are created in the default My Documents folder;
‘you can change the path as desired
strDocsPath = pappWord.Options.DefaultFilePath(wdDocumentsPath) & “”
‘The Word templates are looked for in the default User Templates folder;
‘you can change the path if you stored them in another folder
strTemplatePath = pappWord.Options.DefaultFilePath(wdUserTemplatesPath)
strWordTemplate = strTemplatePath & “” & strWordTemplate
‘Check for existence of template in template folder,
‘and exit if not found
strTestFile = Nz(Dir(strWordTemplate))
Debug.Print “Test file: ” & strTestFile
If strTestFile = “” Then
MsgBox strWordTemplate & ” template not found; can’t create letter”
GoTo ErrorHandlerExit
End If
‘Check that at least one contact has been selected
strQuery = “qryFilteredContacts”
intCount = Nz(DCount(“*”, strQuery))
Debug.Print “No. of contacts selected: ” & intCount
If intCount = 0 Then
MsgBox “No contacts selected; can’t create documents”
GoTo ErrorHandlerExit
End If
‘Create recordset based on filtered query
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strQuery, dbOpenDynaset)
‘Set up a Select Case statement to run the selected type of merge
Select Case strMergeType
Case “Bookmarks”
GoTo Bookmarks
Case “Doc Properties”
GoTo DocProps
Case “Mail Merge”
GoTo MailMerge
Case “TypeText”
GoTo TypeText
End Select
Bookmarks:
Do While Not rst.EOF
‘Check for required address information
strTest = Nz(rst![Address])
Debug.Print “Street address: ” & strTest
If strTest = “” Then
MsgBox “Can’t send letter — no street address!”
GoTo NextRecordBM
End If
strName = Nz(rst![FirstNameFirst])
strJobTitle = Nz(rst![JobTitle])
If strJobTitle <> “” Then
strNameAndJobTitle = strName & vbCrLf & strJobTitle
End If
‘Open a new document based on the selected template
Set doc = pappWord.Documents.Add(strWordTemplate)
‘Write information to Word bookmarks
‘Check for existence of each bookmark before writing to it, since
‘some templates lack some bookmarks
If doc.Bookmarks.Exists(“Name”) = True Then
With pappWord.Selection
.GoTo What:=wdGoToBookmark, Name:=”Name”
.TypeText Text:=strNameAndJobTitle
End With
End If
If doc.Bookmarks.Exists(“CompanyName”) = True Then
With pappWord.Selection
.GoTo What:=wdGoToBookmark, Name:=”CompanyName”
.TypeText Text:=Nz(rst![CompanyName])
End With
End If
If doc.Bookmarks.Exists(“Address”) = True Then
With pappWord.Selection
.GoTo What:=wdGoToBookmark, Name:=”Address”
.TypeText Text:=Nz(rst![Address])
End With
End If
If doc.Bookmarks.Exists(“Salutation”) = True Then
With pappWord.Selection
.GoTo What:=wdGoToBookmark, Name:=”Salutation”
.TypeText Text:=Nz(rst![Salutation])
End With
End If
If doc.Bookmarks.Exists(“TodayDate”) = True Then
With pappWord.Selection
.GoTo What:=wdGoToBookmark, Name:=”TodayDate”
.TypeText Text:=strLongDate
End With
End If
If doc.Bookmarks.Exists(“EnvelopeName”) = True Then
With pappWord.Selection
.GoTo What:=wdGoToBookmark, Name:=”EnvelopeName”
.TypeText Text:=strNameAndJobTitle
End With
End If
If doc.Bookmarks.Exists(“EnvelopeCompany”) = True Then
With pappWord.Selection
.GoTo What:=wdGoToBookmark, Name:=”EnvelopeCompany”
.TypeText Text:=Nz(rst![CompanyName])
End With
End If
If doc.Bookmarks.Exists(“EnvelopeAddress”) = True Then
With pappWord.Selection
.GoTo What:=wdGoToBookmark, Name:=”EnvelopeAddress”
.TypeText Text:=Nz(rst![Address])
End With
End If
‘Check for existence of previously saved letter in documents folder,
‘and append an incremented number to save name if found
strDocType = pappWord.ActiveDocument.BuiltInDocumentProperties(2)
strSaveName = strDocType & ” to ” & strName
strSaveName = strSaveName & ” on ” & strShortDate & “.doc”
i = 2
intSaveNameFail = True
Do While intSaveNameFail
strSaveNamePath = strDocsPath & strSaveName
Debug.Print “Proposed save name and path: ” _
& vbCrLf & strSaveNamePath
strTestFile = Nz(Dir(strSaveNamePath))
Debug.Print “Test file: ” & strTestFile
If strTestFile = strSaveName Then
Debug.Print “Save name already used: ” & strSaveName
‘Create new save name with incremented number
intSaveNameFail = True
strSaveName = strDocType & ” ” & CStr(i) & ” to ” & strName
strSaveName = strSaveName & ” on ” & strShortDate & “.doc”
strSaveNamePath = strDocsPath & strSaveName
Debug.Print “New save name and path: ” _
& vbCrLf & strSaveNamePath
i = i + 1
Else
Debug.Print “Save name not used: ” & strSaveName
intSaveNameFail = False
End If
Loop
‘Save Word doc
doc.SaveAs strSaveName
NextRecordBM:
rst.MoveNext
Loop
‘Activate Word
With pappWord
.ActiveWindow.WindowState = wdWindowStateNormal
.Visible = True
.Activate
End With
GoTo ErrorHandlerExit
Supporting Files
The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site. It is accarch130zip, which is the last entry in the table of Access Archon columns for Access Watch.
|
Document Name |
Document Type |
Place in |
|
Filter and Merge.mdb |
Access 2000 database (can also be used in higher versions of Access) |
Wherever you want |
|
Avery 5160 Labels.dot |
Word templates |
Templates folder (usually C:Program FilesMicrosoft OfficeTemplates |
