Sending data to Word template

Experts,

Here is my scenario:

I have an Access database. With the push of a button, I would like to be able to open a new Word document based on an existing Word template and auto-populate it with data from my database. After the document is filled out, the data needs to save back to the database.

I know this can be easily done with an Access form, but I was informed that I would have to utilize Word templates. Any help is appreciated.
MarkRodAsked:
Who is Participating?
 
GrahamSkanConnect With a Mentor RetiredCommented:
Sounds a bit pointless, but not too difficult if the templates a for Word forms documents.
Sub ToAndFromDoc()
    Dim wdApp As Word.Application 'early binding, so needs to have a reference to the Word object library.
    Dim wdDoc As Word.Document
    Dim rs1 As New ADODB.Recordset
    Dim rs2 As New ADODB.Recordset
    
    Set wdApp = CreateObject("Word.Application")
    wdApp.Visible = True
    Set wdDoc = wdApp.Documents.Add("C:\MyTemplates\Mytemplate.dot")
    rs1.Open "MyTable1", CurrentProject.Connection
        wdDoc.FormFields("Text1").Result = rs1.Fields("Field1").Value
        '... other fields
    rs1.Close
    rs2.Open "MyTable2", CurrentProject.Connection
        rs2.AddNew
        rs2.Fields("Field1").Value = wdDoc.FormFields("Text1").Result
        '... other fields
        rs2.Update
    rs2.Close
    wdDoc.Close wdDoNotSaveChanges
    wdApp.Quit
End Sub

Open in new window

0
 
Donald MaloneyConsultantCommented:
Following up on what Graham wrote.
Mark,

Just a Q.
Why do you "have to utilize Word templates?"  Those highher up need to use them?

Since you are in the Access DB.  open a form, populate the info, collect the new info and generate a report or export a rtf. (which can be read in word)

Plus if you need to go back in time just set a date/time index on your records.

The ONLY time I use Word or Excel from Access is to IMPORT data   then I append to my tables and am off and running.

Don
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
MarkRodAuthor Commented:
To answer your question, I believe I have to use the Word templates so I do not have to re-create the form in Access.
0
 
MarkRodAuthor Commented:
0
 
MarkRodAuthor Commented:
Graham,

How do I know/set the field names in the template that will be used for this line of code?
wdDoc.FormFields("Text1").Result = rs1.Fields("Field1").Value

Open in new window

0
 
GrahamSkanRetiredCommented:
If you are, in fact, using a forms document, you can find the form field names unprotecting the document, right-clicking on the field, choosing Properties, and noting the bookmark name.

Obviously you will have to match individual table fields with document fields with your own knowledge of the document and the data.

Note. Show the Forms toolbar to help with work on forms documents.
0
 
Donald MaloneyConsultantCommented:
sorry try this one:  (I cut off the L at the end)

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21200618.html

Don
0
 
Donald MaloneyConsultantCommented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.