Merge current Access record to Word

[cjg.groups]

(I'll try again, keeping it simpler)

How can I merge the current record of a form to a Word document? I am
using Access 2003 and Word 2003. This article is my starting point
http://support.microsoft.com/?id=209976 .

Since my form has three subforms from three tables, I created a query
to select only the fields needed by the merge. I created the Word
document, associated it with the database, and added the fields. When
I run the MergeIt function (from the article), it merges all of the
records. How can I merge only the form's current record?

 

[cjg.groups]

Here's what I've tried that has not worked:

I added the following WHERE clause to the saved query:
WHERE ((Orders.OrderID)=[Forms]![OrderForm]![OrderID]);
The query is no longer available as a data source to Word. Running
MergeIt opens the merge template and asks me to select a table/query.
Word does not display any queries which use the above code. Can I use
a form reference in a mailmerge source query?

I removed the WHERE and tried to add it to the OpenDataSource call.
Here is the code:
objWord.MailMerge.OpenDataSource Name:="C:\database.mdb",
LinkToSource:=True, Connection:="QUERY qrySamplingRequestForm",
SQLStatement:="SELECT * FROM 'qrySamplingRequestForm' WHERE
Orders.OrderID=Forms!OrderForm!OrderID"
Running this opens the template and asks me to select a database and a
table. After selecting them, the merge runs for all records. Is my
syntax correct?

 

[cjg.groups]

I found a solution, but I do not trust it. If anyone has corrections
or improvements to any method I've mentioned, your help would be much
appreciated.

I removed the call to OpenDataSource, because the Word template was
"bound" to the Access database and query when I created it. I
added this code instead:
With objWord.MailMerge.DataSource
.FirstRecord = Forms!OrderForm.CurrentRecord
.LastRecord = Forms!OrderForm.CurrentRecord
End With

The problem is, the form uses the master table and the merge is based
off the query. This code requires that the query displays every record
in the table, sorted by the primary key (OrderID). This seems prone to
failure.

My next attempt is to use a "find" method to locate the OrderID of
the form's current record in the merge query. Then, restrict
DataSource to only that record. Still, that seems problematic.

My other options, harvested from the newsgroups, include:
Use bookmarks - http://support.microsoft.com/kb/q210271/ - which
requires redoing the Word template.
Use ODBC or OLEDB - http://support.microsoft.com/kb/285332/en-us -
which seems to have a large learning curve.
Filter the query for only the form's current record's OrderID???
Modify DataSource by looping over it, setting all fields as
..Included=FALSE if their OrderID doesn't match the current one???

Thanks again for any ideas or improvements that you may have.

 

[Guest]

If you change the SQL statement in the function so it restricts the result
set to the current record the merge should only use that one record. The
amended line would be along these lines:

SQLStatement:="SELECT * FROM [Customers] WHERE [CustomerID] = " &
Forms!YourForm!CustomerID

Where YourForm is the name of the form and CustomerID is the numeric primary
key of the underlying table.

You might also like to take a look at my Access to Word demo, which as well
as merging includes filing Word tables, Word form fields and Word bookmarks
from Access:


http://community.netscape.com/n/pfx/forum.aspx?msg=23781.1&nav=messages&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Steve Schapel]

Cjg,

For an alternative approach, it may be of interest to you to look at the
"Super Easy Word Merge" section on this page:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

 

[Marshall Barton]

(I'll try again, keeping it simpler)

How can I merge the current record of a form to a Word document? I am
using Access 2003 and Word 2003. This article is my starting point
http://support.microsoft.com/?id=209976 .

Since my form has three subforms from three tables, I created a query
to select only the fields needed by the merge. I created the Word
document, associated it with the database, and added the fields. When
I run the MergeIt function (from the article), it merges all of the
records. How can I merge only the form's current record?

You might want to look at Super Easy Word Merge at:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

 

[Rocky]

Heres what I tried.

I made the query a "Make Table Querie" and then made the criteria on my
querie to filter on the open customer or order. I then made the merge
document look at the created table.

What I did find though was when the merge document opened. it lost it's
links. I then recorded a macro in Word, relinking the data and then
copied the code into the "On Open" function in the Word document.

That did the trick

Rocky

 

[cjg.groups]

Thank you for all of the replies. I will try them now.

I forgot that I had code for this all along, based on this post by Ken
Snell:
http://groups.google.com/group/micr...read/thread/57be2ef8391e01f9/f818d2cbd685e98b

It builds a recordset of the query by passing it a parameter, by
modifying this line:
qdfQueryDefObject.Parameters(0) = Eval("Forms!MyForm!ControlWithID")

Can I use a recordset as the Word MailMerge.DataSource? Maybe pass it
into MailMerge.OpenDataSource? I ask because a different Word output
will need to modify the recordset before merging it. Thank you!!

 

[cjg.groups]

The above suggestions worked very well. I just had to realize that
Forms!OrderForm!OrderID was an Access call, not a SQL statement, and
needed to be appended to the SQL statement as text.

I will try to create a MAKE TABLE query for my dynamicaly built SQL
which is going into the recordset. According to this and other posts,
this seems to be the easiest way to use a modified recordset as a Word
merge data source.

Thank you for your help. MVPs and newsgroup contributors are the best!!