Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
James James is offline
external usenet poster
 
Posts: 113
Default MailMerge only pulls first record from SQL table when filtered

Software versions:

Word 2007
SQL 2005
Access 2007

Issue: When I attempt to do a mailmerge from within Word when linking back
to a basic table in SQL or Access and I attempt to filter on a particular
field (ie InvoiceNumber), it will only choose the very first record of the
table if there are just 10 records or thousands in it. I've not only tried
setting the filter so that the InvoiceNumber equaled the number but I've also
tried using a compound filter where the InvoiceNumber 4 and InvoiceNumber
6 in order for it to select invoice number 5 and that didn't work either, it
would simply pull the first record, no matter what the InvoiceNumber was.

I've found scattered posts on Google about this particular issue with Word
2003 and 2007 and MailMerge but none of the solutions worked. Has anyone
else seen this before and if so, what did you do to fix it?

Thanks for your help!

James
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default MailMerge only pulls first record from SQL table when filtered

I vaguely remember a similar problem, but not the discussion or any
resolution, so can you please do the following
a. open your mail merge main document and try to apply the filter.
Then go into the Word VBA editor, issue the following commands (e.g. in
the Immediate Window, which you can display by looking in the View
menu), and post the results back he

?activedocument.mailmerge.datasource.name
?activedocument.mailmerge.datasource.connectstring
and
?activedocument.mailmerge.datasource.querystring

b. tell us the data type of the InvoiceNumber column in SQL/Access

Peter Jamieson

http://tips.pjmsn.me.uk

On 07/12/2009 05:29, James wrote:
Software versions:

Word 2007
SQL 2005
Access 2007

Issue: When I attempt to do a mailmerge from within Word when linking back
to a basic table in SQL or Access and I attempt to filter on a particular
field (ie InvoiceNumber), it will only choose the very first record of the
table if there are just 10 records or thousands in it. I've not only tried
setting the filter so that the InvoiceNumber equaled the number but I've also
tried using a compound filter where the InvoiceNumber 4 and InvoiceNumber
6 in order for it to select invoice number 5 and that didn't work either, it
would simply pull the first record, no matter what the InvoiceNumber was.

I've found scattered posts on Google about this particular issue with Word
2003 and 2007 and MailMerge but none of the solutions worked. Has anyone
else seen this before and if so, what did you do to fix it?

Thanks for your help!

James

  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
James James is offline
external usenet poster
 
Posts: 113
Default MailMerge only pulls first record from SQL table when filtered

Peter,

Thank you for taking the time to respond. I apparently am not doing what
you asked correctly as I continue to get a error when attempting to run your
code. I ran the mail merge and it pulled the first record which of course
was wrong. I then went into the VBA editor and viewed the code of
"ThisDocument" which was the one I was working out of. Then I created a
quick sub and then ran it:

Sub runthis()
ActiveDocument.MailMerge.DataSource.Name
Print ActiveDocument.MailMerge.DataSource.ConnectString
Print ActiveDocument.MailMerge.DataSource.QueryString
End Sub

However, when I ran it, ".name" in the first line became highlighted and the
following error message appeared, "Compile error: Invalid use of Property".
I tried running each line by itself and it wouldn't work either. I don't
normally code VB (or VBA for that matters) so I apologize for issue.

James

"Peter Jamieson" wrote:

I vaguely remember a similar problem, but not the discussion or any
resolution, so can you please do the following
a. open your mail merge main document and try to apply the filter.
Then go into the Word VBA editor, issue the following commands (e.g. in
the Immediate Window, which you can display by looking in the View
menu), and post the results back he

?activedocument.mailmerge.datasource.name
?activedocument.mailmerge.datasource.connectstring
and
?activedocument.mailmerge.datasource.querystring

b. tell us the data type of the InvoiceNumber column in SQL/Access

Peter Jamieson

http://tips.pjmsn.me.uk

On 07/12/2009 05:29, James wrote:
Software versions:

Word 2007
SQL 2005
Access 2007

Issue: When I attempt to do a mailmerge from within Word when linking back
to a basic table in SQL or Access and I attempt to filter on a particular
field (ie InvoiceNumber), it will only choose the very first record of the
table if there are just 10 records or thousands in it. I've not only tried
setting the filter so that the InvoiceNumber equaled the number but I've also
tried using a compound filter where the InvoiceNumber 4 and InvoiceNumber
6 in order for it to select invoice number 5 and that didn't work either, it
would simply pull the first record, no matter what the InvoiceNumber was.

I've found scattered posts on Google about this particular issue with Word
2003 and 2007 and MailMerge but none of the solutions worked. Has anyone
else seen this before and if so, what did you do to fix it?

Thanks for your help!

James

.

  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default MailMerge only pulls first record from SQL table when filtered

Try creating and running a new module (Insert-Module) in the "Normal"
project like this:

Sub runthis()
debug.print ActiveDocument.MailMerge.DataSource.Name
debug.Print ActiveDocument.MailMerge.DataSource.ConnectString
debug.Print ActiveDocument.MailMerge.DataSource.QueryString
End Sub

It doesn't matter that the macro is not in your document, only that your
document is where the "selection" is (i.e. click somewhere in the main
body of your document immediately before you run the macro.

Peter Jamieson

http://tips.pjmsn.me.uk

On 07/12/2009 14:33, James wrote:
Peter,

Thank you for taking the time to respond. I apparently am not doing what
you asked correctly as I continue to get a error when attempting to run your
code. I ran the mail merge and it pulled the first record which of course
was wrong. I then went into the VBA editor and viewed the code of
"ThisDocument" which was the one I was working out of. Then I created a
quick sub and then ran it:

Sub runthis()
ActiveDocument.MailMerge.DataSource.Name
Print ActiveDocument.MailMerge.DataSource.ConnectString
Print ActiveDocument.MailMerge.DataSource.QueryString
End Sub

However, when I ran it, ".name" in the first line became highlighted and the
following error message appeared, "Compile error: Invalid use of Property".
I tried running each line by itself and it wouldn't work either. I don't
normally code VB (or VBA for that matters) so I apologize for issue.

James

"Peter Jamieson" wrote:

I vaguely remember a similar problem, but not the discussion or any
resolution, so can you please do the following
a. open your mail merge main document and try to apply the filter.
Then go into the Word VBA editor, issue the following commands (e.g. in
the Immediate Window, which you can display by looking in the View
menu), and post the results back he

?activedocument.mailmerge.datasource.name
?activedocument.mailmerge.datasource.connectstring
and
?activedocument.mailmerge.datasource.querystring

b. tell us the data type of the InvoiceNumber column in SQL/Access

Peter Jamieson

http://tips.pjmsn.me.uk

On 07/12/2009 05:29, James wrote:
Software versions:

Word 2007
SQL 2005
Access 2007

Issue: When I attempt to do a mailmerge from within Word when linking back
to a basic table in SQL or Access and I attempt to filter on a particular
field (ie InvoiceNumber), it will only choose the very first record of the
table if there are just 10 records or thousands in it. I've not only tried
setting the filter so that the InvoiceNumber equaled the number but I've also
tried using a compound filter where the InvoiceNumber 4 and InvoiceNumber
6 in order for it to select invoice number 5 and that didn't work either, it
would simply pull the first record, no matter what the InvoiceNumber was.

I've found scattered posts on Google about this particular issue with Word
2003 and 2007 and MailMerge but none of the solutions worked. Has anyone
else seen this before and if so, what did you do to fix it?

Thanks for your help!

James

.

  #5   Report Post  
Posted to microsoft.public.word.mailmerge.fields
James James is offline
external usenet poster
 
Posts: 113
Default MailMerge only pulls first record from SQL table when filtered

Peter,

I may be able to save you some time. I actually found a post that you had
replied to some 3 1/2 years ago in the forum entitled, "Word
2003/Access2000/SQLSVR". I read through the entire posting and am having the
identical issue with Word 2007/Access 2007/SQLSVR2005. So it appears to be
something in Microsoft's code that they never addressed--probably not enough
complaints.

While it is not the cleanest way to do it, I've simply created a query in
Access for the client to put in an invoice number which in turn creates a
single row table in SQL for them to run a non-filtered mail merge from Word.
It works and I realize there is an opportunity for an issue to arise if more
than one person tries to merge at a time but that will be a very, very rare
occassion.

Thanks again for your help!

James

"Peter Jamieson" wrote:

I vaguely remember a similar problem, but not the discussion or any
resolution, so can you please do the following
a. open your mail merge main document and try to apply the filter.
Then go into the Word VBA editor, issue the following commands (e.g. in
the Immediate Window, which you can display by looking in the View
menu), and post the results back he

?activedocument.mailmerge.datasource.name
?activedocument.mailmerge.datasource.connectstring
and
?activedocument.mailmerge.datasource.querystring

b. tell us the data type of the InvoiceNumber column in SQL/Access

Peter Jamieson

http://tips.pjmsn.me.uk

On 07/12/2009 05:29, James wrote:
Software versions:

Word 2007
SQL 2005
Access 2007

Issue: When I attempt to do a mailmerge from within Word when linking back
to a basic table in SQL or Access and I attempt to filter on a particular
field (ie InvoiceNumber), it will only choose the very first record of the
table if there are just 10 records or thousands in it. I've not only tried
setting the filter so that the InvoiceNumber equaled the number but I've also
tried using a compound filter where the InvoiceNumber 4 and InvoiceNumber
6 in order for it to select invoice number 5 and that didn't work either, it
would simply pull the first record, no matter what the InvoiceNumber was.

I've found scattered posts on Google about this particular issue with Word
2003 and 2007 and MailMerge but none of the solutions worked. Has anyone
else seen this before and if so, what did you do to fix it?

Thanks for your help!

James

.



  #6   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default MailMerge only pulls first record from SQL table when filtered

Thanks for the pointer.

I'm glad you've found a workaround for your situation because I doubt
very much that I'll get to the bottom of this one. The main questions
(not for you, just general ones) I have are
a. does the QueryString generated by Word include a WHERE clause? In
the conversation you referenced, it didn't, which leads to the questions...
b. what exactly is doing the filtering in this case and
c. how is Word telling it what to filter?

When the connection to the SQL server is direct (i.e. not via Access), I
think most people would expect Word to send a SELECT statement with a
WHERE clause to SQL Server, SQL Server to assemble the result set, and
return it to Word, in the usual way. And in the original conversation,
that approach was tried and the problem still occurred. It is just
possible that some filtering work might be done by the OLE DB
provider/ODBC driver, but the only other thing likely to do it would be
the "Office Data Source Object" which Word uses to get data from OLE DB
sources and which seems to have a number of defects. I wonder if it is
getting a complete result set, then using something like ADO to filter
the results and getting it wrong (I don't think that would be very
hard). I suppose it would be possible to get more info. by examining the
SQL Server's logs and seeing what queries were actually submitted to it.

Even if that is correct, unfortunately this really only brings the
question back to "why does the ODSO get it wrong, and only get it wrong
sometimes?' But At least it might be possible for the Office people to
fix it.

Peter Jamieson

http://tips.pjmsn.me.uk

On 07/12/2009 17:28, James wrote:
Peter,

I may be able to save you some time. I actually found a post that you had
replied to some 3 1/2 years ago in the forum entitled, "Word
2003/Access2000/SQLSVR". I read through the entire posting and am having the
identical issue with Word 2007/Access 2007/SQLSVR2005. So it appears to be
something in Microsoft's code that they never addressed--probably not enough
complaints.

While it is not the cleanest way to do it, I've simply created a query in
Access for the client to put in an invoice number which in turn creates a
single row table in SQL for them to run a non-filtered mail merge from Word.
It works and I realize there is an opportunity for an issue to arise if more
than one person tries to merge at a time but that will be a very, very rare
occassion.

Thanks again for your help!

James

"Peter Jamieson" wrote:

I vaguely remember a similar problem, but not the discussion or any
resolution, so can you please do the following
a. open your mail merge main document and try to apply the filter.
Then go into the Word VBA editor, issue the following commands (e.g. in
the Immediate Window, which you can display by looking in the View
menu), and post the results back he

?activedocument.mailmerge.datasource.name
?activedocument.mailmerge.datasource.connectstring
and
?activedocument.mailmerge.datasource.querystring

b. tell us the data type of the InvoiceNumber column in SQL/Access

Peter Jamieson

http://tips.pjmsn.me.uk

On 07/12/2009 05:29, James wrote:
Software versions:

Word 2007
SQL 2005
Access 2007

Issue: When I attempt to do a mailmerge from within Word when linking back
to a basic table in SQL or Access and I attempt to filter on a particular
field (ie InvoiceNumber), it will only choose the very first record of the
table if there are just 10 records or thousands in it. I've not only tried
setting the filter so that the InvoiceNumber equaled the number but I've also
tried using a compound filter where the InvoiceNumber 4 and InvoiceNumber
6 in order for it to select invoice number 5 and that didn't work either, it
would simply pull the first record, no matter what the InvoiceNumber was.

I've found scattered posts on Google about this particular issue with Word
2003 and 2007 and MailMerge but none of the solutions worked. Has anyone
else seen this before and if so, what did you do to fix it?

Thanks for your help!

James

.

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
inserting the DATABASE field in a mailmerge with filtered reults Rich Mailmerge 11 September 24th 09 11:53 PM
Mailmerge: First Record in stead of Next Record Guus Mailmerge 6 February 6th 07 04:00 PM
Mailmerge only merges picture from 1st record, can anyone help? Thulo Shrestha Mailmerge 1 April 7th 06 06:01 AM
Mailmerge Next record John T Mailmerge 1 March 2nd 06 01:10 PM
Mailmerge always grabs the first record in Access table Darshan Khalsa Mailmerge 1 January 3rd 06 08:18 PM


All times are GMT +1. The time now is 11:59 AM.

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

About Us

"It's about Microsoft Word"