Mail Merge in Word Using a variable (strSQL) to filter the results

[ProNetGroup]

Hi,

The task that I am trying to accomplish is to have a search form in Access that creates a query on the fly (based on user selection) and then filters the results based on that query. Then, I need a button that can automatically open up the form letter template and do a mail merge based on the query that was made on the fly. This query is stored in a string variable, strSQL.

So far, I have been able to make it work to where it can print out the record that it is sitting on only (by creating a query in the database that shows only the record on the screen at the time) or all records in the whole table. I figure that there *should* be some way to pass on that query string to specify which records but I just can't seem to figure it out.

Also, currently I am using the Shell method to open Word and then open the template, but I was wondering if that was the best way. Based on all the postings on this web site, I also tried using objects to do it. I had it working for a while, and then I screwed it up so I bailed on that method until I figure it out fully. Also, after using the Shell to open word and the document, I called the macro that I had created in Word (I copied and pasted the Sub from Word VBA to Access VBA) and it ran fine for a while, but now it blows up on me, saying that the document isn't open.

Any suggestions on any of this? I know that this is alot, but I have spent almost 3 or 4 days trying to search the web site to figure out some of the answers to these questions and finally got frustrated and wrote my own.

Any help is GREATLY appreciated!!!!!
Thanks!

Eubanks

 

[Orion45]

I'm not doing exactly the same thing you are but its pretty similar. I use a stored query (WordMergeParam_qry) to pull information off four tables based on a system parameter I get from the user. I then have the user select the form letter they want to output to and send the data to Word using the following code.

Public Function MergeIt(doc As String)
    Dim objWord As Word.Document
    doc = "J:\BEA\AppLetters\" & doc
    Set objWord = GetObject(doc, "Word.Document")
    ' Make Word visible.
    objWord.Application.Visible = True
    ' Set the mail merge data source as the personnel database.
    objWord.MailMerge.OpenDataSource _
    Name:="C:\Documents and Settings\Desktop\CurrentDatabaseVer.mdb", _
    LinkToSource:=True, _
    Connection:="QUERY WordMergeParm_qry"
    ' Execute the mail merge.
    objWord.MailMerge.Execute
   End Function

I hope this helps some.
Ryan

 

[ProNetGroup]

Orion45-

Thanks for the reply, I am about to check out this code and see if I can get it to work, but I had one question for you...is the "WordMergeParam_qry" a query stored and created in access or a variable in VBA? Or does this question even make sense??

Thanks again!

 

[ProNetGroup]

Alrighty, I tried your code, and I'm running into a small problem. For one thing, it said that it couldn't open the data source...I double-checked the directory that I typed in, but it still wouldn't work. Secondly, it is opening another instance of Access, which I would not like it to do. Do know any other commands where it doesn't actually "open" the data source but rather just finds it?

Thanks again for your help, and any other tips are always appreciated!!!

 

[Orion45]

I'm running into a similar problem myself. The code was originally designed and run on a desktop version and it worked like a charm. However, when I copied it to my C drive and changed the links it tries to open three instances of Access. I'll let you know if I find anything new but right now this has me pretty confused.

 

[Orion45]

Ok, I think I got it. The problem is the Word document! When opened from Access the file is trying to run the Access datasource all by itself by opening a new instance. You need to create a word file with merge feilds but no data source attached to it. I also modified the links in the code to be a little more dynamic.

Public Function MergeIt(doc As String)
    Dim objWord As Word.Document
    doc = "J:\BEA\AppLetters\" & doc
    Set objWord = GetObject(doc)
    ' Make Word visible.
    objWord.Application.Visible = True
    ' Set the mail merge data source as the personnel database.
    objWord.MailMerge.OpenDataSource Name:=Application.CurrentProject.FullName, LinkToSource:=False, Connection:="QUERY WordMergeParm_qry"
    ' Execute the mail merge.
    'objWord.MailMerge.Execute
   End Function

I really hope this helps, good luck.
Ryan

 

[ProNetGroup]

Thanks alot! This worked beautifully for me. But I do have another question to see if you know the answer or not. This process is much better than what is currently happening (i.e. typing in the individual names each time), but I was wondering if there was any way that you could use a query created on the fly in the database to filter the records and then create letters for all those. Right now, I created a query that grabs all the info and then filters it by whatever is showing on the screen at the time (although there are multiple records within that form). So, I am creating a SQL statement based on these different options, for instance:

UtilityName
County
Region
Population

So the user can select if they want all the Utilities in a since County, Region, or of a certain population, etc. I have this SQL statement created and then stored in a public variable, but is there a way to say "Use that SQL statement stored in that variable" in Word to use as the data source?

Any information would be helpful, and again, thanks for the tip b/c it works wonders!!!

Eubanks

 

[ProNetGroup]

On another note, I was hoping to also automatically program the user to save the document whereever they would like, naming it whatever they would like, then close that document and close the original mail merge template automatically with no warnings, and then quit Word altogether. Any ideas on this either?

 

[Orion45]

I haven't tested this but I think you can change the OpenDataSource call to include a SQL statement. You should be able to insert your string SQL variable here.

objWord.MailMerge.OpenDataSource _
            Name:="C:\My Documents\Personnel_1.3", _
            LinkToSource:=True, _
            SQLStatement:="Select * from [PersonInformation] where sys_PersonInformation_ID = Me![txtID]"

You can close the word document by using this statement at which point Word will ask them to save changes if they've made any.

If WordWasNotRunning = True Then
        objWord.Application.Quit
End If

Your access form can then be closed by stating:
DoCmd.Close acForm, "YourFormName_form"

 

[ProNetGroup]

Thanks so much again, I have taken a giant leap forward in getting this document automation going!!! Your advice worked awesome and I couldn't have done it without knowing that you need to use a "SQLStatement" tag rather than "query".

And just for your information if you ever wondered (it took me forever to figure it out), you can also use a global public variable to store a SQL statement and use it in the mail merge. Opening the data source in VBA would look like this:

objWord.MailMerge.OpenDataSource _
Name:="G:\Personnel\Emily\Refresher Database\Water Assessment.mdb", _
LinkToSource:=True, _
SQLStatement:=strTempSQL

where strTempSQL contains a select SQL statement.

 

[ordendelfai]

Hey, this is great information!

I posted a long thread earlier, and this answers a lot of it. I made the query criteria based on [forms].[myform].[txtbox] or [forms].[myform].[txtbox2],etc and it filtered just what was entered in the text box's.

Now, what do I do if I want to have the automation merge more then one word.doc right after it merges the first one based on two check boxes (if only one box is checked, then the code will merge one word doc based on that check box, but if both are checked, it will merge two different word docs (of course using the paramitized criteria in the query)