Office 2003 mailmerge only produces 1 letter instead of 2 or more

[Murray Muspratt-Rouse]

Having installed Office 2003 on another PC I would like to revisit a
problem I have reported before. The following code processes all letter
merge requests, starting with the deletion of the contents of
MergeTable, which provides the merge data, and the running of Access
2003 queries to load what is required: -

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 ("qryUpdateVolunteerRefereeLetterDate")
DoCmd.OpenQuery ("qryAppendVolunteerRefereedata")
Else
If strLetterDescription = "Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateVolunteerRefereeChaserDate")
DoCmd.OpenQuery ("qryVolunteerRefereechaser")
Else
If strLetterDescription = "TRAINING DATES" Then
DoCmd.RunMacro ("Set up training dates data for
merge")
Else
DoCmd.OpenQuery ("qryVolunteer")
End If
End If
End If
End If
If strFormName = "Clients" Then
If strLetterDescription = "REFERENCE CLIENT" Then
DoCmd.OpenQuery ("qryUpdateClientRefereeLetterDate")
DoCmd.OpenQuery ("qryAppendClientRefereedata")
Else
If strLetterDescription = "Client Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateClientRefereeChaserDate")
DoCmd.OpenQuery ("qryClientRefereechaser")
Else
DoCmd.OpenQuery ("qryClient")
End If
End If
End If

The code which follows does the merge - I think!

strCurrentFileName = CurrentDb.Name

Set objApp = CreateObject("Word.Application")
objApp.Visible = False
'objApp.Activate
'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:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True,
_
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
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
With objApp.ActiveDocument.MailMerge
..Destination = wdSendToNewDocument
..SuppressBlankLines = True
With .DataSource
..FirstRecord = .ActiveRecord
..LastRecord = .ActiveRecord
End With
..Execute Pause:=False
End With

The code which follows must be what closes the merge document and
leaves the result of the merge. I'm afraid I do not understand it at
all - I suppose I must get a good VBA programming guide (suggestions
welcome!)

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

If the result is meant to be more than one letter only one appears. The
only way to get all the letters out is to open the marge document in
Word, identify the Access table - again! - and complete the merge.

All suggestions gratefully received!

Murray

 

[Doug Robbins - Word MVP]

I would think that your problem is with the following lines of code

FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord

Which set the first and the last record to be merged to the active record -
i.e. one record.

--
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

 

[Murray Muspratt-Rouse]

I would think that your problem is with the following lines of code

FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord

Which set the first and the last record to be merged to the active
record -
i.e. one record.

--
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

Thank you for that, Doug. That's what I thought! Can I just use quotes
to remove
"With .DataSource
FirstRecord = .ActiveRecord
LastRecord = .ActiveRecord
End With"?

Not a good idea! It locked the database...

The odd thing is that running the merge under Office 2007 on my PC
(which the client insisted that I install, despite the fact that the
database is installed under Office 2003) I am able to display the
recipients and print the letters - all of them! Perhaps the best
solution would be to leave the main document open, so that the users
can complete the merge themselves? How would that change the code?

Murray

 

[Doug Robbins - Word MVP]

Use an apostrophe at the beginning of each line to convert it into a
comment.

--
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

 

[Murray Muspratt-Rouse]

That's just what I did! Please note what I said about the same cod
producing all the letters when run with Word 2007.

I have ordered a copy of Access 2003 Vba Programmer's Reference (Wro
Press) (Programmer to Programmer) in the hopes of learning a bit mor
about VBA. Learn something new every day - even when you are as old a
me - a few days short of 79!

 

[Doug Robbins - Word MVP]

Try

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With


--
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

 

[Murray Muspratt-Rouse]

Doug, I have tried your suggestion. "wdDefaultFirst/LastRecord" seeme
to prevent the display of any result. Removing objapp from "Wit
objapp.ActiveDocument.MailMerge" had the same effect. Having run th
Access process to get only the first copy I then opened the documen
with Word, asked for the merge data to be loaded, and got all th
letters. That is why I said I thought I would try to leave the Wor
window open, so that after Access had returned to the form from whic
the process is triggered it would be easy to produce all the letters.

I have suggested to the client that we should install Office 2007!

Murray

 

[Doug Robbins - Word MVP]

Or just

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

--
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

 

[Murray Muspratt-Rouse]

Doug, I realised I had to add another reference to VBA - the Word OLB.
also had to 'compact and repair' the database. Now it works wit
wdDefaultFirstRecord and wdDefaultLastRecord.

Thank you again for all your efforts.

Murray

Or just

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With

--
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

"Doug Robbins - Word MVP" (e-mail address removed) wrote in message

Try

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With


--
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 MV