You will need to add the Word Object via Tools-References. I found the info to develop this in Access 2000 Beginning VBA by Wrox. Between that and the Word 2000 VBA book by Wrox, I have successfully made an easy to update Word template for Access.
Here it is!
Generating Word Doc from Access without using Merge WORD TEMPLATE Create your Word document with a template format. Save it as a template file (.dot). Use bookmarks to mark the place you want the data to be pulled in. You can have as many bookmarks as you want. If you require the data to be pulled into tables, don't create tables in Word, but let Access VBA create the tables for you.
ACCESS DATABASE Set up queries showing the fields you want to transfer to the Word document. You may need to set up more than one query. If you have to do that, then you will need to set up each as a recordset in the code with its own SQL string. The idea of the SQL string is to narrow down the records in the recordset to the exact info you need.
Assuming only two queries were made, code as follows:
In a module, key in a Public variable to be shared in database
Option Compare Database Option Explicit
' location of the documents and templates - ' Where will Access find the Word Template? Public Const m_strDIR As String = "d:\database\" Public Const m_strTEMPLATE As String = "submittalcd.dot"
' set up objects for use and Public variables to be shared in database Private m_objWord As Word.Application Private m_objDoc As Word.Document Public strProdNum As String
In the Forms Button for starting the eventà
Create SQL statements based on the values of the active record (i.e., prodnum)
Dim db As DAO.Database Dim recSubmittal As DAO.Recordset Dim recSubmittal2 As DAO.Recordset Dim strSQL As String Dim strSQL2 As String
' Capture the field whose value will narrow your recordset down strProdNum = Me.PartsID
strSQL = "SELECT * FROM qrySubmittalBase WHERE ProdNum= '" & strProdnum & "';" Set db = CurrentDb() Set recSubmittal = db.OpenRecordset(strSQL)
StrSQL2 = "SELECT * FROM qrySubmittalDetail WHERE ProdNum= '" & strProdnum & "';" Set db = CurrentDb() Set recSubmittal2 = db.OpenRecordset(strSQL2)
' This CreateSubmittal sub is created in the module CreateSubmittal recSubmittal, recSubmittal2
Back in the module, create the above sub (remember, this is referenced in the Forms click procedure) This can be a little confusing hereà the recSubmit is capturing the recSubmittal and the recSubmit2 is capturing the recSubmittal2 recordsets.
Public Sub CreateSubmittal(recSubmit As DAO.Recordset, recSubmit2 As DAO.Recordset)
Set m_objWord = New Word.Application Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)
' Generate the table data InsertSummaryTable recSubmit2
Set m_objDoc = Nothing Set m_objWord = Nothing
Private Sub InsertTextAtBookmark(strBkmk As String, varText As Variant) ' This finds the bookmarks in the Word template to place the data. m_objDoc.Bookmarks(strBkmk).Select m_objWord.Selection.Text = varText & ""
Private Sub InsertSummaryTable(recR As DAO.Recordset) ' This pulls in the data for a table then highlights the data ' and creates a table in the Word document at a bookmark location ' for each field you want in the column of the table, have tabs ' surround it. Items in quotes are field names from the query/recordset ' If you need to have a blank column, just place vbTab in twice On Error GoTo No_Record_Err Dim strTable As String Dim objTable As Word.Table