I don't think you will be happy with the results of a report so I wouldn't even go there.
I also don't like mail merge. That leaves us with OLE automation which is what I do. I'm going to post a sample so you can see how this works. The sample is straight blunt force but that's because it is an example. If you have only the one document, this method will be just fine. You only have to code it once and the code is actually pretty straight forward so you won't have trouble understanding it. However, if you have multiple documents with the potential for growth, then the best solution will be to use OLE but create tables in the database to define the mapping. So you have a table that lists the reports and the path to them unless that is stored in some other place. And another table that maps the table fields with the bookmarks in the documents.
Take a look at the sample and we can look at what to do from there.
This database presents an example of using VBA to fill bookmarked fields in a Word document. The Database is A2003 format. However, the only version of Word installed on my PC is W2007 so before using the example, you may need to open any code module and change the Word reference to whatever...
www.access-programmers.co.uk
Getting there, I have an error code come up 5941- The requested member of the collection does not exist. I'm going to get told off for my field names having spaces in them, but I made this a long time ago and it have developed into something much bigger than I intended, to change them now isn't practical due to the time it would take, sorry!
I'm not sure if it is due to the field names or another issue? Here is my code that I have:
Private Sub cmdPrevLetter_Click()
On Error GoTo Err_cmdPrevLetter_Click
Call PopulateBookmarks
Exit_cmdPrevLetter_Click:
Exit Sub
Err_cmdPrevLetter_Click:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_cmdPrevLetter_Click
End Sub
Public Sub PopulateBookmarks()
Dim sPathName As String
Dim sDocName As String
Dim sFamily As String
Dim iSeqNum As Integer
On Error GoTo Err_Proc:
Set db = CurrentDb()
'Fire up Word
Set WordApp = New Word.Application
WordApp.Visible = True ' make word visible
DoCmd.Hourglass True
sPathName = GetCurrentPathName
sDocName = QUOTE & sPathName & "Standard SEA.doc" & QUOTE
Set WordDoc = WordApp.Documents.Add(sDocName)
'Fill sFamily string
sFamily = ""
Set qdDAO = db.QueryDefs!SEA
qdDAO.Parameters![EnterID] = Me.ID
Set rsDAO = qdDAO.OpenRecordset
Do While rsDAO.EOF = False
sFamily = sFamily & rsDAO!FirstName & "; "
rsDAO.MoveNext
Loop
If Len(sFamily) > 1 Then
sFamily = Left(sFamily, Len(sFamily) - 2) ' remove trailing ;
End If
qdDAO.Close
rsDAO.Close
'Open recordset which will be used to populate bookmarks
Set qdDAO = db.QueryDefs!SEA
qdDAO.Parameters![EnterID] = Me.ID
Set rsDAO = qdDAO.OpenRecordset
WordDoc.FormFields("FullName").result = rsDAO!FirstName & " " & rsDAO!LastName
WordDoc.FormFields("FullName2").result = rsDAO!FirstName & " " & rsDAO!LastName
WordDoc.FormFields("Address").result = rsDAO![Home Address]
WordDoc.FormFields("DOB").result = rsDAO!DOB
WordDoc.FormFields("POB").result = rsDAO!POB
WordDoc.FormFields("YachtName").result = rsDAO![Yacht Name]
WordDoc.FormFields("YachtName1").result = rsDAO![Yacht Name]
WordDoc.FormFields("Position").result = rsDAO![Position for contract]
WordDoc.FormFields("Position1").result = rsDAO![Position for contract]
WordDoc.FormFields("Position2").result = rsDAO![Position for contract]
WordDoc.FormFields("Position3").result = rsDAO![Position for contract]
WordDoc.FormFields("Position4").result = rsDAO![Position for contract]
WordDoc.FormFields("Position5").result = rsDAO![Position for contract]
WordDoc.FormFields("StartDate").result = rsDAO![Start Date]
WordDoc.FormFields("Repat").result = rsDAO![Place of Repatriation]
WordDoc.FormFields("Wage").result = rsDAO![Monthly Salary]
WordDoc.FormFields("BenName").result = rsDAO![Account Name]
WordDoc.FormFields("BankName").result = rsDAO![Bank Name]
WordDoc.FormFields("BankAccount").result = rsDAO![Account Number]
WordDoc.FormFields("Routing").result = rsDAO![Routing Number]
WordDoc.FormFields("Swift").result = rsDAO![Sort or Swift Code]
WordDoc.FormFields("IBAN").result = rsDAO![IBAN Number]
WordDoc.FormFields("Leave").result = rsDAO![Leave Allowance]
WordDoc.FormFields("Place").result = rsDAO![Place Agreement Entered]
WordDoc.FormFields("DateEntered").result = rsDAO![Date Agreement Entered]
Set WordDoc = Nothing
MsgBox "Doc finished", vbOKOnly
Exit_Proc:
DoCmd.Hourglass False
Exit Sub
Err_Proc:
Select Case Err.Number
Case 4605
Resume Next
Case Else
MsgBox Err.Number & "-" & Err.Description
Resume Exit_Proc
End Select
Exit Sub
End Sub
Public Sub InsertTextAtBookMark(strBkmk As String, varText As Variant)
Dim BMRange As Range
On Error GoTo PROC_ERR
Set BMRange = WordDoc.BookMarks(strBkmk).Range
BMRange.Text = varText & ""
WordDoc.BookMarks.Add strBkmk, BMRange
BMRange.Select
PROC_EXIT:
Exit Sub
PROC_ERR:
Select Case Err.Number
Case 4605 'this method or property is not available because the object is empty
Resume Next
Case 5941, 6028 ' member does not exist/the range cannot be deleted
MsgBox "Bookmark {" & strBkmk & "} There is a mapping error with this document. Please contact your administrator.", vbOKOnly
Resume Next
Case 91 'object variable not set
Resume Next
Case 4218 'type mismatch
Resume Next
Case Else
MsgBox Err.Number & " - " & Err.Description
Resume PROC_EXIT
End Select
Resume PROC_EXIT
End Sub
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Exit_cmdDelete_Click:
Exit Sub
Err_cmdDelete_Click:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_cmdDelete_Click
End Sub
End Sub