Open an Access parameter query from Word

[PDJ]

I'm trying to use Word to build a table from data stored in a parameter
query in Access (both 2003). Access will open the Word doc via VBA and run
the relevant code in Word to build the table.

Can anybody let me know how best to pass the relevant variable from Access
to Word? I've searched all over for this and can't see any pointers anywhere.

Here's the code I have so far:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim databasename As String
Dim qd As QueryDef


databasename = "C:\IFA Database\1 Database\IFA Database.mdb"
Set db = OpenDatabase(Name:=databasename, ReadOnly:=False)

Set qd = db.QueryDefs("qryGPPRWLBeneficiaries")
qd.Parameters("[Forms].[frmGPP].[PolicyID]") = (PROBLEM HERE)
Set rs = qd.OpenRecordset

If there is a better way of doing this, I'd be happy to be pointed in the
right direction.

Thanks

PDJ

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?UERK?=,

Your question is unclear. Can you be more specific as to what happens when and
where? Where is the parameter for the query coming from, originally? The user?

Are you able to get your syntax to work if Word is not involved at all?

I'm trying to use Word to build a table from data stored in a parameter
query in Access (both 2003). Access will open the Word doc via VBA and run
the relevant code in Word to build the table.

Can anybody let me know how best to pass the relevant variable from Access
to Word? I've searched all over for this and can't see any pointers anywhere.

Here's the code I have so far:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim databasename As String
Dim qd As QueryDef


databasename = "C:\IFA Database\1 Database\IFA Database.mdb"
Set db = OpenDatabase(Name:=databasename, ReadOnly:=False)

Set qd = db.QueryDefs("qryGPPRWLBeneficiaries")
qd.Parameters("[Forms].[frmGPP].[PolicyID]") = (PROBLEM HERE)
Set rs = qd.OpenRecordset

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[PDJ]

--
If nothing changes, everything stays the same

Hi =?Utf-8?B?UERK?=,

Your question is unclear. Can you be more specific as to what happens when and
where? Where is the parameter for the query coming from, originally? The user?

Are you able to get your syntax to work if Word is not involved at all?

I'm trying to use Word to build a table from data stored in a parameter
query in Access (both 2003). Access will open the Word doc via VBA and run
the relevant code in Word to build the table.

Can anybody let me know how best to pass the relevant variable from Access
to Word? I've searched all over for this and can't see any pointers anywhere.

Here's the code I have so far:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim databasename As String
Dim qd As QueryDef


databasename = "C:\IFA Database\1 Database\IFA Database.mdb"
Set db = OpenDatabase(Name:=databasename, ReadOnly:=False)

Set qd = db.QueryDefs("qryGPPRWLBeneficiaries")
qd.Parameters("[Forms].[frmGPP].[PolicyID]") = (PROBLEM HERE)
Set rs = qd.OpenRecordset

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)


This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

Hi Cindy,

Thanks for the response.

I'll be as brief as I can....

On an Access form, I want to use a button to open a word doc. The Word doc
contains the code to extract a recordset from Access. However, the Access
recordset is defined by parameter query; the parameter is a field on the
Access form.

The Word code sample given above works if I hard code a value for the
parameter. Where I'm struggling is finding a way of having the code behind
the Access button pass the relevant field value over to the Word code.

Any help much appreciated.

PDJ

There's probably a really easy way to do this

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?UERK?=,

If nothing changes, everything stays the same

It's very dangerous to put this at the top of a message, then not type your
real answer at the top, as well. I almost passed right by this, thinking
uncomplimentary thoughts. Then decided I'd scroll WAY down to the end, just
to be sure.

On an Access form, I want to use a button to open a word doc. The Word doc
contains the code to extract a recordset from Access. However, the Access
recordset is defined by parameter query; the parameter is a field on the
Access form.

The Word code sample given above works if I hard code a value for the
parameter. Where I'm struggling is finding a way of having the code behind
the Access button pass the relevant field value over to the Word code.

I take it the Word document is running an Auto macro of some sort? Since your
button will always be opening the same Word document, why rename the macro in
Word and have your VBA code in Access use Application.Run to start the macro.
As long as this isn't Word97, you can pass parameters through Application.Run
to be used by the macro. (See the Run method in Word's Help files).

Other than that, your Access-VBA code would have to write the information
somewhere, such as to an INI file or to a "little" table within the database.
Somewhere where Word can extract it.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[PDJ]

Cindy,

Thanks for the help.

Apologies for making you look for my comments; I'm relatively new to the
forum and not used to the way of doing things yet.

Paul

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?UERK?=,

Apologies for making you look for my comments; I'm relatively new to the
forum and not used to the way of doing things yet.

It's always a matter of personal preference And greatly influenced by
how the software you're using to interface with the groups works! Not so
much a problem for me, as it potentially could be for you... That's the
only reason I mentioned it.

Personally, I tend to make it a judgement call, unless I'm really strapped
for time. It the content lends itself to the approach, I'll intersperse my
remarks in the quoted text. If it's a short quote (like here), I'll type
underneath. If it's a very long quote, I'll usually answer at the top, to
be sure it's visible.

Cindy Meister