Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft: Access Reports FAQ

How To

Populate a Word Doc without Merging? by Telsa
Posted: 25 May 01

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

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.

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)

Click event:

    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)
        m_objWord.Visible = True
        InsertTextAtBookmark "basepart", recSubmit("base")
        InsertTextAtBookmark "title", recSubmit("title-version")
        InsertTextAtBookmark "bundledparts", recSubmit("bundledparts")
        InsertTextAtBookmark "ReleaseDate", recSubmit("ReleaseDate")
        InsertTextAtBookmark "version", recSubmit("Version")
    ' Generate the table data
    InsertSummaryTable recSubmit2

    Set m_objDoc = Nothing
    Set m_objWord = Nothing
End Sub

Private Sub InsertTextAtBookmark(strBkmk As String, varText As Variant)
    ' This finds the bookmarks in the Word template to place the data.
    m_objWord.Selection.Text = varText & ""
End Sub

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
    strTable = ""
    While Not recR.EOF
        strTable = strTable & vbTab & recR("discontinuedpart") & vbTab & vbTab & recR("5x5No") & vbCr
    InsertTextAtBookmark "DiscPart", strTable
    Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTab)

    objTable.Columns(1).Width = InchesToPoints(1.51)
    objTable.Columns(2).Width = InchesToPoints(2.56)
    objTable.Columns(3).Width = InchesToPoints(1.44)
    objTable.Columns(4).Width = InchesToPoints(2.14)
    Set objTable = Nothing
    Exit Sub
    End Sub

Back to Microsoft: Access Reports FAQ Index
Back to Microsoft: Access Reports Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close