We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Sending data to Word template

Medium Priority
662 Views
Last Modified: 2012-05-06
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.
Comment
Watch Question

Retired
CERTIFIED EXPERT
Top Expert 2012
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Donald MaloneyConsultant

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

Author

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.

Author

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

GrahamSkanRetired
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.
Donald MaloneyConsultant

Commented:
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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.