473,226 Members | 1,701 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,226 software developers and data experts.

Collections, Mail Merge and Word FormFields

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
Nov 13 '05 #1
8 5126
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)).

Nov 13 '05 #2
"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
Nov 13 '05 #3
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


Nov 13 '05 #4
"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
Nov 13 '05 #5
"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
Nov 13 '05 #6
"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
Nov 13 '05 #7
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
Nov 13 '05 #8
"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
Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: William Wisnieski | last post by:
Hi Everyone, Access 2000 I have some code behind a button that performs a word merge with a query data source. The merge works fine. But what I'd like to do somehow is after the merge is...
8
by: Squirrel | last post by:
Hi everyone, I've created a mail merge Word doc. (using Office XP) , the data source is an Access query. Functionality I'm attempting to set up is: User sets a boolean field to true for...
3
by: Andy Davis | last post by:
I have set up a mail merge document in Word 2003 which gets its data from my Access 2000 database. I want to set up a button on a form that: 1. runs the query to provide the dat for the merge...
4
by: lesperancer | last post by:
I have 3 tables (office97) tblQuote quoteNbr tblDetails ( quote : 1 <-> M: quoteDetails) quoteNbr detailLine product value
2
by: roz | last post by:
Hello, I'm fairly new to VB programming and I've been going round the bend with this problem, so any nudges or shoves in the right direction would be greatly appreciated. I have a template...
6
by: crealesmith | last post by:
Firstly, I have no problem with mail merging to Word, VB code for that works perfectly. On one mail merge I need to merge 15 fields of data that are from 3 seperate records. The 3 records are all...
1
by: mr k | last post by:
Hi, I wanted to use mail merge with forms but Text form fields are not retained during mail merge in Word, I got the code from Microsoft but it doesn't remember the text form field options such as...
17
by: wparrott | last post by:
Hello experts! I'm having trouble coding a button on a form and could use some help. In a nutshell, I'm attempting to code the button to do the following: 1. Choose the appropriate Word...
10
by: mlevit | last post by:
Hi, I've found a tutorial that shows you how to fill in Word documents with values from Access 'Print customer slip for current customer. Dim appWord As Word.Application Dim doc As...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.