[Murray Muspratt-Rouse]

I have implemented Doug's suggestions, but, while the code ran without error, nothing was displayed - the application returned to the form in MS Access from which the merge was initiated. The code that ran is below: -

Sub OpenWordDoc(strDocName As String, strLetterDescription As String, strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String
On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name

Set objApp = CreateObject("Word.Application")
'objApp.Visible = False
objApp.Activate
'Dim objMMMD As Object
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

'objApp.Documents.Open FileName:=strDocName, ConfirmConversions:=False, _
'ReadOnly:=False, AddToRecentFiles:=False _
', PasswordDocument:="", _
'PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
'WritePasswordTemplate:="", XMLTransform:=""

'objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _

With objMMMD
.MailMerge.OpenDataSource Name:=strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = .ActiveRecord
.LastRecord = .ActiveRecord
End With
.Execute Pause:=False
.Close wdDoNotSaveChanges
End With


'Dim intSplitName As Integer
'Dim intLength As Integer
'intLength = Len(strDocName)
'intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
'strDocName = Right(strDocName, intLength - intSplitName)


'objApp.Windows(strDocName).Activate
'objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
'objMMMD.Close SaveChanges:=False
'Set objMMMD = Nothing


'objApp.Documents.Open strDocName


'objApp.Visible = True
'objApp.Activate

End Sub

Murray

Originally Posted by Doug Robbins - Word MVP

Try the following (watch out for line breaks that may occur in the wrong
place.) I haven't really looked at the If... Else...End If manipulations of
the Access data at the beginning to see if there is anything wrong with it,
only at the Word part of the code.

Sub OpenWordDoc(strDocName As String, strLetterDescription As String,
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String

On Error Resume Next
DoCmd.OpenQuery "qrydeleteMergeTablerows"


'Load data to MergeTable with a query that collects the required data
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateRefereeLetterDate")
DoCmd.OpenQuery ("qryReferees")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateRefereeChaserDate")
DoCmd.OpenQuery ("qryRefereechaser")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
Else
DoCmd.OpenQuery ("qryClient")
End If

strCurrentFileName = CurrentDb.Name
'Opens the document

Set objApp = CreateObject("Word.Application")
objApp.Activate
Set objMMMD = objApp.Documents.Open(FileName:=strDocName)
With ObjMMMD
.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er _
ID=Admin;Date Source=strCurrentFileName;Mode=Read; _
Extended Properties="""";Jet OLEDB:System database=""""; _
Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLE",
_
SQLStatement:="SELECT * FROM `mergetable`", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With
.Execute Pause:=False
.Close wdDoNotSaveChanges
End With

End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
--