[David Wedding]

I have a database with a number of option fields that I want to
include in a word merge document. I have no trouble with regular
fields or checkboxes, but I need some advice on transfering option
values to the same type of formated form on the word template as on my
database. Should I be using VBA code to do this?

Thanks,

PC

[Doug Robbins - Word MVP]

What do you mean by "option fields"?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"David Wedding" wrote in message
...
I have a database with a number of option fields that I want to
include in a word merge document. I have no trouble with regular
fields or checkboxes, but I need some advice on transfering option
values to the same type of formated form on the word template as on my
database. Should I be using VBA code to do this?

Thanks,

PC

[PC User]

An option field is a field in a table that gets its value from an
option group on a form. The option group consists of a frame with
checkboxes, radio buttons, toggle buttons, etc. that are choices that
are mutually exclusive. The option value comes from the frame, not
the checkboxes or buttons. If there are 12 checkboxes and one of them
is selected, then the option group's value is a number corresponding
to the checkbox's option value. The checkbox is not independent, but
a part of the group and the selected checkbox gives its value to the
group. Hence there could be 12 possible values transfered to the MS
Word template, but only one of the twelve can be transfered.

This is a control in MS Access and I'm trying to find out if MS Word
has a similar control so that I can merge that option value like the
other field values are done during a word merge process. The
description of an option is given in the MS Access help:

Each control (control: A graphical user interface object, such as a
text box, check box, scroll bar, or command button, that lets users
control the program. You use controls to display data or choices,
perform an action, or make the user interface easier to read.) in an
option group (option group: A frame that can contain check boxes,
toggle buttons, and option buttons on a form, report, or data access
page. You use an option group to present alternatives from which the
user can select a single option.) has a numeric value that you can set
with the OptionValue property. Read/write Long.

The OptionValue property applies only to the check box (check box: A
control that indicates whether an option is selected. A check mark
appears in the box when the option is selected.), option button
(option button: A control, also called a radio button, that is
typically used as part of an option group to present alternatives on a
form, report, or data access page. The user cannot select more than
one option.), and toggle button (toggle button: A control that is used
to provide on/off options on a form or report. It can display either
text or a picture and can be stand-alone or part of an option group.)
controls in an option group.

So far I've seen two methods of processing a word merge: 1) merging
the fields and 2) using bookmark fields. However, my research is far
from done; so any help on this would be appreciated. Thanks so much
for your inquiry.

[Doug Robbins - Word MVP]

OK so as far as the field type in the table definition, the field that is
populated by selecting the option is probably either a Text field or a
Number field and it should not make any difference to the mail merge process
how that field is populated.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"PC User" wrote in message
...

An option field is a field in a table that gets its value from an
option group on a form. The option group consists of a frame with
checkboxes, radio buttons, toggle buttons, etc. that are choices that
are mutually exclusive. The option value comes from the frame, not
the checkboxes or buttons. If there are 12 checkboxes and one of them
is selected, then the option group's value is a number corresponding
to the checkbox's option value. The checkbox is not independent, but
a part of the group and the selected checkbox gives its value to the
group. Hence there could be 12 possible values transfered to the MS
Word template, but only one of the twelve can be transfered.

This is a control in MS Access and I'm trying to find out if MS Word
has a similar control so that I can merge that option value like the
other field values are done during a word merge process. The
description of an option is given in the MS Access help:

Each control (control: A graphical user interface object, such as a
text box, check box, scroll bar, or command button, that lets users
control the program. You use controls to display data or choices,
perform an action, or make the user interface easier to read.) in an
option group (option group: A frame that can contain check boxes,
toggle buttons, and option buttons on a form, report, or data access
page. You use an option group to present alternatives from which the
user can select a single option.) has a numeric value that you can set
with the OptionValue property. Read/write Long.

The OptionValue property applies only to the check box (check box: A
control that indicates whether an option is selected. A check mark
appears in the box when the option is selected.), option button
(option button: A control, also called a radio button, that is
typically used as part of an option group to present alternatives on a
form, report, or data access page. The user cannot select more than
one option.), and toggle button (toggle button: A control that is used
to provide on/off options on a form or report. It can display either
text or a picture and can be stand-alone or part of an option group.)
controls in an option group.

So far I've seen two methods of processing a word merge: 1) merging
the fields and 2) using bookmark fields. However, my research is far
from done; so any help on this would be appreciated. Thanks so much
for your inquiry.

[PC User]

Thank you for your reply. I would be interested in hearing anyone
else's approach to the problem.

~~ PC

[Doug Robbins - Word MVP]

What is the problem?

What do you mean by "the same type of formated form" from you original post?

What is it that you want to appear in the result of the Mail Merge?

If you look at the table behind the Access form, what data appears in what
you call the "Options Fields"?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"PC User" wrote in message
...
Thank you for your reply. I would be interested in hearing anyone
else's approach to the problem.

~~ PC

[PC User]

Hi Doug,

Given an option group on a form in MS Access with one of
the options selected the field on the table that is the control source
has a value. With 12 options, lets say that I chose option 5 that has
a value of 5. Now the option group designed on this form is visually
designed to look like a hardcopy form from a government agency. The
option group appears with 3 rows and 4 columns. This is not an array,
nor is it a table. The checkboxes with their labels are just arranged
in this way as shown on the agency's form. This is the input form on
MS Access so that the user can easily relate on how to read the data
on the agecy form and input it into MS Access. So the form in MS
Access with all other fields now looks exactly like the agency's form.

Now the data from MS Access now needs to go onto an agency
form that is created in an MS Word template. Again to clarify my
point, the agency form designed on the input form in MS Access and the
agency form in MS Word template look exactly like the form from the
government agency. My point being that I am using a option group in
MS Access, but I do not see a way to create an option group in the MS
Word template to pickup the one value of 5 that I selected in MS
Access and merge it into the MS Word template in order to correctly
generate a word merged document. If there was a way to put an
identical field in the MS Word template (i.e. an option group) then
when the value from the MS Access table having merged with the MS Word
template will display in the option group in MS Word.

The problem is that MS Word doesn't have option groups
and the selected value of 5 doen't do anything for the word merge
document. So I'm looking for a method "out of the box" to solve the
problem. Either I have to create a VBA function in MS Word or MS
Access and design the MS Word template to pickup the value from the
function and place it onto the merged document. I'm quite aware that
the option group in MS Access stores only one value in a field. I use
them quite a bit in designing my database; however, word merge is
another creature to me at this point and the fields that I can work
with in a word merge template present some limitations. I'm
considering that VBA to carry the value over to the MS Word template
may be the answer and in my research in a number of online forums, it
doesn't look like anyone has approached the problem before.

Thanks,
PC

[Doug Robbins - Word MVP]

So what you will need in Word is 12 If...then...Else field constructions
each of which checks to see if the value in the merge field and then inserts
a symbol - probably Wingdings: 164 if the value matches the one where you
want the (what I would call) radio button to appear to be selected and
inserts Wingdings: 161 if it doesn't.

{ IF { MERGEFIELD Optionfieldname } = 1 [Wingdings: 164] [Wingdings: 161] }

{ IF { MERGEFIELD Optionfieldname } = 2 [Wingdings: 164] [Wingdings: 161] }

{ IF { MERGEFIELD Optionfieldname } = 3 [Wingdings: 164] [Wingdings: 161] }

{ IF { MERGEFIELD Optionfieldname } = 4 [Wingdings: 164] [Wingdings: 161] }

etc.

{ IF { MERGEFIELD Optionfieldname } = 12 [Wingdings: 164] [Wingdings: 161] }


In all of the above, replace the [Wingdings: 164] and [Wingdings: 161] with
the corresponding symbols

Also, you must use Ctrl+F9 to insert each pair of field delimiters { }

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"PC User" wrote in message
...
Hi Doug,

Given an option group on a form in MS Access with one of
the options selected the field on the table that is the control source
has a value. With 12 options, lets say that I chose option 5 that has
a value of 5. Now the option group designed on this form is visually
designed to look like a hardcopy form from a government agency. The
option group appears with 3 rows and 4 columns. This is not an array,
nor is it a table. The checkboxes with their labels are just arranged
in this way as shown on the agency's form. This is the input form on
MS Access so that the user can easily relate on how to read the data
on the agecy form and input it into MS Access. So the form in MS
Access with all other fields now looks exactly like the agency's form.

Now the data from MS Access now needs to go onto an agency
form that is created in an MS Word template. Again to clarify my
point, the agency form designed on the input form in MS Access and the
agency form in MS Word template look exactly like the form from the
government agency. My point being that I am using a option group in
MS Access, but I do not see a way to create an option group in the MS
Word template to pickup the one value of 5 that I selected in MS
Access and merge it into the MS Word template in order to correctly
generate a word merged document. If there was a way to put an
identical field in the MS Word template (i.e. an option group) then
when the value from the MS Access table having merged with the MS Word
template will display in the option group in MS Word.

The problem is that MS Word doesn't have option groups
and the selected value of 5 doen't do anything for the word merge
document. So I'm looking for a method "out of the box" to solve the
problem. Either I have to create a VBA function in MS Word or MS
Access and design the MS Word template to pickup the value from the
function and place it onto the merged document. I'm quite aware that
the option group in MS Access stores only one value in a field. I use
them quite a bit in designing my database; however, word merge is
another creature to me at this point and the fields that I can work
with in a word merge template present some limitations. I'm
considering that VBA to carry the value over to the MS Word template
may be the answer and in my research in a number of online forums, it
doesn't look like anyone has approached the problem before.

Thanks,
PC

[PC User]

Thanks for your reply. I think I'm getting closer to the solution.
I'm thinking of doing this in VBA so that I can use this in similar
forms for other agencies. Unfortunately these agencies woun't adopt a
uniform form. They all have 90% of the same items, but the rest is
their own ideas. So for these12 items, I'm thinking of making VBA
function driven by an event like "msoOpenDocument" or "ActiveDocument"
or "CustomDocumentProperties" or "DocumentChange". Since I'm more
familiar with MS Access than MS Word, I don't know which would be a
good trigger event to activate the function. Also I'm think that on
the form I'll use fields with no properties other than an unique name
and use VBA to set the properties and formulas into each of the fields
using those unique names (aka ID). So let me focus in on a question
that you may be able to answer.

1. How can I set the properties of each field (i.e. make it a merge
field) and include the necessary formula that would be otherwise
placed inside the field directly on the form, but now all by using
VBA? I know that there must be a way to identify each individual
field by referencing its unique name (i.e. option1, option2, etc.)
Something like: Fields.Add wrdApplication.Selection.Range, "option1"

2. Which event would you recommend to trigger the function to set the
properties?

Thanks,
PC

[Doug Robbins - Word MVP]

I would start with a Word Template in which I would use Document Variable
{ DOCVARIABLE varName } fields where you want the information from the
Access database to appear. Then in your VBA Code, you would set the value
of the variables [varname] to the value from Access using

Dim NewDoc as Document
Set NewDoc = Documents.Add("templatename")
With NewDoc
.Variables("varname").Value = somethingformAccess
'etc
.Range.Fields.Update
End With

If you are creating multiple documents, you would put this inside a
For....Next loop that iterated through the records in the database, creating
a new document for each one.

I am not sure what you mean by thinking that you are getting closer to a
solution. Whichever way, you still have the issue of needing to display
something that looks like a series of radio buttons.

You should also consider whether the whole thing can be done as a report in
Access. In a number of applications, I use Access reports to produce
documents that to look at them are indistinguishable from documents produced
by Word.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"PC User" wrote in message
...
Thanks for your reply. I think I'm getting closer to the solution.
I'm thinking of doing this in VBA so that I can use this in similar
forms for other agencies. Unfortunately these agencies woun't adopt a
uniform form. They all have 90% of the same items, but the rest is
their own ideas. So for these12 items, I'm thinking of making VBA
function driven by an event like "msoOpenDocument" or "ActiveDocument"
or "CustomDocumentProperties" or "DocumentChange". Since I'm more
familiar with MS Access than MS Word, I don't know which would be a
good trigger event to activate the function. Also I'm think that on
the form I'll use fields with no properties other than an unique name
and use VBA to set the properties and formulas into each of the fields
using those unique names (aka ID). So let me focus in on a question
that you may be able to answer.

1. How can I set the properties of each field (i.e. make it a merge
field) and include the necessary formula that would be otherwise
placed inside the field directly on the form, but now all by using
VBA? I know that there must be a way to identify each individual
field by referencing its unique name (i.e. option1, option2, etc.)
Something like: Fields.Add wrdApplication.Selection.Range, "option1"

2. Which event would you recommend to trigger the function to set the
properties?

Thanks,
PC

[PC User]

Yes, I'm quite aware of making these same reports in MS Access and I
have done that. However, my program manager wants these documents
done in MS Word, but because of the massive amounts of data, I have
already started making the reports in MS Access. Now to give him an
archived set of reports in MS Word, I'm programming MS Word. I feel
that handling large amounts of data are always done better on a
database; however, I'm trying to do my project quickly by using a
database and accommodate my manager by putting the reports in MS
Word.

It seems that asking a question is not enough on a forum, but I have
to justify my request. These things are a little personal, but if
this is what is needed to get an answer I guess I have to say it. I'm
sure its always interesting to get the background of some of the
unusual questions that people post. If its ok with you, I'd like to
stay on my posted question.

I'll use your code concept in a MS Word module to loop through all the
fields on the document to set their names and properties triggered by
using the msoOpenDocument event.

Thanks,
PC

[Doug Robbins - Word MVP]

No, it is not always necessary to justify a request, but having created a
number of very complex reports in Access, I do feel justified in suggesting
that to use an Access report is sometimes better than trying to create
something in Word.

Sometimes it is the manager who needs educating.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"PC User" wrote in message
...
Yes, I'm quite aware of making these same reports in MS Access and I
have done that. However, my program manager wants these documents
done in MS Word, but because of the massive amounts of data, I have
already started making the reports in MS Access. Now to give him an
archived set of reports in MS Word, I'm programming MS Word. I feel
that handling large amounts of data are always done better on a
database; however, I'm trying to do my project quickly by using a
database and accommodate my manager by putting the reports in MS
Word.

It seems that asking a question is not enough on a forum, but I have
to justify my request. These things are a little personal, but if
this is what is needed to get an answer I guess I have to say it. I'm
sure its always interesting to get the background of some of the
unusual questions that people post. If its ok with you, I'd like to
stay on my posted question.

I'll use your code concept in a MS Word module to loop through all the
fields on the document to set their names and properties triggered by
using the msoOpenDocument event.

Thanks,
PC