SQL query issue (MailMerge.OpenDataSource)

[hazz_London]

I have tried Yes and -1. Both behave in the same way as True.

The OpenDataSource works fine (no errors), but it just doesn't pick up the last line of the SQL statement.

Interestingly, when you open up the recipients list you can manually filter the entries by "SecondaryMarketSup = TRUE" (there is a drop down to manually filter). HOWEVER, when you do that it drops the "CompanyID = " & MyClientID " bit driving the recipients list, so you end up with a list where "SecondaryMarketSup = TRUE", but for every "CompanyID" in our database (as opposed to just the ones for "CompanyID = " & 47.

This makes me wonder whether the mailmerge function in Word just doesn't like filtering by 2 criteria at a time...

 

[vbaInet]

It must be a Word automation issue then. There must be explanation to it.

I would still go for the second query option. Apply your criteria in QUERY 1, pull all the fields from query 1 into query 2. Use QUERY 2 as the source of your query. Query 2 should contain no criteria.

 

[hazz_London]

Sorry I don't quite follow your last post. (Am still learning this VBA stuff as I go along!)

Would you mind elaborating slightly?

H

 

[vbaInet]

Maybe if you tell me the name of the problem query I can explain using that.

 

[hazz_London]

Sorry - I've lost you. 'Name of the problem query'. I just assigned the query statement to a string (strSQL) in VBA and then passed it to the MailMerge.OpenDataSource method. I don't think it has a name (other that f&*£ing annoying statement that doesn't want to work!).


I also found something else that might be useful at this link: http://msdn.microsoft.com/en-us/library/aa140183(office.10).aspx

In particular:

As a developer, you have an alternative. You can get around this limitation using the Connect, SQLStatement, and SQLStatement1 arguments of the OpenDataSource method. The SQLStatement and SQLStatement1 arguments each allow a maximum of 255 characters in the string, which, while limited, at least doubles the capacity MSQuery is allowed.

I have counted the characters in my SQL statement and its 292 (no spaces) 311 (with spaces).

When I remove the second WHERE clause its 241 (no spaces) 256 (with spaces).

The trouble now is that I can't really change the table names without creating a lot of work on our database.

Oh the joy.

Thanks again for your input.

 

[hazz_London]

Maybe I can use both the SQLStatement and SQLStatement1 arguments of the MailMerge.OpenDataSource method. Will try to work out how that is done.

 

[vbaInet]

We might be able to overcome this limitation. Where does MyClientID get its value from? A textbox control on a form?

Edit: If I had looked carefully I would have seen it. The value is static in your code, set as 47. You mentioned that this is meant to be dynamic, so would it get its value from a control?

 

[hazz_London]

At the moment it comes straight from the code (will eventually come from somewhere else)

He is the latest version of the code. I have split the SQL into two sections (strSQL and strSQL1) to get under the 255 character limit, but it still doesn't implement both aspects of the WHERE clause.

See what you think.

H

Sub SendByMailMerge()

Dim wdApp As Word.Application
Dim wdDoc As Word.Document

Dim strSQL As String
Dim strSQL1 As String
Dim MyClientID As String
Dim strConnection As String

Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.ActiveDocument


MyClientID = 47

strSQL = "SELECT tbl001_ClientContactStatus.SecondaryMarketSup,tbl001_Contact.EmailAddress1 "
strSQL = strSQL & "FROM tbl001_Contact "
strSQL = strSQL & "INNER JOIN tbl001_ClientContactStatus "
strSQL = strSQL & "ON tbl001_ClientContactStatus.ContactID = tbl001_Contact.ContactID "

strSQL1 = strSQL1 & "WHERE tbl001_ClientContactStatus.CompanyID = " & MyClientID
strSQL1 = strSQL1 & " AND tbl001_ClientContactStatus.SecondaryMarketSup = True"
strSQL1 = strSQL1 & ";"

Debug.Print strSQL & strSQL1

strConnection = "DSN=MS Access Databases;" _
        & "DBQ=Z:\PROBASE\ADMIN\SH_Management_BE.mdb;" _
        & "FIL=RedISAM;"

Debug.Print strConnection

With wdDoc.MailMerge

If ActiveDocument.MailMerge.DataSource.Name <> "" Then _
    MsgBox ActiveDocument.MailMerge.DataSource.Name
    
.OpenDataSource Name:="", _
Connection:=strConnection, _
SQLStatement:=strSQL, _
SQLStatement1:=strSQL1, _
SubType:=MergeSubTypeWord2000




End With

wdDoc.MailMerge.Destination = wdSendToEmail

With wdDoc
If .MailMerge.State = wdMainAndDataSource Then
Dialogs(wdDialogMailMergeRecipients).Show
End If
End With

'wdDoc.MailMerge.Execute

Set wdApp = Nothing
Set wdDoc = Nothing

End Sub

 

[hazz_London]

Here are the debug.prints:

The top one is the combined SQL statement. The lower one is the connection string.

SELECT tbl001_ClientContactStatus.SecondaryMarketSup,tbl001_Contact.EmailAddress1 FROM tbl001_Contact INNER JOIN tbl001_ClientContactStatus ON tbl001_ClientContactStatus.ContactID = tbl001_Contact.ContactID WHERE tbl001_ClientContactStatus.CompanyID = 47 AND tbl001_ClientContactStatus.SecondaryMarketSup = True;
DSN=MS Access Databases;DBQ=Z:\PROBASE\ADMIN\SH_Management_BE.mdb;FIL=RedISAM;

 

[vbaInet]

At the moment it comes straight from the code (will eventually come from somewhere else)

But where's this somewhere else? A textbox control or similar?

 

[hazz_London]

I have a macro that runs from Outlook. That macro looks at the subject line of the current email and compares that to our client list, which is located in our Access database. The macro then opens a Word template and populates various bits of information into the template. The document is then saved, ready for the user to email out*.

Each client has a ClientID, so my plan was to somehow store the ClientID in the saved version of the Word document so that it can be called upon when the user uses this macro to then automatically mail merge the document to the email address list for that Client.

H


*FYI - in the old version of the macro, it pulled a list of email addresses (using the same SQL statement I have shown in previous posts on this thread) and sticks them in the BBC section of the MailEnvelope, ready for the user to press "send a copy". That worked fine, but now some of the email lists have over 100 entries and therefore get blocked as spam by some email servers. I'm hoping that sending by MailMerge will mean that they are treated as individual emails (at least that's what I read elsewhere).

 

[hazz_London]

Just to be clear - each client has its own email list.

 

[vbaInet]

Why I asked if MyClientID is coming from a textbox is because you can put that sql statement into a query and in the criteria you point to the textbox like this:

[Forms]![NameOfForm]![NameOfTextbox]

Let's say you called the query NewQuery, you can use that new query as the source of your merge like:

strSQL = "SELECT * FROM NewQuery;"

You see the benefit now? Access validates your problem query and all your source will be pulling from that validated query.

 

[hazz_London]

I see your logic. Just not entirely sure how to assign my statement to "NewQuery - would that be done in MSQuery? or can you do it directly in VBA?

 

[vbaInet]

It will be done via the query builder in Access.

Follow the query wizard, select the tables and you will see where to put the criteria.

 

[hazz_London]

Of course. I'll see what I can do - our database was written externally, so I will need to get full developer rights first.

Hopefully that will do the trick - i'll let you know.

Thanks again.

H

 

[vbaInet]

No problemo!

Good luck!

 

[pbaldy]

I won't pretend to have read all the posts, but as to the length problem you can alias the tables and save quite a few characters:

SELECT A.FieldName, B.FieldName FROM FullTableName AS A INNER JOIN OtherTableName AS B ON A.Whatever = B.Whatever

If that was mentioned already and I missed it, my apologies.

 

[hazz_London]

Very helpful pbaldy.

Using the abreviated table names gets the character count down to about 180. More importantly, it means that the query works properly.

Thanks guys - this problem has been bugging me for quite a while!

H

 

[pbaldy]

Happy to help.