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