Creating a Table in Word from Access module

[Tom Ross]

Hi
I use the code below to create and populate a table from a adodb recordset.
This is used to produce a printed purchase order.

It works fine except when there is a carriage return, or line feed within
any of the fields in of the recordset. The text after the linefeed comes in
the NEXT Row of the table setting everything else out of sync. It should
come as a second line within that cell. I have tried inserting chr(10),
chr(13), 'vbnewline' in the text and each one of the goes to the next row.

When I add a row to the table manually (with .rows.add ) I can insert text
with embedded returns and the cell in the table expands for mutiline
content.

I hope there is a simple solution of something I can embed in the line. that
will not confuse the createtablefromrecordset function.

OR

Should I build and populate the table manually? In which case I need a
little guidance on how to create the table. I think I can populate it once
it is created. My data has four fields Number, QTY, Description, Price.
Once created, I assume I could add rows, populate cells and set properties.


I suspect the manual approach will be best for me in the long run. I hope
there is some simple code to create the table.

Thanks

Tom
*****************************
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

varData = RstAny.GetString()
With RngAny
.InsertAfter varData
Set objTable = .ConvertToTable()
End With
Set CreateTableFromRecordset = objTable
End Function

 

[tony_1955]

I had a smiliar problem with tables from Excel to Word (a linebrea
within a cell forces Word to make a new line in its table whatever
do); if you find a way how to overcome it, I would be interested i
learning about it.

As far as I can say, exporting your Access table to Excel instead o
Word might be kind of a solution. Another opiton, which sounds like th
best way to me, would be to print your purchase order directly as a
Access report. Not what you exactly want, but perhaps wort
considering?

Ton

 

[Tom Ross]

Tony

Thanks for your advice, but I figured out a way to solve that problem. I
was previously using the following approach to build the table from the sql
recordset (rstany)

************
varData = RstAny.GetString()
With RngAny
.InsertAfter varData
Set objTable = .ConvertToTable()
End With
*************

I found that If I build the table manually and then loop through the
recordset and insert into each cell manually. I get great results. Line
feeds, dashes and tabs no longer go to the next row. The cell accepts what
I put in it. I don't know how you would handle the recordset in excel but
the word actions should be the same for you.

################
Function CreateTableForRecordset(RngAny As Word.Range) As Word.Table

Dim objtable As Word.Table
Dim strSQL As String
Dim db As Database
Dim rst As dao.Recordset
Dim RecCount, intCount, j As Integer

'create recordset
strSQL = " SELECT ItemTable.number, ItemTable.quantity,
itemtable.description, itemtable.price FROM ItemTable Where ProjectID ='" &
strProject & "' and ponum = '" & strPONum & "'"
Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
RecCount = rst.RecordCount

'create the table in word RngAny refers to my bookmark in the Word Template
With RngAny
Set objtable = .Tables.Add(RngAny, 1, 4)
End With

'Load the header cells
With objtable
.Borders.Enable = True
.Cell(1, 1).Range.Text = "Item"
.Cell(1, 2).Range.Text = "Qty"
.Cell(1, 3).Range.Text = "Description:"
.Cell(1, 4).Range.Text = "Price/Ea"

'Load the rest of the cells
rst.movefirst
For j = 2 To RecCount + 1
.Rows.Add
.Cell(j, 1).Range.Text = rst!Number
.Cell(j, 2).Range.Text = rst!quantity
.Cell(j, 3).Range.Text = rst!Description
.Cell(j, 4).Range.Text = rst!price
rst.MoveNext
Next j

Set CreateTableForRecordset = objtable
rst.Close

End Function
###############################################