Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Is it possible to use a query that is based on a function in a mail merge?
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 |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Is it possible to use a query that is based on a function in a mail merge?
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 |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Is it possible to use a query that is based on a function in a mail merge?
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 |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Is it possible to use a query that is based on a function in a mail merge?
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. -- Peter Jamieson http://tips.pjmsn.me.uk "Shimon" wrote in message ... 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 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word Mail-merge to email looses BG Color on Web-Based Mail | Mailmerge | |||
Mail Merge and MS Query | Microsoft Word Help | |||
Mail Merge Query | Mailmerge | |||
mail merge query | Mailmerge | |||
how do I set up a query for my mail merge | Mailmerge |