Mail merge in Word (Datasource: Access)

[GA]

Hi!
I have created a mail merge document(Word 2003) of 300
students & saved it a) one master document and also tried
to b) save it as 300 different stud. names. My data
source is in Access2003. The purspose being to update the
student's individual record (class, sections, scores,
etc.) and to insert teachers comments(over 1000 words per
student), during the course of the academic year.

However, when I update the fields in Access at periodic
intervals(e.g. student attendance, faculty name, etc.),
the mail merge document does not pick up the updated/new
info.

Pls. advice if a) Mail merge (Word) does have the option
to update fields on a real-time basis.
b) Since my data source is in Access & I update it at
various intervals, is there a better way of monitoring
student info. (perhaps, in Excel,etc.)?

Thank you.. GA ([email protected])

 

[Doug Robbins]

The best thing would be to use a report in Access.

I am not sure what you mean by the mailmerge document. If it is a document
that was previously created by executing the mailmerge, then it will not be
updated as there is no link between that document and the data source.

If you mean the mailmerge main document (the one into which you inserted the
mergefields) then when you re-execute the merge, it should pick up the
latest information, though to be sure, you can re-attach the data source.

There have been some reports here about mailmerge truncating data that
exceeded 250 characters. I have not experienced that and it may depend on
the connection method to the data source which can be controlled if you
select Options from the tools menu and then on the General tab, check the
box next to "Confirm conversions at Open"

If you use an Access report, you won't have to worry about any of that.

If the teachers comments are to be added to the document created by the
mailmerge, rather than into Access, then you will have to persist with the
mailmerge. You may be interested in the following however which would allow
you to split up the document created by the mailmerge into individual
documents for each student

The method involves creating a separate catalog type mailmerge maindocument
which creates a word document containing a table in each row of which would
be your data from the database that you want to use as the filename. In
your case probably the student name/number

You first execute that mailmerge, then save that file and close it. Then
execute the mailmerge that you want to create the separate files from and
with the
result of that on the screen, run a macro containing the following code
and when the File open dialog appears, select the file containing the table
created by the first mailmerge

' Throw Away Macro created by Doug Robbins
'
Dim Source As Document, oblist As Document, DocName As Range, DocumentName
As String
Dim i As Long, doctext As Range, target As Document
Set Source = ActiveDocument
With Dialogs(wdDialogFileOpen)
.Show
End With
Set oblist = ActiveDocument
Counter = 1
For i = 1 To oblist.Tables(1).Rows.Count
Set DocName = oblist.Tables(1).Cell(i, 1).Range
DocName.End = DocName.End - 1

'Change the path in the following command to suit where you want to save
the documents.
DocumentName = "I:\WorkArea\Documentum\" & DocName.Text
Set doctext = Source.Sections(i).Range
doctext.End = doctext.End - 1
Set target = Documents.Add
target.Range.FormattedText = doctext
target.SaveAs FileName:=DocumentName
target.Close
Next i


--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

 

[GA]

Hi,
Thank you ever so much for your prompt reply.
From what I've understood, the issue is not in creating
300 different word documents("sub-doc.") (which is the
individual student records) from the Master/Main
mailmerge doc. But the problem arises when, once I have
set up these "sub-doc." (created by executing the main
mail merge doc.) and I subsequently, update the fields in
my datasource (eg Access), the SUB-DOC. does not pick up
the changes made in my datasource.

1) Can I run a Macro in my sub-doc. to reflect the
changes made in datasource at periodic intervals?

2) Other than creating a form/report on Access, is there
another application which I can use, which works on a
real-time basis. Thank you for your advice. GA

 

[Doug Robbins]

Your separate post has been answered. Better to keep it all in the one
thread, so here it is again for others that may be interested and were
watching this thread:

Once you execute the merge, there is no link between the document that is
created that contains a "sub-doc" for each student and the original data
source. If changes are made to the datasource, it is necessary to
re-execute the merge for those changes to be reflected in the "sub-docs".
Of course, if the individual "sub-docs" have been modified in any way, those
modifications will not appear in the new document (containing a new set of
sub-docs) is created by executing the merge.

It is probably possible to create a macro to do what you suggest, but,
without wanting in any way to sound insulting, if you had the ability to
create that macro, you probably would not ask the question. It would not be
a simple task.

I do not know of any other method of doing this. I would do it all in
Access.



--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP