MailMerge created by Access XP - Data Missing

[Shannon Rotz]

I have a function in a Microsoft Access module (below) which creates a Word
XP Mailmerge document, drawing the data from Access XP. I can get the
connection to work fine. However, when I create the form letters, the data
from certain merge fields is missing. When I view the data in the MailMerge
Recipients dialog box, the data is not there either - the columns are there,
but the records have blanks where the data in those columns should be.

When I look at the Access source table, the data is fine.

I've tried Refreshing via MailMerge Recipients, with no luck. I've also
tried converting the document back to a Normal Document, then re-connecting
to the Access data source - also with no luck. I've even tried re-booting
the computer!

One thing that I think might be relevant: the three fields which aren't
showing data were added to the Access table after I initially set up the
Word connection manually (in order to add the Merge fields). So my
assumption is that something isn't refreshing.

Is this data being cached somewhere? Or am I just nuts?

Someone please rescue my sanity ...

Shannon

Public Function WordXP_letter(doc_name As String, sqlstmt As String,
AutoMerge As Boolean, Optional strQuery As String)
'On Error GoTo err_word_letter

Dim mywrd As Word.Application
Dim mydoc As Word.Document

'Stop
Set mywrd = New Word.Application

Set mydoc = mywrd.Documents.Add(doc_name)
mywrd.Visible = True
With mydoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource NAME:=CurrentDb.NAME, SQLStatement:=sqlstmt,
subtype:=wdMergeSubTypeWord2000
.Destination = wdSendToNewDocument
End With
If AutoMerge = True Then
mydoc.MailMerge.Execute True
mydoc.Close wdDoNotSaveChanges
End If
mywrd.Activate

'mywrd.Documents("Document1").Close wdDoNotSaveChanges
Set mydoc = Nothing
Set mywrd = Nothing
Exit Function

err_WordXP_letter:
Select Case Err.Number
Case Is = 5151
MsgBox ("Cannot find the template '" & doc_name & "'. Please
re-add it to the 'Secretary' folder and try again.")
Case Is = 4198
Resume Next
Case Else
MsgBox Err.Number & " - " & Err.Description
Exit Function
End Select

End Function

[Pieter Linden]

"Shannon Rotz" <sr***@shaw.ca> wrote in message news:<jVmcd.759809$gE.624153@pd7tw3no>...

I have a function in a Microsoft Access module (below) which creates a Word
XP Mailmerge document, drawing the data from Access XP. I can get the
connection to work fine. However, when I create the form letters, the data
from certain merge fields is missing. When I view the data in the MailMerge
Recipients dialog box, the data is not there either - the columns are there,
but the records have blanks where the data in those columns should be.

When I look at the Access source table, the data is fine.

I've tried Refreshing via MailMerge Recipients, with no luck. I've also
tried converting the document back to a Normal Document, then re-connecting
to the Access data source - also with no luck. I've even tried re-booting
the computer!

One thing that I think might be relevant: the three fields which aren't
showing data were added to the Access table after I initially set up the
Word connection manually (in order to add the Merge fields). So my
assumption is that something isn't refreshing.

Is this data being cached somewhere? Or am I just nuts?

Someone please rescue my sanity ...

Sounds like something's wrong with the Word doc. I'd drop in the new
fields and be sure to save it, close and retry the merge. If that
fails, I'd copy/paste the boilerplate to a new doc, and do the setup
again. Annoying, probably, but also likely quicker than sorting out
whatever's causing the problem. I'd be willing to bet it's the fact
that the datasource hasn't been reset, so Word isn't "seeing" the new
fields.

[david epsom dot com dot au]

Also try doing a Compact on the database after making design changes,
and check the data in those columns - you sometimes get an 'error'
value in the whole column (which turns up blank) if there is a problem
with the data in one or more rows.

(david)

"Shannon Rotz" <sr***@shaw.ca> wrote in message
news:jVmcd.759809$gE.624153@pd7tw3no...

I have a function in a Microsoft Access module (below) which creates a Word XP Mailmerge document, drawing the data from Access XP. I can get the
connection to work fine. However, when I create the form letters, the data from certain merge fields is missing. When I view the data in the MailMerge Recipients dialog box, the data is not there either - the columns are there, but the records have blanks where the data in those columns should be.

When I look at the Access source table, the data is fine.

I've tried Refreshing via MailMerge Recipients, with no luck. I've also
tried converting the document back to a Normal Document, then re-connecting to the Access data source - also with no luck. I've even tried re-booting
the computer!

One thing that I think might be relevant: the three fields which aren't
showing data were added to the Access table after I initially set up the
Word connection manually (in order to add the Merge fields). So my
assumption is that something isn't refreshing.

Is this data being cached somewhere? Or am I just nuts?

Someone please rescue my sanity ...

Shannon

Public Function WordXP_letter(doc_name As String, sqlstmt As String,
AutoMerge As Boolean, Optional strQuery As String)
'On Error GoTo err_word_letter

Dim mywrd As Word.Application
Dim mydoc As Word.Document

'Stop
Set mywrd = New Word.Application

Set mydoc = mywrd.Documents.Add(doc_name)
mywrd.Visible = True
With mydoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource NAME:=CurrentDb.NAME, SQLStatement:=sqlstmt,
subtype:=wdMergeSubTypeWord2000
.Destination = wdSendToNewDocument
End With
If AutoMerge = True Then
mydoc.MailMerge.Execute True
mydoc.Close wdDoNotSaveChanges
End If
mywrd.Activate

'mywrd.Documents("Document1").Close wdDoNotSaveChanges
Set mydoc = Nothing
Set mywrd = Nothing
Exit Function

err_WordXP_letter:
Select Case Err.Number
Case Is = 5151
MsgBox ("Cannot find the template '" & doc_name & "'. Please
re-add it to the 'Secretary' folder and try again.")
Case Is = 4198
Resume Next
Case Else
MsgBox Err.Number & " - " & Err.Description
Exit Function
End Select

End Function