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" wrote in message
...
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.
--
Peter Jamieson
http://tips.pjmsn.me.uk
"Shimon" wrote in message
...
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