Populating Word template form fields from Access

[David Ball]

Hi,

I am building a database that will be used to assign multiple Test Forms to items of Equipment.
There is an Equipment table, a Test Forms table, and an Intermediate table that contains the Primary Keys from both the other tables.
I have a Form that consists of a Main Form that displays the Equipment details and a Subform with a Combo Box where Test Forms can be assigned to each item of Equipment. Each piece of Equipment can have multiple Test Forms assigned.
I would like to take this database to the next level where I could have the actual Test Forms (Word templates) attached to their matching document number (Test Forms) in the database and then have the details from the Equipment table populate the title panel of the test form with Equipment Number, Description, etc.
I would then have, for each item of Equipment, a list of all the Test Forms assigned to it and be able to view and print the test Forms. The test forms would all have the details of the relevant Equipment item in their title panels.
Can this be done in access and what would be the first steps?
I don’t need this database to be completed urgently. It is a project that I will work on, as time allows, over a period of time.
Thanks very much.
Dave B

 

[Rank Am]

Yes it can be done, but be warned formatting data and assigning styles in code is a pain in Word. If you have large amounts of data, set the templates up in Excel it is much, much easier to work with and the copy from recordset method to an XLrange object executes much quicker than Do While Loops through a recordset for large amounts of data
I only use word for invoices, or document frontsheets from the db, I use excel for equipment data sheets, procedures, timesheets etc. It is much less complex to set up and work with in code than Word.
Anyhow here's some basic code, for creating some word objects from Access, you will need to add a reference to the word library in the Visual Basic Editior prior to starting

Private Function SendToWord() As Boolean
Dim rs As Recordset
    'use ADO/DAO to get the data from the DB to a recordset
    'you can also use an array but recordsets are easier to work with
Dim objWRD As Word.Application
Dim objDOC As Word.Document
Dim intI As Integer
    
    'Some code here to populate the recordset from the db
    
    'Create a new new instance of word in memory and make it visible
Set objWRD = CreateObject("word.application")
objWRD.Visible = True
    'Open a new document based upon the template, you will also need to specify the path if different
    'to the database
Set objDOC = objWRD.Documents.Add("C:\path\TEMPLATE_NAME_HERE.dotm")
    'Or you can open an existing document as below
Set objDOC = objWRD.Documents.Open("C:\Foldername\Filename.docx")
rs.MoveFirst
intI = 1
Do While rs.EOF = False
    'The easiest way to get data into word is through tables
    'The tables collection starts at 1 you cant name tbls in the collection
    'Loop through the recordset and set the text values in the word table
objDOC.Tables(1).Cell(intI, 1).Range.Text = rs(0)
intI = intI + 1
rs.MoveNext
    'If you have variable number of rows you can use the following to add rows to
    'the word table but be warned formatting them and setting styles etc is a pain in code
objDOC.Tables(1).Rows.Add
Loop
    'You can also set a bookmark in the word doc and point the rs at it
    'Useful for titles and headers and the like
objDOC.Bookmarks("MYBOOKMARK").Range.Text = rs(0)
    'Save as new file
objDOC.SaveAs ("C:\path\MYFILE")
    'Existing filename
objDOC.Save
    
End Function