Reply
 
Thread Tools Display Modes
  #1   Report Post  
TL
 
Posts: n/a
Default Mail Merge - Parameter prompts twice...

I have learned so much from these newsgroups €“ it is amazing how helpful they
have been. Unfortunately, I am not a programmer and I have run across a
couple of problems. I apologize that this posting is so long€¦.but I wanted
to be thorough. I am running Office 2002 with the exception of MS Access.
We are running MS Access 97.

Background: I have created a form in Access. This form has a combobox on
it that allows the user to select a Word file (complete file path) from the
dropdown list. Right next to this combobox is a €œMerge€ button. Once the
form is selected the user can then click the Merge button and it will
initiate a mail merge into the specified Word file (doc.). For one of the
forms the datasource is a parameter query. Once the button is clicked the
parameter pops up and tells the user to Enter the Record ID. Then, a second
or two later Word opens, and the Confirm Data Source dialog box opens up.
The user will select "MS Access Databases via DDE." The user is again
prompted for the Record ID. Once the Record ID has been entered the merge
executes and completes as expected.

Question 1: Why is the user prompted twice? My code is below. This code
is called when the user clicks on the Merge button €œ=MergeIt2()€

Function MergeIt2()

Dim objWord As Word.Document
Dim strFilePath As String

strFilePath = Forms.frmClientTrackingInput.FormAddress

Set objWord = GetObject(strFilePath, "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source.
objWord.MailMerge.OpenDataSource _
Name:="I:\shared\QED\Distributions\PCS Team\Client Tracking -
TL.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryClientTracking", _
SQLStatement:="Select * from [qryClientTracking]"
' Execute the mail merge.
objWord.MailMerge.Execute

End Function

Question 2: Is it possible to include code in the above function (MergeIt2)
that will close the Access database that the DDE connection opened? I
realize that the DDE connection is responsible for opening the database to
connect to the query. I have tried the following code but I keep getting an
error that says the Active X object cant be created.

Function MergeIt2()

Dim objWord As Word.Document
Dim objAccess As Access.Application
Dim strFilePath As String

strFilePath = Forms.frmClientTrackingInput.FormAddress

Set objWord = GetObject(strFilePath, "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source.
objWord.MailMerge.OpenDataSource _
Name:="I:\shared\QED\Distributions\PCS Team\Client Tracking -
TL.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryClientTracking", _
SQLStatement:="Select * from [qryClientTracking]"
' Execute the mail merge.
objWord.MailMerge.Execute
Set objAccess = GetObject(, "I:\shared\QED\Distributions\PCS Team\Client
Tracking - TL.mdb")
objAccess.Visible = True
objAccess.Quit
Set objAccess = Nothing

End Function

The reason that I am concerned with closing the Access database that the DDE
connection opens is because of the users. I am not certain that they will
realize the database they are using has been opened again. If they have
several random client records to merge, they might run out of system
resources.

Additional Questions: Would any of the DDE functions (DDEInitiate,
DDExecute, DDEPoke, DDESend, and DDETerminateAll) that are available in
Access be better to use in order to communicate with MS Word? And if so,
would the DDE functions/statements be used with the With statement? (e.g.:
With objAccess, etc.) Would it be any easier closing the Access database
that DDE opens?

Any help is appreciated. Any help with specific code examples is GREATLY
appreciated.

Tiffany

  #2   Report Post  
Peter Jamieson
 
Posts: n/a
Default

The reason why Word prompts twice is /probably/ because the mail merge main
document is already linked to the data source. When you open a mail merge
main document, Word will always try to connect to the data source, and in
this case, that means that it will try to open Access and run the parameter
query. Then your code comes along and runs an OpenDataSource which means
that it will try to make the connection all over again. Unless the use of
Access 97 introduces complications I haven't considered, you really have two
options:
a. ensure that the Word document is always saved without a data connection
(in other words, it is not a Mail Merge Main Document). You can do this
programmatically by setting

Activedocument.MailMerge.MainDocumentType = wdNotAMergeDocument

Then the user will not be asked any questions when the document is first
opened, only when your OpenDataSource command executes
b. as long as you know that the document is always connected to the correct
query/table in the correct database, don't execute an OpenDataSource.

If you take route (a), you just need to know that when you remove the
connection all you lose is the info. about the database and table/query
names, any sort/filter options and any record-by-record selections the user
may have done in the Mail Merge Recipients dialog box. All the fields should
remain. The advantage of (a) is that you can, for example, insert code to
verrify that the .mdb exists before Word tries to open it, and respond to
VBA error trapping in the OpenDataSource comand - otherwise, you have no
control whatever over what happens when the database does not exist or
cannot be found.

If you take route (b) you are much more dependent on Word's conneciton info.
being correct and nothing going wrong during the connection. You might be
able to modify the connection in minor ways by setting
MailMerge.Datasource.QueryString, but you would need to test that.

If you are going to execute an OpenDataSource in Word 2002/3 with an Access
DDE source, you should probably suse the parameter

Subtype:=wdMergeSubtypeWord2000

in the call.

Question 2: Is it possible to include code in the above function
(MergeIt2)
that will close the Access database that the DDE connection opened? I
realize that the DDE connection is responsible for opening the database to
connect to the query. I have tried the following code but I keep getting
an
error that says the Active X object can't be created.


Well, when Word connects to a data source via DDE it should close its
connection, and close Access if Access was not previously open, when it
either closes the Mail Merge Main Document orthat document reverts to being
a non-mail merge document. In other words, if you execute the merge, then
close objWord, any connection/copy of Access Word opened should disappear.If
you want to keep objWord open, you can try
objWord.MailMerge.MainDocumentType = wdNotAMergeDocument

I think things may become a bit simpler when Word stops trying to open your
data source twice. Even so, it may be difficult to get this to work in all
circumstances, and that is of course one of the problems with trying to use
DDE connections. You might be able to avoid DDE altogether if you could set
up, or even execute, the query the user wants to run dynamically. I don't
have code to do that, it's non-trivial and gets harder in a multi-user
environment, but e.g. if you already know what record(s) the user needs to
merge before you initiate merge then you might be able to create a query in
your Access code. if you don't, then you could consider using ADO and ADOX
code in Word to crate a new query/view, then open it using OLEDB or ODBC
which means that Word does not need to open Access - it just needs to run
the jet engine. However, I think I would only start going down that road if
I was willing to spend quite a lomg time on it.

Peter Jamieson


"TL" wrote in message
...
I have learned so much from these newsgroups - it is amazing how helpful
they
have been. Unfortunately, I am not a programmer and I have run across a
couple of problems. I apologize that this posting is so long..but I
wanted
to be thorough. I am running Office 2002 with the exception of MS Access.
We are running MS Access 97.

Background: I have created a form in Access. This form has a combobox on
it that allows the user to select a Word file (complete file path) from
the
dropdown list. Right next to this combobox is a "Merge" button. Once the
form is selected the user can then click the Merge button and it will
initiate a mail merge into the specified Word file (doc.). For one of the
forms the datasource is a parameter query. Once the button is clicked the
parameter pops up and tells the user to Enter the Record ID. Then, a
second
or two later Word opens, and the Confirm Data Source dialog box opens up.
The user will select "MS Access Databases via DDE." The user is again
prompted for the Record ID. Once the Record ID has been entered the merge
executes and completes as expected.

Question 1: Why is the user prompted twice? My code is below. This code
is called when the user clicks on the Merge button "=MergeIt2()"

Function MergeIt2()

Dim objWord As Word.Document
Dim strFilePath As String

strFilePath = Forms.frmClientTrackingInput.FormAddress

Set objWord = GetObject(strFilePath, "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source.
objWord.MailMerge.OpenDataSource _
Name:="I:\shared\QED\Distributions\PCS Team\Client Tracking -
TL.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryClientTracking", _
SQLStatement:="Select * from [qryClientTracking]"
' Execute the mail merge.
objWord.MailMerge.Execute

End Function

Question 2: Is it possible to include code in the above function
(MergeIt2)
that will close the Access database that the DDE connection opened? I
realize that the DDE connection is responsible for opening the database to
connect to the query. I have tried the following code but I keep getting
an
error that says the Active X object can't be created.

Function MergeIt2()

Dim objWord As Word.Document
Dim objAccess As Access.Application
Dim strFilePath As String

strFilePath = Forms.frmClientTrackingInput.FormAddress

Set objWord = GetObject(strFilePath, "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source.
objWord.MailMerge.OpenDataSource _
Name:="I:\shared\QED\Distributions\PCS Team\Client Tracking -
TL.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryClientTracking", _
SQLStatement:="Select * from [qryClientTracking]"
' Execute the mail merge.
objWord.MailMerge.Execute
Set objAccess = GetObject(, "I:\shared\QED\Distributions\PCS
Team\Client
Tracking - TL.mdb")
objAccess.Visible = True
objAccess.Quit
Set objAccess = Nothing

End Function

The reason that I am concerned with closing the Access database that the
DDE
connection opens is because of the users. I am not certain that they will
realize the database they are using has been opened again. If they have
several random client records to merge, they might run out of system
resources.

Additional Questions: Would any of the DDE functions (DDEInitiate,
DDExecute, DDEPoke, DDESend, and DDETerminateAll) that are available in
Access be better to use in order to communicate with MS Word? And if so,
would the DDE functions/statements be used with the With statement?
(e.g.:
With objAccess, etc.) Would it be any easier closing the Access database
that DDE opens?

Any help is appreciated. Any help with specific code examples is GREATLY
appreciated.

Tiffany



  #3   Report Post  
TL
 
Posts: n/a
Default

Thank you so much for your help! The prompt issue is related to the document
already being linked to the datasource. I am encountering a small problem,
though. I have tried using the code:

Activedocument.MailMerge.MainDocumentType = wdNotAMergeDocument

to fix this, but I am apparently not putting in the right place in my code.
I have been getting various errors depending on where I place it. So, I went
straight to the document and changed it with the setting "Normal Word
Document and saved it. This only works the first time the merge is executed.
When you click the Merge button again the prompt for the RecordID comes up
again. I am assuming, (and I never really like to do that), that this is
because when the Mail Merge is Executed the document is changed to a Mail
Merge Document during the Execute of the first Mail Merge - hence the need
for the above code. So, my question is: Where exactly do I need to place
the above code in order for the document to be returned to a normal document?
I have tried several places with no luck.

Tiffany


  #4   Report Post  
Peter Jamieson
 
Posts: n/a
Default

Yes, the change to "not a merge document" really needs to be a one-off
action which yu now know how to do. After that, the name of the game is that
you either prevent the user from saving the mail merge main document after
the merge, or, since you are automating the merge, you should be able to put
the statement after the Mailmerge.Execute. But you need to use the right
document object variable to do that, and immediately after the merge
executes the ActiveDocument is the /new/ document, so you need to use

objWord.MailMerge.MainDocumentType = wdNotAMergeDocument

(I think you were using objWord to refer to the Mail Merge Main Document in
your code?)

Another possibility is to put

Activedocument.MailMerge.MainDocumentType = wdNotAMergeDocument

in an AutoClose macro (i.e. create a module in the document, create a sub
called AutoClose(), and put the appropriate statement in there), but I would
avoid that if possible.

Peter Jamieson


"TL" wrote in message
...
Thank you so much for your help! The prompt issue is related to the
document
already being linked to the datasource. I am encountering a small
problem,
though. I have tried using the code:

Activedocument.MailMerge.MainDocumentType = wdNotAMergeDocument

to fix this, but I am apparently not putting in the right place in my
code.
I have been getting various errors depending on where I place it. So, I
went
straight to the document and changed it with the setting "Normal Word
Document and saved it. This only works the first time the merge is
executed.
When you click the Merge button again the prompt for the RecordID comes up
again. I am assuming, (and I never really like to do that), that this is
because when the Mail Merge is Executed the document is changed to a Mail
Merge Document during the Execute of the first Mail Merge - hence the need
for the above code. So, my question is: Where exactly do I need to place
the above code in order for the document to be returned to a normal
document?
I have tried several places with no luck.

Tiffany




  #5   Report Post  
TL
 
Posts: n/a
Default

Sorry for not replying sooner....my mother had major neck surgery, and I was
out of the office to take care of her.

I really appreciate your help! The merge is working great. I added the
following code per your suggestion, and it is no longer prompting for the
parameter twice.

objWord.MailMerge.MainDocumentType = wdNotAMergeDocument
objWord.Close

It still won't close the Access window. I guess we can live with that,
unless you have any other suggestions for me. ; - ) In any case, I really
appreciate your help! You're AWESOME!

Thanks!

Tiffany


  #6   Report Post  
Peter Jamieson
 
Posts: n/a
Default

No, no more suggestions in this case.

Hope your mother has a swift recovery.

Peter Jamieson
"TL" wrote in message
...
Sorry for not replying sooner....my mother had major neck surgery, and I
was
out of the office to take care of her.

I really appreciate your help! The merge is working great. I added the
following code per your suggestion, and it is no longer prompting for the
parameter twice.

objWord.MailMerge.MainDocumentType = wdNotAMergeDocument
objWord.Close

It still won't close the Access window. I guess we can live with that,
unless you have any other suggestions for me. ; - ) In any case, I
really
appreciate your help! You're AWESOME!

Thanks!

Tiffany



  #7   Report Post  
TL
 
Posts: n/a
Default

Oddly enough, I was able to get Access to close - the only thing I changed
was to add an Application Title, that matches the db name, to the Startup
options.

"Peter Jamieson" wrote:

No, no more suggestions in this case.

Hope your mother has a swift recovery.

Peter Jamieson
"TL" wrote in message
...
Sorry for not replying sooner....my mother had major neck surgery, and I
was
out of the office to take care of her.

I really appreciate your help! The merge is working great. I added the
following code per your suggestion, and it is no longer prompting for the
parameter twice.

objWord.MailMerge.MainDocumentType = wdNotAMergeDocument
objWord.Close

It still won't close the Access window. I guess we can live with that,
unless you have any other suggestions for me. ; - ) In any case, I
really
appreciate your help! You're AWESOME!

Thanks!

Tiffany




Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge Word 2003 Issue S.Nicks Mailmerge 5 February 20th 09 02:55 AM
mail merge with attachments AS Mailmerge 5 April 9th 05 09:49 AM
Problem with automating mail merge HeatherO Mailmerge 2 March 29th 05 10:11 PM
Mail Merge Issue With Office 97 - Excel Data Source Matt Thorley Mailmerge 1 February 16th 05 12:38 AM
Word-Excel 2003 - Mail Merge Recipients problem AYager Mailmerge 2 January 11th 05 05:11 AM


All times are GMT +1. The time now is 09:15 PM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright ©2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"