Linking Word Fields to An Access Database

[Chris waller]

I have created a complex form in Word. There are a number of fields on the
form. The first one contains the National Insurance Number, the second the
name of the person and the third is the sex of the person. All this data is
also held on an Access database. Ideally what I'd like to do, is to input the
National insurance number in the appropriate field on the Word Document and
for each of the subsequent fields to be updated with the correct data from
the Access Database. I would also linke to be able to input the first line of
the address and then for the subsequent 5 lines to be updated again from data
held in a table on Access. I am sure this can be done using form fields or
perhaps some VBA behind the scenes. What I want to do will save many
man-hours work, but as my knowledge of VBA is somewhat limited I don't know
where to start. Any help would be gratly appreciated.

 

[Jonathan West]

I have created a complex form in Word. There are a number of fields on the
form. The first one contains the National Insurance Number, the second the
name of the person and the third is the sex of the person. All this data
is
also held on an Access database. Ideally what I'd like to do, is to input
the
National insurance number in the appropriate field on the Word Document
and
for each of the subsequent fields to be updated with the correct data from
the Access Database. I would also linke to be able to input the first line
of
the address and then for the subsequent 5 lines to be updated again from
data
held in a table on Access. I am sure this can be done using form fields or
perhaps some VBA behind the scenes. What I want to do will save many
man-hours work, but as my knowledge of VBA is somewhat limited I don't
know
where to start. Any help would be gratly appreciated.

Hi chris

There is an artcle on this subject here

Please Fill Out This Form, Part 5
http://www.computorcompanion.com/LPMArticle.asp?ID=136

 

[Pete]

The example provided in that article is good but is specific to one
table. What you want to be able to do is create a solution for any
form. I am in the process of doing so however I ran into a snag.
That is being able to append a text control that contains carriage
returns for new lines. Word displays a little box and does not
provide a new line. Here is my current code undocumented. Hope it
helps:


Two procedures included:

This first one gets the bookmark name from a table which contains the
field name and word bookmark. Then the second pages thru the
recordset and sets the field equal to the data in the table.

Public Function GetBookMark(FieldName As String) As String
'GetBookMark("Detailed_Disposition_W51")
'purpose: to get word bookmark for pasting data to word doc

Dim dbs As Database
Dim strSQL As String
Dim rst As DAO.Recordset


strSQL = "SELECT BookMarks.FieldName, BookMarks.BookMark FROM
BookMarks WHERE (((BookMarks.FieldName)='" & FieldName & "'));"
'strSQL = "SELECT BookMarks.* FROM BookMarks;"
'CreateAndRunActionQuery (strSQL)

Set dbs = CurrentDb 'Point to current database
Set rst = dbs.OpenRecordset(strSQL) 'open recordset

If Not rst.EOF Then
GetBookMark = rst![Bookmark] 'Return record set
End If
Chr (13)

End Function
Public Function writeword1()

Dim strSQL As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim FieldName As String
Dim FieldValue As String
Dim BookMarkName As String
Dim objWord As Word.Application

Set objWord = CreateObject("Word.Application")
objWord.Visible = True 'Show Word.
objWord.Documents.Open ("C:\Documents and
Settings\Julie\Desktop\RCI_Template2.doc") 'Open the document.

strSQL = "SELECT tbl_RCI1.* FROM tbl_RCI1;"
Set dbs = CurrentDb 'Point to xcurrent database
Set rst = dbs.OpenRecordset(strSQL) 'open recordset

For Each fld In rst.Fields
FieldName = fld.Name
If FieldName <> "ID" Then
If Not IsNull(fld.Value) Then
FieldValue = fld.Value
BookMarkName = GetBookMark(FieldName)
objWord.ActiveDocument.Bookmarks(BookMarkName).Select 'Move to
each bookmark and insert text.
' objWord.ActiveDocument.FormFields.Shaded = Not
ActiveDocument.FormFields.Shaded
If BookMarkName Like "*check*" Then
If FieldValue = "true" Then
objWord.ActiveDocument.FormFields(BookMarkName).CheckBox.Default
= True
Else
objWord.ActiveDocument.FormFields(BookMarkName).CheckBox.Default
= False
End If
Else
objWord.ActiveDocument.FormFields(BookMarkName).Result =
FieldValue
'if you find new line char then grab first part of text and
line character then
' set form field
End If
End If
End If
'Call Write2Word(BookMarkName, FieldValue)
Next fld

'Close the document without saving changes.
' objWord.ActiveDocument.FormFields.Shaded = Not
ActiveDocument.FormFields.Shaded
objWord.ActiveDocument.SaveAs FileName:="C:\Documents and
Settings\mj\Desktop\Doc1.doc"
'Quit Microsoft Word and release the object variable.
' objWord.Quit


Set rst = Nothing
Set fld = Nothing

'rst.Close
Set db = Nothing
Set objWord = Nothing

End Function