Is it possible to use a query that is based on a function in a mail merge?

[Shimon]

I have a word docment that imports data from access. When the query is bases
on a number it wrks fine, but when I use a function in the query, word
responds with an error and cannot open the data source.

SELECT qselItem_List.*, qselItem_List.ReadyForTranslating
FROM qselItem_List
WHERE ( ((qselItem_List.SaleID)< CurrentAuctionNumber()))
ORDER BY qselItem_List.AutoNumber;

Here is the function that the query uses.


Public Const stCurrentAuctionNumber = 45
Public Static Function CurrentAuctionNumber() As Double
Static currAuctionNumber As Double
If tempCurrAuctionNumber = 0 Then
CurrentAuctionNumber = stCurrentAuctionNumber
tempCurrAuctionNumber = stCurrentAuctionNumber
Else: CurrentAuctionNumber = tempCurrAuctionNumber
End If
End Function

I would like to use this function, so that I can change the criteria from
tim to time by using the setCurrentAuctionNumber

Public Static Function SetTempAuctionNumber(dAuctionNumber)
tempCurrAuctionNumber = dAuctionNumber
End Function

Thanks for any help,
Shmon

 

[Peter Jamieson]

To use "User-defined" Access functions in a data source, you have to connect
to the data source using the DDE method, not OLEDB (the default in Word 2002
and later).

In Word 2002/2003, check Word Tools->Options->General->Confirm conversaions
at open, then
a. go through the process of connecting to your data source again
b. after you have selected the file in the "Select Data Source" dialog box
and clicked "Open", you should see an additional dialog box titled "Confirm
Data Source"
c. select "MS Excel Worksheets via DDE (*.xls)" and click OK.

In Word 2007, use much the same process but start by checking Office
button->Word Options->Advanced->General->Update Automatic Links at Open.

I'm not so sure this works the same way in Word 2007. Also, Access has to be
running for this to work.

The other approach is in essence to generate the data you need first, then
connect to that - e.g. you can export to another file, or probably use a
CREATE TABLE query of some kind within Access. But then you have to do that
as a separate step.

 

[Shimon]

Hi Peter,
Thanks alot. worked like a charm.
I have to change the value in Access before I open word, as once the
recordset is choosen, changeng the value of the function does not change the
recordset that word uses. That is fine with me.

As we work in a multi computer, multi-user environment, I was wondering if
there is some reg file that would cause Word to use Dde instead of Ole DB,
or to confirm conversions at open.

I also would like for the merged document to open another word document,
with the name as the autonumber field that I use to identify a record, and
import (or insert) the contents.
Is there a opendocument command that you are familiar with that would do
this?

Thanks alot,
Shimon

 

[Peter Jamieson]

Depending on what you are doing you might be able to redesign your query as
a Parameter query that requests the number you want to use and passes it to
the appropriate function(s). When you connect using DDE, Access runs the
query and should prompt for the parameter. The main snag with this is that
the parameter dialog may pop up behind Word - users can be fooled into
thinking that Word has hung up, when in fact they need to Alt-tab (or
whatever) to get to the parameter box and fill in the value.

The alternative (it looks feasible in this case but I can't really tell what
you are doing) is to put the value(s) you want to use in a tablein Access
and reconstruct your query to use that. Then you don't need a user-defined
function in Access VBA and you can go back to using OLE DB to connect. And
so on...

As we work in a multi computer, multi-user environment, I was wondering if
there is some reg file that would cause Word to use Dde instead of Ole DB,
or to confirm conversions at open.

As far as I know there is no way to specify the default connection method
and the confirm conversions setting is just a Word setting (probably held
within the "data" key in the registry, and the data key's structure is not
documented). Another approach might be to write your own Word code to open
the data source programmatically using DDE (by providing the correct
parameters in Word's OpenDataSource call). but to do that, you have to know
the name of the database and the query or table or get them from the user,
which complicates the whole exercise.