Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

Initiating a Word mail merge from Access using a query with parameters

Hi,

I have a form which which prompts the user for a date range and a command button which creates a Word object, opens a template I've written, sets an Access query as its data source, and does the mail merge (based on the code in http://support.microsoft.com/?kbid=285332).

Now, the Access query takes the date values in the two textboxes to run the query. MS Word obviously can't see what these date values are (or the query at all) because you're setting the data source directly in code.

What's the best way of getting Word to use a parameterized Access query and populate the parameters with the values entered in the form? Is there a better way of doing it than dropping the query in VBA and creating it again, hardcoding the dates? Hope I've explained myself well. Any thoughts appreciated. Thanks.
0
gafffe
Asked:
gafffe
  • 3
  • 3
  • 2
1 Solution
 
jefftwilleyCommented:
You want the dates that the user entered on the form to show up somewhere in the Word doc?
0
 
jefftwilleyCommented:
Looking at the link you pasted, is this where you're having the problem?

With oMainDoc.MailMerge

        .MainDocumentType = wdFormLetters
       
        'Set up the mail merge data source to Northwind.mdb.
        sDBPath = "C:\Program Files\Microsoft Office\" & _
                  "OfficeXP\Samples\Northwind.mdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [Customers]"  <------------ You want to put your parameters from the form here?
0
 
Leigh PurvisDatabase DeveloperCommented:
This is one of the reasons why I always run mailmerges based on tables.
A front end local table - using the query to append the necessary records into that table before running the mailmerge.

It's generally speaking noticably faster too.  (The data update operations before executing the merge are effectively instantaneous compared to the automation that follows).

(You can make your query into a Make Table query to create your merge table at first - then once it exists you can change it to an Append query - deleting the records in the table from the previous merge immediately before you append at each merge time).
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
gafffeAuthor Commented:
Thanks for replying - yeah, I'd like to put the parameters where you indicated if that will work. After searching for a bit I'm not able to figure out how to do that though.
0
 
jefftwilleyCommented:
We'll help then.
First set up a couple of variables that will be the date values from your form.
dtFrom as date
dtTo as date

dtFrom = me.Fromdatefieldname
dtTo = me.Todatefieldname

'Here you want to make sure that these fields both have a valid value. If they are null, or contain some value you can't use, then you need to stop the process and tell the user to put in viable dates
if isnull(dtFrom) or isnull(dtTo) or dtFrom = "" or dtTo = "" then
     msgbox"You need valid dates in those fields"
      exit function
end if

Now that you have those, you can build your SQL statement that will pass these to the query on the fly.

With oMainDoc.MailMerge

        .MainDocumentType = wdFormLetters
       
        'Set up the mail merge data source to Northwind.mdb.
        sDBPath = "C:\Program Files\Microsoft Office\" & _
                  "OfficeXP\Samples\Northwind.mdb"
        .OpenDataSource Name:=sDBPath, _
           SQLStatement:="SELECT * FROM [YourTable] where [datefield] between #" & dtFrom & "# And #" & dtTo & "#)""

this will create the recordset based on the criteria on the form. Let me know if you run into a snag.
J

0
 
gafffeAuthor Commented:
Thanks Jeff - that worked.
0
 
Leigh PurvisDatabase DeveloperCommented:
Glad you're sorted.
Requiring Word to perform a DDE query on your Select statement will be that bit slower still than a direct table ;-)

But not to worry - you can always "fiddle" with it once you're finished.
0
 
gafffeAuthor Commented:
Ok. If I ever do this myself from scratch I'll do it through a table :-)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now