If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
User Defined parameters for query when running mailmerge
Is there any to have a dialog box pop up when you run a mailmerge that will
ask for a query parameter to use to filter the list of recipients. I am doing this for a client that has very basic Word expertise and know next to nothing about Access. So I am trying to make this as user friendly as possible. |
#2
|
|||
|
|||
In essence, when Word connects to a datasource, it sends the query as an SQL
SELECT statement (even if the data source is a Word document). Assuming you are using Word VBA, what you need to do is construct the correct SQL for use in a call to OpenDataSource or the correct SQL to assign to ActiveDocument.MailMerge.DataSource.QueryString. Of those two options I think the former is probably a bit more reliable. How easy that is to do depends on how general-purpose your code needs to be. If the user has no control over the data source, and cannot use other sorting/filtering options (e.g. via the Query Options in Word 2000 and earlier or the advanced options in the Edit Recipients dialog box in Word 2002 and later), then it should be reasonably easy. If the user has more flexibility, things become much more difficult because a. the syntax of the SQL may need to be different depending on the data source (the built-in dialect that is used to get data from a Word data source is not the same as Jet SQL, for example) b. it may be more difficult to insert your bits of SQL correctly. Assuming the simple situation, typically the SQL that Word issues for an unfiltered, unsorted data source is quite simple - something like SELECT * FROM tablename where tablename may be a file path name, or may be enclosed in some form of quotes (e.g. [] for Jet SQL), and so on. Suppose what your user needs to do is enter a number and you want to select records where the field xyz is greater than that number, then the required SQL would look like SELECT * FROM tablename WHERE xyz thenumber so all you would need to do is display a VBA Userform (or e.g. use Inputbox) to get the value and add it to the end of a SELECT string consisting of "SELECT * FROM tablename WHERE xyz " It's advisable to generate the SQL required by the type of data source you are using, and follow that syntax when constructing your own queries. For example, Word tends to insert more brackets around expressions than are probably necessary, but it is probably worth playing safe and following its pattern. Unfortunately, in Word 2002 it can be difficult to inspect the Querystring because of an error in Word, but you can try setting up the connection, saving the document in HTML format, then opening it as a text file and looking for the SELECT statement Word has created. -- Peter Jamieson "Neil" wrote in message ... Is there any to have a dialog box pop up when you run a mailmerge that will ask for a query parameter to use to filter the list of recipients. I am doing this for a client that has very basic Word expertise and know next to nothing about Access. So I am trying to make this as user friendly as possible. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Add ins and user defined functions | Vicki | General Discussion | 3 | August 30th, 2004 10:28 PM |
Not Running Smoothly | Tony B. | Powerpoint | 3 | May 20th, 2004 12:33 PM |
Excel 97 User Defined Charts & Text Boxes | Ella Davis-Suggs | Charts and Charting | 1 | September 16th, 2003 12:01 PM |