Access 2003 Mail Merge to Word 2003 using query to locate the specific record being displayed

E

ehorde

After spending ALL DAY searching the entire WEB I have found a lot of
posts asking this same question but not one answer.

I thought I's throw it up again.

The button on my form does this. * From Mergit() *

Dim objWord As Word.Document
Set objWord = GetObject("H:\Contract Admin Database\Merge Documents
\WIP\Closings\Closing Packet Documents\Test - closing packet EM.doc",
"Word.Document")
objWord.Application.Visible = True
' Set the mail merge data source as the Northwind database.
objWord.MailMerge.OpenDataSource _
Name:="H:\Contract Admin Database\Development\CA.mdb", _
LinkToSource:=True, _
Connection:="Query Q_CA_MergeDocsMaster", _
SQLStatement:="SELECT * FROM [Q_CA_MergeDocsMaster] WHERE
((Q_CA_MergeDocsMaster.Job_Key)=[Forms]![WordMerge]![Job_Key])", _
SubType:=wdMergeSubTypeWord2000
objWord.MailMerge.Execute

The Word document opens and the merge even executes, but totally
ignoring the WHERE clause.
Can anyone tell me why the WHERE part is ignored by the merge?
 
P

Pieter Wijnen

Not done that myself but maybe
Connection:="Query Q_CA_MergeDocsMaster"
overrides
SQLStatement:="SELECT * FROM [Q_CA_MergeDocsMaster] WHERE
((Q_CA_MergeDocsMaster.Job_Key)=[Forms]![WordMerge]![Job_Key])"

?
can't see the need for both

Pieter
 
A

Albert D. Kallal

WHERE
((Q_CA_MergeDocsMaster.Job_Key)=[Forms]![WordMerge]![Job_Key])", _
SubType:=wdMergeSubTypeWord2000
objWord.MailMerge.Execute

The Word document opens and the merge even executes, but totally
ignoring the WHERE clause.
Can anyone tell me why the WHERE part is ignored by the merge?
Click to expand...


What happens if you need to search for a job key names [forms]![WordMerge]

How would you search for text called "[forms]", and not the value
represented by the form?

Word does not know about forms, and forms expression in ms-access. You have
to correct form the sql,a and sub-mit that sql to word.

In other words, build the correct sql *string* FIRST...

dim strSql as string

strSql = "SELECT * FROM [Q_CA_MergeDocsMaster] WHERE " & _
" Job_Key = " & [Forms]![WordMerge]![Job_Key]

furhter, if job key is a string value, the you need to add quotes:

strSql = "SELECT * FROM [Q_CA_MergeDocsMaster] WHERE " & _
" Job_Key = '" & [Forms]![WordMerge]![Job_Key] & "'"

I *think* single quotes will work with word, but I not sure...

The next line of code you need (for testing) is:

debug.print strSql
msgbox strSql

Then, you can use your word informaton...

SQLStatement:= strSql

The "display" of the sql and the debug.print can be removed when you get it
working.

On the other hand, you opening up a can of words and allowing word to attach
to the mdb file is a source of problems.

I would suggest you try my following sample code. To merge your current
record, your code would be


MergeSingeword.

Yup...one line of code!!!


The sample I have can be found here:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

What is nice/interesting about my sample is that is specially designed to
enable ANY form with ONE LINE of code....

Thus, each time you build a new form, you can word merge enable it with
great ease, and not have to write a lot of new code...
 
E

ehorde

After spending ALL DAY searching the entire WEB I have found a lot of
posts asking this same question but not one answer.

I thought I's throw it up again.

The button on my form does this. * From Mergit() *

Dim objWord As Word.Document
Set objWord = GetObject("H:\Contract Admin Database\Merge Documents
\WIP\Closings\Closing Packet Documents\Test - closing packet EM.doc",
"Word.Document")
objWord.Application.Visible = True
' Set the mail merge data source as the Northwind database.
objWord.MailMerge.OpenDataSource _
Name:="H:\Contract Admin Database\Development\CA.mdb", _
LinkToSource:=True, _
Connection:="Query Q_CA_MergeDocsMaster", _
SQLStatement:="SELECT * FROM [Q_CA_MergeDocsMaster] WHERE
((Q_CA_MergeDocsMaster.Job_Key)=[Forms]![WordMerge]![Job_Key])", _
SubType:=wdMergeSubTypeWord2000
objWord.MailMerge.Execute

The Word document opens and the merge even executes, but totally
ignoring the WHERE clause.
Can anyone tell me why the WHERE part is ignored by the merge?
Click to expand...

Update, even though it opens the document Access eventually errors out
and says - "Run Time Error '5922': Word was unable to open the data
source.

I think the merge data coming in is from the document's pre-defined
original datasource.
 
A

Albert D. Kallal

I think the merge data coming in is from the document's pre-defined
original datasource.
Click to expand...

*excellent* obseation on your part.....

just run the word macro reocrder..and open up the mdb file...you see how it
sets the data source...

eg:

I tried the word macro recorder, you get somting like:

Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=C:\Documents and Settings\Albert\My
Documents\db5_be.mdb;Mode=Read;Extended Properties="""";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database
Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `Contacts`", SQLStatement1:="",
SubType:= _

so, you have to add in the connection string to tell word the path name.

And, as mentioned, the word merge can't use open forms...it is not
ms-access, and it is NOT aware of values and data in form. You have to pass
word CORRECTLY formed sql, with no references, or expression to forms......

Or, you can use my sample code, and write ONE line of code....
 
E

ehorde

And, as mentioned, the word merge can't use open forms...it is not
OK, let me get this straight "word merge can't use open forms"?
Is that true?
If so, it explains all the frustration I see posted from other people
trying to do this.

I downloaded it, I will give it a try.

Thanks,

Eric
 
A

Albert D. Kallal

OK, let me get this straight "word merge can't use open forms"?
Is that true?
Click to expand...

Yes, word can't assume that you have ms-access installed. Further, a form is
code designed by you the developer. Word can't launch that form and run it.
That form might email home, empty your bank account out, who knows, maybe
that form is a video game.....

You have to supply valid sql to that word merge. It is word that running the
sql here. It knows nothing about ms-access, or simply accounting, or
whatever.....

Word cannot be expected to run, load, and know how to execute any piece of
software on your computer.

However, YOUR CODE can simply pull the needed values form the form, and THEN
supply the correct sql in code to word. (so, while a VERY minor difference
in coding, it is a GRAND canyon of difference in what one is asking of
word).

so,

strSql = "select * from table
where company = forms!Cust!Company" <-- not good

strSql = "select * from table
where company = '" & forms!Cust!Company & "'" <-- this ok


So, if you use code in ms-access to RESOLVE the 7 to a value, then your home
free.

Try using a msgbox command to display the above two examples in ms-access,a
and you INSTANT understand what is occurring here.

Anyway, my sample takes care of this stuff, don't forget the step by step
instructions on how to use my sample in your existing application, they can
be found here:

http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html
 
E

ehorde

Albert,

Your program looks really slick. I think it will work nicely for us.
I am running into a problem though.

When I try to "Add New Template" I am getting:

Run-Time error: '-2147417851 (80010105)':
Method 'Add' of object 'Documents' failed

Clicking Debug highlights this code:

Set WordDoc = wordApp.Documents.Add

I followed your instructions perfectly for the install.
 
E

ehorde

Albert,

Your program looks really slick. I think it will work nicely for us.
I am running into a problem though.

When I try to "Add New Template" I am getting:

Run-Time error: '-2147417851 (80010105)':
Method 'Add' of object 'Documents' failed

Clicking Debug highlights this code:

Set WordDoc = wordApp.Documents.Add

I followed your instructions perfectly for the install.
Click to expand...

Got it working by adding this...

MergeSingleWord "H:\Contract Admin Database\Merge Documents
\Documents", True

Albert, you should get some kind of award for this code. Best stuff
I've seen yet.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Email Mail Merge from Access 2003 3
User defined type not defined error in mail merge from Access 2003 1
Q: simple Access to Word mail merge 8
Only merge a new record, not all 2
Mail Merge with word Automatical 3
Mail merge to Word from Access 2000 1
password problems in a mail merge 1
Saving Word file to disk automatically 2

Top