Push Data from Access to Word; 'X' in Check Box Form Field

[ryguy7272]

I recently posted this in the Access Programming area, and after not
receiving any feedback, thought it may be more of a Word issue.

Basically, I am trying to push data from a Form to a Word.doc. I had this
working fine for a document with Bookmarks, but when I set it up for another
document,
with a different type of bookmark, a CheckBox, it didn’t work. For
instance, in Word > Forms > Check Box Form Field, I inserted several
CheckBoxes in my Word doc. Now, I am trying to push checks from an Access
form to my Word template. I’ve tried multiple things and can’t seem to get
it working. Is this possible? It seems to be quite easy to double-click a
CheckBox in Word and the ‘X’ goes in there easy enough; not sure if you can
control Word in the same manner, using Access.


The name of the Form is ‘Parient’, all code is below:

Below is my code:
Private Sub Command10_Click()
'Print customer slip for current customer.
Dim appWord As Word.Application
Dim doc As Word.Document
'Avoid error 429, when Word isn't open.
On Error Resume Next
err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set appWord = New Word.Application
End If
Set doc = appWord.Documents.Open(CurrentProject.Path &
"\MontefioreRADON.doc", , True)
With doc
..FormFields("Supine").Result = Me!Supine
..FormFields("Prone").Result = Me!Prone
..FormFields("ArmsUp").Result = Me!ArmsUp
..FormFields("ArmsSides").Result = Me!ArmsSides
..FormFields("ArmsAkimbo").Result = Me!ArmsAkimbo
..FormFields("ReversedTable").Result = Me!ReversedTable
..FormFields("Other").Result = Me!Other


..Visible = True
..Activate
End With
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox err.Number & ": " & err.Description
End Sub

 

[Doug Robbins - Word MVP]

To set a checkbox type formfield in Word, you use

..CheckBox.Value = True 'or False,

or

..CheckBox.Value = -1 ' or 0

Instead of .Result

Therefore, for the appropriate formfields, just replace the .Result in your
code with .CheckBox.Value.

--
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

 

[Peter Jamieson]

For checkboxes you have to do a bit extra -

..FormFields("whatever").Checkbox.Value = True ' or False

i.e. you may need to coerce your Me!Whatever value to be a boolean.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[ryguy7272]

Thanks for the information. This is my first time doing this with the
CheckBoxes…something doesn’t seem to make sense. I have an Access Table with
several Yes/No fields. I have another table with all kinds of patient
information. I joined these two tables using an EquiJoin Line. Now in a
query I have all my Yes/No fields and the name of the patient as well as the
patient ID. This query is the basis for my Form. This pretty much seems
like the way to do this. When I open the Form, which is named ‘Patient’ the
checks show automatically; source is the Table that I described above. The
CheckBoxes are all named appropriately. When I run the code, nothing
happens. Well, Word opens, but not CheckBoxes are checked!!

It seems like I have to do some kind of conditional test, like this:
If .FormFields("Supine").Result = Me!Supine Then
..FormFields("Supine").CheckBox.Value = True
Else
..FormFields("Supine").CheckBox.Value = False

Is that right? It doesn’t work.

I tried this too:
..FormFields("Supine").CheckBox.Value = Me!Supine

Still nothing!! The Word doc must be in ‘Protected’ mode, right.

What does it mean to convert Me!Supine to Boolean? I definitely appreciate
your help as I get over this last hurdle!!

Thanks,
Ryan---

 

[Doug Robbins - Word MVP]

Have you tried

With doc
.FormFields("Supine").CheckBox.Value = Me!Supine.Value
.FormFields("Prone").CheckBox.Value = Me!Prone.Value
.FormFields("ArmsUp").CheckBox.Value = Me!ArmsUp.Value
.FormFields("ArmsSides").CheckBox.Value = Me!ArmsSides.Value
.FormFields("ArmsAkimbo").CheckBox.Value = Me!ArmsAkimbo.Value
.FormFields("ReversedTable").CheckBox.Value = Me!ReversedTable.Value

.FormFields("Other").CheckBox.Value = Me!Other.Value



etc.


But, why don't you use an Access Report?

--
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

 

[ryguy7272]

Thanks for the follow up Doug! This is kind of bizarre; seems like it should
be working but it just isn’t. I have to hit my Command Button on the Form
twice before the Word doc opens. This doesn’t seem right! I’ve done this
before, using just Bookmarks, not CheckBoxes, and it worked fine when the
Command Button was clicked once!! The data flows from Access to Word, just
like it should. I wonder if the Word doc got corrupted somehow; it just
doesn’t update at all. I’m going to fumble around with it some more. The
problem may be on the Access side, not sure. If you have any new thoughts,
please share.

Regards,
Ryan----

 

[Peter Jamieson]

What does it mean to convert Me!Supine to Boolean?

Well in fact I think what you are doing is OK. So I'm not sure what is
wrong.

Word expects a VB boolean value, True or False. Suppose you had not used
a Yes/No value for Supine, but a 1-char value "Y" or "N". Then assigning
that value to the Checkbox.Value probably would not work.

Since Supine is a Yes/No, if you debug.print Me!Supine, you will
probably see that Access prints either -1 (for True/Yes) or 0 (For
False/No), which is strictly speaking not a Boolean Value but in fact
VBA "gets it right" in this case. If you want, you can convert the value
to a Boolean explicitly by doing cbool(Me!Supine)

You don't need to read this bit, but you did ask...

"Coercion" is when a value of one type is automatically converted into a
value of another type. For example, if you
dim a as string
dim b as double
a = "123.45" ' a is a string. We know it contains a number, but the
computer "sees' a string.
b = a ' hold on, isn't b a number?

Some language systems will recognise that the value of a is numeric and
automatically "coerce" the value to be a number. Languages that are more
"strongly typed" will do /some/ coercions (typically between different
types of number, e.g. will coerce an interger to a float), but may
require you to specify a "cast", which is in essence a statement saying
"yes, I know this is an integer type, but I want you to assign it to a
float type") and if you want to assign a "number in a string" to a
float, will probably insist that you call a conversion function.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[ryguy7272]

Thanks for the information Peter!! Much of it made sense, but some of it
didn’t really resonate. Looks like I’ve got some more work to do in the
realm of VBA. I tried this scenario:

Dim Supine As Boolean
‘etc.

..FormFields("Supine").CheckBox.Value = Me!Supine.Value
‘etc.

I fire the macro, the Word doc opens (no check boxes checked, of course),
and then I go to the Immediate Window and type this: debug.Print Me!Supine

Then I get a MessageBox which reads: Compile Error. Variable not yet
created in this context.

Now, I ask, what does that mean? That must be why it is not working.
Somehow I don’t even have any variables and this is who those CheckBoxes
don’t have a big ‘X’ in there!!

Any thoughts on this Peter? Doug?

I guess I’ll get started on creating an Access Report for what I need. I
think that’s going to be much more difficult than what I’m doing now. I
wanted to learn a new trick, that’s why I tried pushing all this stuff to
Word (and it has worked for me very well in the past; albeit not with
CheckBoxes). Also, the guy I’m helping already has the Word template. To
reproduce his template in an Access Report is going to take some work!!

Thanks,
Ryan---

 

[Doug Robbins - Word MVP]

I just modified a Form in an Access database so that it had a command button
Command10 with the following event code

Private Sub Command10_Click()
'Print customer slip for current customer.
Dim appWord As Word.Application
Dim doc As Word.Document
'Avoid error 429, when Word isn't open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set appWord = New Word.Application
End If
Set doc = appWord.Documents.Open("C:\Documents\Formtest.doc", , True)
With doc
.FormFields("ECN").CheckBox.Value = Me!ECN
.FormFields("NICEIC").CheckBox.Value = Me!NICEIC
.FormFields("ECA").CheckBox.Value = Me!ECA
End With
appWord.Activate
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub

which is the equivalent of yours, except that I deleted the .Visible and
..Activate from inside the With doc End With loop, and added appWord
activate outside that loop and when I click on the command button, the Word
document (FormTest) that I had set up with the appropriate CheckBoxes opens
with the relevant boxes checked to reflect their state on the Access form.

--
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

 

[Peter Jamieson]

I hope Doug's reply will help your progress.

Then I get a MessageBox which reads: Compile Error. Variable not yet
created in this context.

OTTOMH, I think this is because "Me" only exists, or only references
your form, when the event code attached to your form is actually run. If
you are tping this command /after/ the code has un, "Me" probably
doesn't reference anything. However, if you put a break in the code and
do the same thing while your code is paused, you may see a different result.

Peter Jamieson

http://tips.pjmsn.me.uk