Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge Word 2003 Issue | Mailmerge | |||
mail merge with attachments | Mailmerge | |||
Problem with automating mail merge | Mailmerge | |||
Mail Merge Issue With Office 97 - Excel Data Source | Mailmerge | |||
Word-Excel 2003 - Mail Merge Recipients problem | Mailmerge |