Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Set a field before running query
I have a Word document linked to an Access query. In the
query, it has a predefined numeric value that I would like the user to be able to change from the Word document and the have the query refresh using the new value. Is this possible? I'm an Access programmer and have never done any programming on Word. TIA |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Set a field before running query
Can you spell this out a bit please?
Your query returns a value (let's say it returns a value of "2" in a column called "myvalue") You present that value to the user (perhaps using an ASK field) and the user may type in a different value (say, "3") What do you want to happen to that value "3"? (And are we talking about a query that returns a single row, or what?) Peter Jamieson "NeedHelp" wrote in message ... I have a Word document linked to an Access query. In the query, it has a predefined numeric value that I would like the user to be able to change from the Word document and the have the query refresh using the new value. Is this possible? I'm an Access programmer and have never done any programming on Word. TIA |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Set a field before running query
Sure thing...
I have a two tables. Table1 has a filed named Year. Table2 has fields FirstName, LastName, and PaidDate. I have a query (used by the Word document) that selects all records from Table2 in which PaidDate is within Table1.Year. I would like allow the user to set the Table1.Year field and have the query refresh using that new value. It doesn't have to be real fancy. If I have to set it up so that the user must specify the year on opening the Word document, that's OK. If the user makes a mistake he has to close and reopen, that's OK. Hope that's enough detail. Is this doable? Peter Jamieson wrote: Can you spell this out a bit please? Your query returns a value (let's say it returns a value of "2" in a column called "myvalue") You present that value to the user (perhaps using an ASK field) and the user may type in a different value (say, "3") What do you want to happen to that value "3"? (And are we talking about a query that returns a single row, or what?) Peter Jamieson "NeedHelp" wrote in message ... I have a Word document linked to an Access query. In the query, it has a predefined numeric value that I would like the user to be able to change from the Word document and the have the query refresh using the new value. Is this possible? I'm an Access programmer and have never done any programming on Word. |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Set a field before running query
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 Access which will then pop up a dialog box asking for the parameter value. However, DDE has some drawbacks (e.g. it hasn't really been supported for years, doesn't let Unicode date through, and complicates the user interaction). but if you want to try it, a. set up your query as a parameter query b. check Word Tools|Options|General|Confirm conversion at open (in Word 2007 the option is somewhere near the bottom of Word Office Button|Word options|Advanced) c. go through the connection process again, select the DDE option when offered, and select the parameter query d. when the user opens the mail merge main document in Word, Word should automatically start Access if it is not already running, and open the appropriate database if it is not already open, and connect to the query. However, when Access pops up a dialog box for the parameter, be aware that it may appear behind the Word window and that the user may need to Alt-tab to see it. If they don't, eventually they will probably see a message saying that Word has failed to connect. 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 "NeedHelp" wrote in message ... Sure thing... I have a two tables. Table1 has a filed named Year. Table2 has fields FirstName, LastName, and PaidDate. I have a query (used by the Word document) that selects all records from Table2 in which PaidDate is within Table1.Year. I would like allow the user to set the Table1.Year field and have the query refresh using that new value. It doesn't have to be real fancy. If I have to set it up so that the user must specify the year on opening the Word document, that's OK. If the user makes a mistake he has to close and reopen, that's OK. Hope that's enough detail. Is this doable? Peter Jamieson wrote: Can you spell this out a bit please? Your query returns a value (let's say it returns a value of "2" in a column called "myvalue") You present that value to the user (perhaps using an ASK field) and the user may type in a different value (say, "3") What do you want to happen to that value "3"? (And are we talking about a query that returns a single row, or what?) Peter Jamieson "NeedHelp" wrote in message ... I have a Word document linked to an Access query. In the query, it has a predefined numeric value that I would like the user to be able to change from the Word document and the have the query refresh using the new value. Is this possible? I'm an Access programmer and have never done any programming on Word. |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Set a field before running query
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 "NeedHelp" wrote in message ... Sure thing... I have a two tables. Table1 has a filed named Year. Table2 has fields FirstName, LastName, and PaidDate. I have a query (used by the Word document) that selects all records from Table2 in which PaidDate is within Table1.Year. I would like allow the user to set the Table1.Year field and have the query refresh using that new value. It doesn't have to be real fancy. If I have to set it up so that the user must specify the year on opening the Word document, that's OK. If the user makes a mistake he has to close and reopen, that's OK. Hope that's enough detail. Is this doable? Peter Jamieson wrote: Can you spell this out a bit please? Your query returns a value (let's say it returns a value of "2" in a column called "myvalue") You present that value to the user (perhaps using an ASK field) and the user may type in a different value (say, "3") What do you want to happen to that value "3"? (And are we talking about a query that returns a single row, or what?) Peter Jamieson "NeedHelp" wrote in message ... I have a Word document linked to an Access query. In the query, it has a predefined numeric value that I would like the user to be able to change from the Word document and the have the query refresh using the new value. Is this possible? I'm an Access programmer and have never done any programming on Word. |
#6
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Set a field before running query
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 "NeedHelp" wrote in message ... Sure thing... I have a two tables. Table1 has a filed named Year. Table2 has fields FirstName, LastName, and PaidDate. I have a query (used by the Word document) that selects all records from Table2 in which PaidDate is within Table1.Year. I would like allow the user to set the Table1.Year field and have the query refresh using that new value. It doesn't have to be real fancy. If I have to set it up so that the user must specify the year on opening the Word document, that's OK. If the user makes a mistake he has to close and reopen, that's OK. Hope that's enough detail. Is this doable? Peter Jamieson wrote: Can you spell this out a bit please? Your query returns a value (let's say it returns a value of "2" in a column called "myvalue") You present that value to the user (perhaps using an ASK field) and the user may type in a different value (say, "3") What do you want to happen to that value "3"? (And are we talking about a query that returns a single row, or what?) Peter Jamieson "NeedHelp" wrote in message ... I have a Word document linked to an Access query. In the query, it has a predefined numeric value that I would like the user to be able to change from the Word document and the have the query refresh using the new value. Is this possible? I'm an Access programmer and have never done any programming on Word. |
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 |