[forms] Parameter query not working right with VBA

[ordendelfai]

Ok, it looks like ultra long posts don't get replies, so 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&quot
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the Delphi database.
objWord.MailMerge.OpenDataSource _
Name:="S:\Database\groups.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryMailMerge", _
SQLStatement:="Select * from [qryTEMPORARY1groupcleanup]"
' 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!!!

 

[ordendelfai]

Anyone, pretty please ;o)

 

[ordendelfai]

Below is a reply I received from a different forums site, it is very very useful info that this community might be interested in having.

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

 

[ordendelfai]

And here is my response to him, if anyone has an answer I would be very very appreciative

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??? =)