Collections, Mail Merge and Word FormFields

[Darryl Kerkeslager]

I hope that although this is 25% Access and 75% Word, that someone will know
...

The whole problem here arises because 1) Microsoft acknowledges an 'issue'
wherein TextInput type FormFields are lost when doing a MailMerge and 2) the
fix that they have proposed is both inadequate and won't work from Access.
http://support.microsoft.com/?kbid=286841

The part of the code below that actually performs the merge is great; the
core of it is Albert D. Kalall's.

What I have tried to do is
1. put the Text FormFields into a collection
2. substitute "<ff>" for the formfield
2. merge the documents
3. substitute the formfield for "<ff>" from the collection

On 3, Access declares a data type mismatch.

Any help?

=================================
Const wdAllowOnlyFormFields = 2
Const wdNoProtection = -1
Const wdFieldFormTextInput = 70
Const wdFindContinue = 1

Public Function MergeQuery(queryName As String, _
docName As String) As Boolean
On Error GoTo handle_error
MergeQuery = False

Dim i As Integer
Dim mWord As Object
Dim mDoc As Object
Dim fField As Object
Dim ffObjects As Collection
Set ffObjects = New Collection
Set mWord = CreateObject("Word.Application")

DoCmd.TransferText acExportDelim, , queryName, "c:\merge.txt", True
With mWord
Set mDoc = .Documents.Open(docName)
.ActiveDocument.MailMerge.MainDocumentType = 0
If .ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
.ActiveDocument.Unprotect
End If

' Put each FormField into the ffObjects collection
For Each fField In .ActiveDocument.FormFields
If fField.Type = wdFieldFormTextInput Then
ffObjects.Add fField
fField.Select
.Application.Selection.TypeText "<ff>"
End If
Next fField

.ActiveDocument.MailMerge.OpenDataSource Name:="c:\merge.txt", _
AddToRecentFiles:=False,
Format:=0, _
Connection:="", SQLStatement:="",
SQLStatement1:=""
.ActiveDocument.MailMerge.Execute

' put form fields into new document
.Application.Selection.Find.ClearFormatting
i = 1
Do While .Application.Selection.Find.Execute(FindText:="<ff >", _
Wrap:=wdFindContinue, Forward:=True) = True
.ActiveDocument.FormFields.Add ffObjects(i)
i = i + 1
Loop

If .ActiveDocument.ProtectionType = wdNoProtection Then
.ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True,
Password:=""
End If
.Application.Visible = True

mDoc.Close
End With
MergeQuery = True
exit_point:
Set mWord = Nothing
Set mDoc = Nothing
Exit Function
handle_error:
LogError Err.Number, "MergeQuery", Err.Description
GoTo exit_point
End Function
===================================

Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927

[rkc]

Darryl Kerkeslager wrote:

I hope that although this is 25% Access and 75% Word, that someone will know
..

The whole problem here arises because 1) Microsoft acknowledges an 'issue'
wherein TextInput type FormFields are lost when doing a MailMerge and 2) the
fix that they have proposed is both inadequate and won't work from Access.
http://support.microsoft.com/?kbid=286841

The part of the code below that actually performs the merge is great; the
core of it is Albert D. Kalall's.

What I have tried to do is
1. put the Text FormFields into a collection
2. substitute "<ff>" for the formfield
2. merge the documents
3. substitute the formfield for "<ff>" from the collection

On 3, Access declares a data type mismatch.

Any help?

=================================
Const wdAllowOnlyFormFields = 2
Const wdNoProtection = -1
Const wdFieldFormTextInput = 70
Const wdFindContinue = 1

Public Function MergeQuery(queryName As String, _
docName As String) As Boolean
On Error GoTo handle_error
MergeQuery = False

Dim i As Integer
Dim mWord As Object
Dim mDoc As Object
Dim fField As Object
Dim ffObjects As Collection
Set ffObjects = New Collection
Set mWord = CreateObject("Word.Application")

DoCmd.TransferText acExportDelim, , queryName, "c:\merge.txt", True
With mWord
Set mDoc = .Documents.Open(docName)
.ActiveDocument.MailMerge.MainDocumentType = 0
If .ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
.ActiveDocument.Unprotect
End If

' Put each FormField into the ffObjects collection
For Each fField In .ActiveDocument.FormFields
If fField.Type = wdFieldFormTextInput Then
ffObjects.Add fField
fField.Select
.Application.Selection.TypeText "<ff>"
End If
Next fField

.ActiveDocument.MailMerge.OpenDataSource Name:="c:\merge.txt", _
AddToRecentFiles:=False,
Format:=0, _
Connection:="", SQLStatement:="",
SQLStatement1:=""
.ActiveDocument.MailMerge.Execute

' put form fields into new document
.Application.Selection.Find.ClearFormatting
i = 1
Do While .Application.Selection.Find.Execute(FindText:="<ff >", _
Wrap:=wdFindContinue, Forward:=True) = True
.ActiveDocument.FormFields.Add ffObjects(i)
i = i + 1
Loop

If .ActiveDocument.ProtectionType = wdNoProtection Then
.ActiveDocument.Protect Type:=wdAllowOnlyFormFields, _
NoReset:=True,
Password:=""
End If
.Application.Visible = True

mDoc.Close
End With
MergeQuery = True
exit_point:
Set mWord = Nothing
Set mDoc = Nothing
Exit Function
handle_error:
LogError Err.Number, "MergeQuery", Err.Description
GoTo exit_point
End Function

Try converting your collection objects to a string (or whatever is
expected) before adding them to the document -> cstr(ffObjects(i)).

[Darryl Kerkeslager]

"rkc" <rk*@rochester.yabba.dabba.do.rr.bomb> wrote

Try converting your collection objects to a string (or whatever is
expected) before adding them to the document -> cstr(ffObjects(i)).

Thank you, that worked ... well, it did what I asked. Unfortunately, the
entire code did not do what I had hoped.

But at least now I know.

Thanks again.
Darryl Kerkeslager
Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927

[Albert D. Kallal]

You know, a good solution here would be to:

Have the word merge code take ALL fields of the forms reocrdset, and output
that.

In addition, have the code ALSO EXPORT any text box on the form that is NOT
bound. This would thus allow you to build "prompt" forms, or even a form
that is not bond to a table, and still use it to merge to word. And, this
approach would also allow a "mix" of bound, and un-bound text boxes to be
used here!!

There is two locations in my example code that outputs the text file (one is
JUST to make the template editor work correctly, and output ONE record.
In this routine (MakeMergeText)

, we have:

If frmF.RecordsetClone.Fields.Count > 0 Then
For Each OneField In frmF.RecordsetClone.Fields

If strFields <> "" Then strFields = strFields & ","
strFields = strFields & qu(OneField.Name)

If strData <> "" Then strData = strData & ","
strData = strData & qu(frmF(OneField.Name))

Next OneField
End If

The above outputs ONE line with the field names for word, and one line of
data so you can edit the template and see the fields.

Now, lets just add the following code right AFTER the above to include
any un-bound text box.

Dim vField As Control

For Each vField In frmF.Controls
If vField.ControlType = acTextBox Then
' if it is a text box, then include in field list

' ONLY include if the contorl souce is blank
If vField.ControlSource = "" Then
If strFields <> "" Then strFields = strFields & ","
strFields = strFields & qu(vField.Name)
' now add data from this box

If strData <> "" Then strData = strData & ","
strData = strData & qu(frmF(vField.Name))

End If
End If
Next vField

You are done!!

The above thus now allows you to use any un-bound text box on the form. And,
note that all fields of the data file are still included, and EVEN included
in the merge if they are NOT placed on the screen. So, now...just add the
above 2nd set of example code, and when you hit the merge button, all
un-bound text boxes will be included....

I had a few people ask me for this feature, and I usually just told them
that it is not needed (I mean, just let the users edit word!). However, as I
read your post, the above idea and code just popped into my head as a good
solution here...

(in fact, I am going to add it to the download version..(I just did this!).
So, you can now actually add blank un-bound text boxes, and they will be
included in the word merge. Do note that this ONLY works for the single
merge..and not the merge all word (that does not make sense, since the
mergeall word is desinged to merge a query...and not have a form open
anyway.

You can grab a working copy with the above code that now includes un-bound
text boxes here:

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

And, if you were smart, you kept your code separate from my code library. To
include the new features in *existing* applictiaons, you just delete the
form GuiWordTempalate. You then delete the modules WordCode, and then
re-import these two items from my download.

Also, note that the new addtion was only added to the a2000 download...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Darryl Kerkeslager]

"Albert D. Kallal" <ka****@msn.com> wrote

Have the word merge code take ALL fields of the forms reocrdset, and
output
that.

In addition, have the code ALSO EXPORT any text box on the form that is
NOT
bound. This would thus allow you to build "prompt" forms, or even a form
that is not bond to a table, and still use it to merge to word. And, this
approach would also allow a "mix" of bound, and un-bound text boxes to be
used here!! [snip] The above thus now allows you to use any un-bound text box on the form.
And,
note that all fields of the data file are still included, and EVEN
included
in the merge if they are NOT placed on the screen. So, now...just add the
above 2nd set of example code, and when you hit the merge button, all
un-bound text boxes will be included....

Albert,

That's really a neat solution (which I wouldn't have thought would work).

Unlike the full and more dynamic solution on your site, my users won't be
able to create their own documents and build their own merge queries, but I
don't think I want them to. Now I see that I could easily add in empty
fields. I use a stored query to pull data from a Document Form, tables, and
some built-in functions. Here is a big ugly one:

SELECT [off_first_name] & " " & [off_last_name] & (" "+[off_suffix]) AS
Name, off_cur_location, off_address1, off_address2,
Eval("[Forms]![DocForm]![cboAgency].[Column](1)") AS Agency, GetUserName()
AS PO, GetPOTitle() AS Title,
Eval("[Forms]![DocForm]![cboAgency].[Column](2)") AS AAddress1,
Eval("[Forms]![DocForm]![cboAgency].[Column](3)") AS AAddress2,
Eval("[Forms]![DocForm]![cboAgency].[Column](4)") AS ACSZ,
[Forms]![DocForm]![cboCourt] & " Court" AS Court,
Format([Forms]![DocForm]![txtApptDate],"dddd mmmm d"", ""yyyy") AS ApptDate,
[Forms]![DocForm]![txtApptTime] & "" AS ApptTime, off_last_name,
IIf([off_sex]="M","Mr.","Ms.") AS Salutation, [Forms]![DocForm]![txtDockets]
& "" AS Docket, Eval("[Forms]![DocForm]![cboJudge].[Column](2)") AS
JudgeName, IIf([off_sex]="M","his","her") AS HisHer FROM offender;

I then just pass in the name of the query and the name of the Word document
to the below function; no other code is involved. This is similar to my
originally posted function, but in that one I was vainly trying to duplicate
the existing empty Word fields.
================================

Public Function MergeQuery(queryName As String, _
docName As String) As Boolean
On Error GoTo handle_error
MergeQuery = False
Dim mWord As Object
Dim mDoc As Object
Set mWord = CreateObject("Word.Application")
DoCmd.TransferText acExportDelim, , queryName, "c:\merge.txt", True
With mWord
Set mDoc = .Documents.Open(docName)
.ActiveDocument.MailMerge.MainDocumentType = 0
.ActiveDocument.MailMerge.OpenDataSource Name:="c:\merge.txt", _
AddToRecentFiles:=False, Format:=0, _
Connection:="", SQLStatement:="",
SQLStatement1:=""
.ActiveDocument.MailMerge.Execute
.Application.Visible = True
mDoc.Close
End With
MergeQuery = True
exit_point:
Set mWord = Nothing
Set mDoc = Nothing
Exit Function
handle_error:
LogError Err.Number, Err.Description
GoTo exit_point
End Function
====================================

Previously, the best that I could come up with was to put <ff> marking in
the Word documents (almost all static forms, not letters) where I wanted an
empty field, and add this code to the above, which just replaces <ff> with a
blank field.

..Application.Selection.Find.ClearFormatting
Do While .Application.Selection.Find.Execute _
(FindText:="<ff>", Wrap:=wdFindContinue, Forward:=True) =
True
.ActiveDocument.FormFields.Add _
Range:=.Application.Selection.Range, Type:=wdFieldFormTextInput
Loop

My main problem is that so far, I can only add a field - I cannot then add
the formatting that the original designers of the Word Document intended
(underline, maximum width 2, uppercase, etc).
--
Darryl Kerkeslager
Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927

[Albert D. Kallal]

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in message
news:h-********************@comcast.com...

That's really a neat solution (which I wouldn't have thought would work).

Unlike the full and more dynamic solution on your site, my users won't be
able to create their own documents and build their own merge queries, but
I don't think I want them to.
I can agree 100% with the above. So, what I would do is generate the text
file (but use code in place of transfertext).

This would allow you to setup, and use standard merge fields.
Previously, the best that I could come up with was to put <ff> marking in
the Word documents (almost all static forms, not letters) where I wanted
an empty field, and add this code to the above, which just replaces <ff>
with a blank field.
As I mentioned, output the data + extra fields to that merge file...and then
you done...
My main problem is that so far, I can only add a field - I cannot then add
the formatting that the original designers of the Word Document intended
(underline, maximum width 2, uppercase, etc).

And, if you send make the merge fields part of the output data, then the
merge fields are already set in the template, then you can format them as
you wish.

I would lift the code in my wordmerge that sends out the text file (but does
not use transfer text). I would then modify that output code to add in the
additional non bound fields from the form...

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Darryl Kerkeslager]

"Albert D. Kallal" <ka****@msn.com> wrote

"Darryl Kerkeslager" <ke*********@comcast.net> wrote

My main problem is that so far, I can only add a field - I cannot then
add the formatting that the original designers of the Word Document
intended (underline, maximum width 2, uppercase, etc).

I'm afraid I'm not explaining myself well, and the main difficulty comes in
with the distinction between a Word merge field and a Word Form field.

Dealing with the Word *merge* fields is no longer an issue. I've
successfully used your method to manage the merging of data. However, the
Word fill-in forms that I have been given have blank Word *form* fields -
textInput, checkbox, and combobox form fields. Some of these, I want to
merge data into, some of these, I don't, but want them to be left blank for
the user to fill in manually *after the merge*. Just like in this MS
article, http://support.microsoft.com/?kbid=286841, although the checkboxes
and comboboxes remain after the merge, any unfilled textInput boxes are not
present in the newly created document. The user cannot then tab between the
unfilled Word form fields and fill them in.


Darryl Kerkeslager
Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.

Demo: www.adcritic.com/interactive/view.php?id=5927

[Albert D. Kallal]

Ah...thanks for taking the time to clarify this issue!

I see the problem now!

One possible solution would be to grab the values before the "merge.execute"
occurs..and then put them back in...but I don't know how to do that!
(yet!!).
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal

[Darryl Kerkeslager]

"Albert D. Kallal" <ka****@msn.com> wrote

Ah...thanks for taking the time to clarify this issue!

I see the problem now!

One possible solution would be to grab the values before the
"merge.execute" occurs..and then put them back in...but I don't know how
to do that! (yet!!).

Well, I tried to capture the form fields as objects and place them into a
collection. After the merge, I put them into the new document. It did
'work' - but I lost all of the formatting for the field, so I'm not sure
whether the form field object contains its own formatting (I now suspect
that it does not).

Darryl Kerkeslager

Power corrupts.
Absolute power corrupts absolutely.
Knowledge is power.
See www.adcritic.com/interactive/view.php?id=5927