Set a field before running query

[NeedHelp]

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

 

[Peter Jamieson]

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]

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]

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]

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]

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]

Peter... just wanted to drop a quick note before I take off for the day...
Thank you for your assistance. You've given me a big leap forward
to continue my education!

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

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.

 

[NeedHelp]

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...

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

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]

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;otherparameters;", _
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

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...

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

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