I have a more specific question, as I am close to solving my problem 
(Access 97).  The below example is simplified. 

I have a query named "qryMailMerge" with two fields:

GroupNumber
TelephoneNumber

In the query I have the criteria for the field GroupNumber as: [forms].
[frmMerge].[txtbox]

The form (frmMerge) has one textbox where a user can enter a group 
number, and then click a button to merge the pertinent data into a Word 
Doc for that Group Number only.

When I have the form open, and enter group number "54312", then manually 
Run the Query, it returns the telephone number for ONLY that group number 
as it should.

Using the below vba code to automate the Mail Merge process, I figured 
that when the button is clicked (running this code), Word would open the 
Doc, and only that group number would merge (the Word Doc has no data 
source)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Function MergeIt()
         Dim objWord As Word.Document
         Set objWord = GetObject
("C:\mailmerge\1EmployeeTermGroup2.doc", "Word.Document")
         ' Make Word visible.
         objWord.Application.Visible = True
         ' Set the mail merge data source as the group database.
         objWord.MailMerge.OpenDataSource _
            Name:="S:\Database\groups.mdb", _
            LinkToSource:=True, _
            Connection:="QUERY qryMailMerge", _
            SQLStatement:="Select * from [qryMailMerge]"
         ' Execute the mail merge.
         objWord.MailMerge.Execute
      End Function
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

HOWEVER, when the code launches, another Access database opens, an that 
new DB window pops open a Parameter Query window that asks me to input 
the data.  This makes no sense because if I launch the query manually, it 
just returns the information based on the form.  Even wierder is, say I 
put group number "54312" in the Parameter Query question box, the 
template merges perfectly.  However, when I open the original MailMerge 
template, it is now saved with Header/Data information (linked rigth to 
that same query).  What do I need to do so that the criteria in the query 
looks at the form textbox and not a normal parameter query.  How do I 
stop the VBA code from saving the Word Doc to have a Data Source? 

BTW, I know -0- about VBA, I copied this code from other threads that 
have helped tremendously, so please be very descriptive.

Thanks everyone!!!

I have an odd feeling nobody knows :(

> I have an odd feeling nobody knows :(

You might try http://www.helenfeddema.com/CodeSamples.htm. Scroll down
to the Access -> Word section and look at VBA Code Sample #24 - Merging
to Word Four Ways. An Access 97 database is included in the download.

I think the second instance of Access opening for the data source is 
normal behavior when using the MailMerge object. I've only worked with
custom doc properties and bookmarks though (two other methods the article
discusses).

Good luck.

Bob

Wow, that is ALOT of info, thanks =).  I will definately be able to use 
for future use when I learn VBA.  Only problem is, I can tell this is 
very advanced VBA programming, and I know natta about VBA.

The method I described above should work.  I do not mind at the moment a 
second instance of Access opening.  But what has me baffled is why the 
query criteria [forms].[frmMailMerge].[qryMailMerge] is treated as a 
standard parameter question pop box when the second Access opens to get 
the data for the merge, it should just look at the form and use the data 
that is filled in the boxes.

Wait....I think I got it.  The second instance of Access does not have 
the form open, so when the query loads, it has no data from the form to 
get, so it pops open a parameter query question to provide the data.  
Darn!  That has to be it.  So now all I need is a way to make word get 
the data from the current open Access instance, and not open another 
instance.  

Any ideas??? =)

>So now all I need is a way to make word get the data from the current open 
>Access instance, and not open another instance.

>Any ideas??? =)

1) Fill a local table with the merge list data (the current record your
   form is displaying)
2) Export the table data to a text file using DoCmd.TransferText
3) Use the text file as your data source for the merge
   .MailMerge.OpenDataSource Name:=strTextFile

The only way to avoid opening another instance of Access using the
MailMerge object is to use a datasource other than Access. Using a
text file accomplishes that. The Access 97 database I pointed you to
does this in the Click event of cmdMerge on frmMailMerge.


Best,

Bob



>From: "Joel Seguin" <drppepp@p...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: [forms] Parameter query not working right with VBA
>Date: Sun, 29 Dec 2002 20:54:39
>
>Wow, that is ALOT of info, thanks =).  I will definately be able to use
>for future use when I learn VBA.  Only problem is, I can tell this is
>very advanced VBA programming, and I know natta about VBA.
>
>The method I described above should work.  I do not mind at the moment a
>second instance of Access opening.  But what has me baffled is why the
>query criteria [forms].[frmMailMerge].[qryMailMerge] is treated as a
>standard parameter question pop box when the second Access opens to get
>the data for the merge, it should just look at the form and use the data
>that is filled in the boxes.
>
>Wait....I think I got it.  The second instance of Access does not have
>the form open, so when the query loads, it has no data from the form to
>get, so it pops open a parameter query question to provide the data.
>Darn!  That has to be it.  So now all I need is a way to make word get
>the data from the current open Access instance, and not open another
>instance.
>
>Any ideas??? =)


_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

Actually this is an Access quirk - if your application is titled, 
(Tools/Startup/Application Title) then the Mail Merge tools essentially 
thinks that Access isn't running because it's lloking for a window 
handle titled MS Access.  You have to trick Access it by temporarily 
re-Titling your application to "".   You'll want to reset your 
application title in an on-activate when you return.  Your code should 
be similar to this:


Dim mstAppTitle As String

Function fSetAccessCaption() As Boolean
Dim dbs As Database
Const cPropNotExit =3D 3270

    Set dbs =3D CurrentDb
    On Error Resume Next
    mstAppTitle =3D dbs.Properties("AppTitle")  ' Retrieve current title
    'if property doesn't exist
    If Err =3D cPropNotExit Then
        fSetAccessCaption =3D False
    Else
        dbs.Properties("AppTitle") =3D "Microsoft Access"
        RefreshTitleBar
        fSetAccessCaption =3D True
    End If
End Function

' Now you can perform the MailMerge without the additional challenge

'  When you return from the Merge session call this subroutine to reset 
your application title
Sub sRestoreTitle()                         ' Restore application title
    CurrentDb.Properties("AppTitle") =3D mstAppTitle
    RefreshTitleBar
End Sub


 -----Original Message-----
From: 	Bob Bedell [mailto:bobbedell15@m...]
Sent:	Sunday, December 29, 2002 2:50 PM
To:	Access
Subject:	[access] Re: [forms] Parameter query not working right with VBA

>So now all I need is a way to make word get the data from the current 
open
>Access instance, and not open another instance.

>Any ideas??? =3D)

1) Fill a local table with the merge list data (the current record your
   form is displaying)
2) Export the table data to a text file using DoCmd.TransferText
3) Use the text file as your data source for the merge
   .MailMerge.OpenDataSource Name:=3DstrTextFile

The only way to avoid opening another instance of Access using the
MailMerge object is to use a datasource other than Access. Using a
text file accomplishes that. The Access 97 database I pointed you to
does this in the Click event of cmdMerge on frmMailMerge.


Best,

Bob



>From: "Joel Seguin" <drppepp@p...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: [forms] Parameter query not working right with 
VBA
>Date: Sun, 29 Dec 2002 20:54:39
>
>Wow, that is ALOT of info, thanks =3D).  I will definately be able to 
use
>for future use when I learn VBA.  Only problem is, I can tell this is
>very advanced VBA programming, and I know natta about VBA.
>
>The method I described above should work.  I do not mind at the moment 
a
>second instance of Access opening.  But what has me baffled is why the
>query criteria [forms].[frmMailMerge].[qryMailMerge] is treated as a
>standard parameter question pop box when the second Access opens to get
>the data for the merge, it should just look at the form and use the 
data
>that is filled in the boxes.
>
>Wait....I think I got it.  The second instance of Access does not have
>the form open, so when the query loads, it has no data from the form to
>get, so it pops open a parameter query question to provide the data.
>Darn!  That has to be it.  So now all I need is a way to make word get
>the data from the current open Access instance, and not open another
>instance.
>
>Any ideas??? =3D)


_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3D3963