Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
BOOK: Access 2003 VBA Programmer's Reference
This is the forum to discuss the Wrox book Access 2003 VBA Programmer's Reference by Patricia Cardoza, Teresa Hennig, Graham Seach, Armen Stein; ISBN: 9780764559037
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Access 2003 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 8th, 2005, 02:48 AM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default "Pushing" from Access to Word via Mail Merge

Could someone help on this?
I am working in A2K, and have an .MDB that I am using to launch a Word Doc with mail merge fields already made. I am using the example in Access 2003 VBA pg. 462 as my guide.

My code bombs at the opendatasource method. The error I'm getting is saying the "Server threw an error" (!!) and it references number -2147417851 (80010105). Word does launch to the correct document, so the filename property of the dialog box is at least populated. I want to merge the data, and in so doing, pass a WHERE clause into the SQL. I just can't get it to take my arguments for the opendatasource method. Does anyone know WHICH common dialog controls components to check? Am I missing an argument- perhaps they have changed between 2K and 2003? We've tested the path and it's correct. The Word Template launches, but I want to narrow the SQL programatically for the user.

Thanks,
Loralee

************ my code
Private Sub cmdOpenPCL_Click()
' this is an attempt to open a Word document and start a mail merge
Dim strFilePath As String
Dim objWord As Word.Document
Me.cmdlg.DialogTitle = "Choose File Name and Location"
Me.cmdlg.ShowOpen
strFilePath = Me.cmdlg.FileName


Set objWord = GetObject(strFilePath, "Word.document")
' make word visible
objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource Name:="C:\Documents and Settings\My Name\My Documents\" & _
"CTU Provider Management 11022005 1726\", _
linktosource:=True, _
Connection:="TABLE tblPatient", _
sqlstatement:="SELECT * FROM tblPatient" ' bombs here with "server threw an error"

' execute the mail merge
objWord.MailMerge.Execute

End Sub


 
Old March 8th, 2006, 06:10 PM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Did you ever figure this out? I'm having same problem with the opendatasource.

 
Old March 8th, 2006, 10:20 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Yorba Linda, California, USA.
Posts: 217
Thanks: 0
Thanked 1 Time in 1 Post
Default

I never got it to work the way the book example showed. Another thread for this problem is:

http://p2p.wrox.com/topic.asp?TOPIC_ID=36365

(Other Forum members had some ideas that may work for you). Perhaps it has to do with my db being in A2K........ and the example was for Access 2003.

However, I used a work-around which gets me what I wanted. I've taken the PK from the form user is on, and set the WHERE of the query that will underly the Word DOT to be merged through the querydef collection.
For this to work the target WOrd documents MUST be .DOTs and they MUST be set up as mail merge documents with their OpenDataSource set to the target query.
THis has really saved us alot of time as well as allowed staff who can create WORD docs to create the templates and mail merge docs.
It also is easy for us to then customize letters that are otherwise form letters. (It was worth the sweat).


HTH,

Loralee

*****************************
Private Sub cmdMergeLetter_Click()
' this is an attempt to open a Word document and start a mail merge
Dim strFilePath As String
Dim objWord As Word.Document
Dim strPatientID As String ' use to link to PK of the form this launches from
Dim strSELECT As String
Dim strFROM As String
Dim strWHERE As String
Dim strSQLMerge As String

strPatientID = Forms!frmpatientdata.txtPatientID ' launch from this form
strSELECT = "SELECT tblPatient.[PatientID], tblPatient.[PtFirstName], tblPatient.[PtLastName], tblPatient.[PtBD], tblCity.[City], tblPatient.[PtAddress], tblPatient.[PtZip], tblReferral.[ReferBy]"
strFROM = " FROM (tblCity INNER JOIN tblPatient ON tblCity.[CityID] = tblPatient.[PtCityFK]) INNER JOIN tblReferral ON tblPatient.[PatientID] = tblReferral.[PtFK]"
strWHERE = " WHERE ((([PatientID])= " & strPatientID & "))"
strSQLMerge = strSELECT & strFROM & strWHERE

CurrentDb.QueryDefs("qrymailmerge").SQL = strSQLMerge

Me.cmdlg.DialogTitle = "Choose File Name and Location"
Me.cmdlg.ShowOpen
strFilePath = Me.cmdlg.FileName

Set objWord = GetObject(strFilePath, "Word.document")
' make word visible
objWord.Application.Visible = True

' execute the mail merge
objWord.MailMerge.Execute
objWord.Close

End Sub





Similar Threads
Thread Thread Starter Forum Replies Last Post
Access launching Word for mail merge Loralee Access 8 November 14th, 2005 04:24 PM
Mail Merge with MS Word bezaman Classic ASP Basics 1 October 6th, 2005 04:13 PM
Access/Vba mail merge to word help alfonse Access VBA 0 July 28th, 2005 05:11 AM
Access to Word Mail Merge Automation kareltje Access VBA 6 June 9th, 2005 04:54 AM
Word E-Mail Merge using Access Tables Richard Lally Access 2 February 7th, 2005 04:48 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.