Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Set a field before running query
I'm still trying to understand how Word is deals with DataSources
so I am confused. How can I deal with multiple DataSources (the Parameters table and the queryA query) before Word tries to start merging? Word cannot really use two separate data sources in the same merge, so you need to create a single data source. Either you do that by creating a new query in Access that joins the existing query and the parameters table in the way that you need, or you issue the required SQL query directly from Word. Right now in AutoOpen(), I'm getting stuck at OpenDataSource: ActiveDocument.MailMerge.OpenDataSource _ Name:="d:\member\memberSW.mdb", _ LinkToSource:=True, _ ReadOnly:=False, _ AddToRecentFiles:=False, _ SQLStatement:="Select * From Parameters", _ Connection:="TABLE Parameters", _ Revert:=False The above opens a "Select Table" window and it is using another .mdb file. Using the "Database" Dropdown list, I have to select "memberSW.mdb". Then, because it's a link, I have to use Options and select Synonyms. After I select Parameters I get prompted with multiple "Invalid Merge Field" windows. Which apparently is because Word is trying to tie my MergeField definitions with the Parameters table (which doesn't match). But the thing is, why am I getting the "Select Table" window in the first place? Why doesn't the OpenDataSource do what I think it should do? Sorry I've been away so have lost the thread (and I won't be fully up and running again for a couple of days). Which version of Word are we talking about here? Unfortunately it isn't at all easy to work out the parameters you need for OpenDataSource - it is not obvious what values you need to provide. But if you are using WOrd 2002 or later and you want to connect to a given table or query, try using ActiveDocument.MailMerge.OpenDataSource _ Name:="d:\member\memberSW.mdb", _ SQLStatement:="Select * From [Parameters]" Forget about the COnnection=TABLE thing - that's more appropriate for an old-style DDE connection and may even confuse the OLE DB provider. If you are trying to connect via ODBC then there are two approaches: ActiveDocument.MailMerge.OpenDataSource _ Name:="", _ Connection:="DSN=mydsn;otherparameters;", _ SQLStatement:="Select * From [Parameters]", _ Subtype:=wdMergeSubtypeWord2000 where "mydsn" is the name of a "machine" DSN that is set up on the machine making the connection. If that specifies everything else you need, you won't need "otherparameters" - otherwise, you may need to specify additional stuff such as the database name. or if you have a file DSN such as c:\dsn\mydsn.dsn, use ActiveDocument.MailMerge.OpenDataSource _ Name:="c:\dsn\mydsn.dsn", _ Connection:="FILEDSN=c:\dsn\mydsn.dsn;otherparamet ers;", _ SQLStatement:="Select * From [Parameters]", _ Subtype:=wdMergeSubtypeWord2000 You may be able to find out a bit more by searching this group for e.g. Jamieson wdMergeSubtypeWord2000 or some such. I can't really help much further without knowing how your parameters and queryA queries are supposed to be related - maybe you could try constructing a query that returns the data you need from those two tables/queries. -- Peter Jamieson http://tips.pjmsn.me.uk "NeedHelp" wrote in message ... Well, I'm back after getting side tracked. So I've been playing around to get familiar with ActiveDocument.MailMerge stuff in VBA. So my current thought process is this... * The Word document is set to use queryA from xxxSW.mdb * There's a field (Year) in a Linked table (Parameters) * queryA uses Year * I want to use InputBox to allow the user to modify Year * I would use the user input and modify the Param table * After the modification, I want to "refresh" the result for queryA I'm still trying to understand how Word is deals with DataSources so I am confused. How can I deal with multiple DataSources (the Parameters table and the queryA query) before Word tries to start merging? Right now in AutoOpen(), I'm getting stuck at OpenDataSource: ActiveDocument.MailMerge.OpenDataSource _ Name:="d:\member\memberSW.mdb", _ LinkToSource:=True, _ ReadOnly:=False, _ AddToRecentFiles:=False, _ SQLStatement:="Select * From Parameters", _ Connection:="TABLE Parameters", _ Revert:=False The above opens a "Select Table" window and it is using another .mdb file. Using the "Database" Dropdown list, I have to select "memberSW.mdb". Then, because it's a link, I have to use Options and select Synonyms. After I select Parameters I get prompted with multiple "Invalid Merge Field" windows. Which apparently is because Word is trying to tie my MergeField definitions with the Parameters table (which doesn't match). But the thing is, why am I getting the "Select Table" window in the first place? Why doesn't the OpenDataSource do what I think it should do? Note: If I remove the "SQLStatement", it opens the correct file (memberSW.mdb), but still opens the "Select Table" window and there's no "Options" button to select Synonyms and the linked Parameters table. I hope this is solvable... Peter Jamieson wrote: In this case I would consider an AutoOpen or AutoNew macro. In Word you can put it in any module in the document or the template it is attached to - use AutoNew if it's in a template, and AutoOpen in a document. Then you would need to capture the value. The simplest way is to use Dim strValue As String strValue = InputBox("Prompt text for the box") To build the SQL, look at your query. Suppose it is something like SELECT [t2].* FROM [Table2] [t2] WHERE Year([t2].[PaidDate]) = theyeartheuserwants (I am assuming you do not actually need to use Table1 in this case) Then your query text can - in theory - be Dim strQuery As String strQuery = "SELECT [t2].* FROM [Table2] [t2] WHERE Year([t2].[PaidDate]) = " & strValue Then you can set up the merge data source, e.g. ActiveDocument.MailMerge.OpenDataSource _ Name:="the full pathname of your Access file.mdb", _ SQLStatement:=strQuery I say "in theory" because when a query constructed using the above technique is potentially insecure - the user may be able to enter something in the InputBox that makes Access execute a completely unexpected query. So it is advisable to validate the data - in this case, probably check that it is a credible 4-digit year. I leave you to do that. InputBox (sorry, I think I said something else before) is a rather old-fashioned way to get a user response these days, so you might want to go for a Userform instead, but if so, I suggest you find out how to do that elsewhere. There may be articles on the http://word.mvps.org site that tell you how. Yes, you could also try to grab a value that was entered in response to an ASK or FILLIN field in Word, but then it can be a bit harder to make Word run the macro at the point you want. I have to leave it to you to explore that, at least until tomorrow. Peter Jamieson "NeedHelp" wrote in message ... Peter, I would like to use the VBA approach, but I'm not sure how to get started. Are there events available where I could prompt the user to enter data when the document opens? Or a button that the user pushes to trigger something? I found a template ELGMADR.DOT which, after linking to my database, allowed me to generate a list. I see the module for the template, but I'm still not sure how I can "trigger" an event to enter data. Peter Jamieson wrote: Well... If your user has Access on their system, and you make the connection to Access using the old DDE method, you can connect to a parameter query in snip You could also consider using Word VBA's OpenDataSOurce method to issue the correct SQL (e.g. prompt for the value before the merge using a Userform or Inputline, construct the SQL, and issue it via OpenDataSource) or to EXECUTE the parameter query and pass the parameter you want (not something I have used "for real"), and possibly a couple of other approaches. Peter Jamieson |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Right justifying results in a DATABSE field code query | Mailmerge | |||
Merge Field and Query | Mailmerge | |||
Database Query on Merge field | Mailmerge | |||
Mail Merge with Access Query Date Field | Mailmerge | |||
Inserting a MS Access Query as a field using Office 2002 | Mailmerge |