Word as Report Writer...

[Guest_imported]

Using Access 2000

I have developed code (with the help of Access 2000 Developer's Handbook) that allows me to populate a Word template from a query. This is accomplished by linking data in Access to bookmarks in the Word template.

When I am linking single fields, I have no problems (i.e. Name bookmark = Joe), but I have complicated things by pushing multiple records to one bookmark that creates a table.

Here is the code I am using - apologize for the length:
Function CreateTableFromRecordset(rngAny As Word.Range, rstAny As ADODB.Recordset, Optional fIncludeFieldNames As Boolean = False) As Word.Table

Dim objTable As Word.Table
Dim fldAny As ADODB.Field
Dim varData As Variant
Dim strBookmark As String
Dim cField As Long

' Get the data from the recordset
varData = rstAny.GetString()

' Create the table
With rngAny
.InsertAfter varData
Set objTable = .ConvertToTable()

If fIncludeFieldNames Then
With objTable
' Add a new row on top and make it a heading
.Rows.Add(.Rows(1)).HeadingFormat = True
' Iterate through the fields and add their
' names to the heading row
For Each fldAny In rstAny.Fields
cField = cField + 1
.Cell(1, cField).Range.Text = fldAny.Name
Next
End With
End If
End With
Set CreateTableFromRecordset = objTable
End Function

Sub PrintInvoiceWithWord()

Dim objWord As Word.Application
Dim rst As Recordset
Dim strSQL As String

' Launch Word and load the template
Set objWord = New Word.Application
objWord.Documents.Add Application.CurrentProject.Path & "\Template.dot"
objWord.Visible = True

' Add header information using predefined bookmark(s)
With objWord.ActiveDocument.Bookmarks
.Item("Name&quot.Range.Text = frmOrder.Name
’bookmark pulling from Order form
End With

' Build SQL string for details
strSQL = "SELECT [1_tblPrimary].CustID, [1_tblPrimary].Desc, [1_assoc_tblSecondary].Status, " & _
"[1_assoc_tblSecondary].ProdID " & _
"FROM 1_tblPrimary LEFT JOIN 1_assoc_tblSecondary ON [1_tblPrimary].CustID = [1_assoc_tblSecondary].CustID " & _
"WHERE ((([1_tblPrimary].RptList)=True))"

' Get details from database and create a table in the document
Set rst = New Recordset
rst.Open strSQL, CurrentProject.Connection
With CreateTableFromRecordset(objWord.ActiveDocument.Bookmarks("Details&quot.Range, rst, True)

' Apply formatting
'.AutoFormat wdTableFormatProfessional
.AutoFitBehavior wdAutoFitContent

' Fix up paragraph alignment
.Range.ParagraphFormat.Alignment = wdAlignParagraphRight
.Columns(1).Select
objWord.Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft
objWord.Selection.MoveDown
End With

Set objWord = Nothing
End Sub

Sometimes the table creation works perfectly and other times it doesn't. These are the two main issues I have:
1. The ID field (first field) is sometimes separated between the first two columns.
2. Sometimes if I try to push more than 4 fields into the table, it will not display them all.

As a less important issue, I have one field that is numeric (values 1-5) that represent words within Access (i.e. 1=Paid). When I populate the Word template, the number is what is created.

I think this is a relatively complex problem and I sincerely hope that you can help me out with this issue.