CreateTableFromRecordset

  • Thread starter Thread starter Dee
  • Start date Start date
D

Dee

It was recomended to me to use the CreateTableFromRecordset in order to get
the line items on the subreport when they export to Word in a table instead
of the tab format in which they get exported right now. I saw some code for
this by searching the groups in google but am unsure where to put the code.
Where do I insert this code.... how do I use it? Here is the code that I
found:

Function CreateTableFromRecordset( _
rngAny As Word.Range, _
rstAny As ADODB.Recordset, _
Optional fIncludeFieldNames As Boolean = False) _
As Word.Table


' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert. (Sybex)
' Copyright 1999. All Rights Reserved.


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

' Creating the basic table is easy,
' just insert the tab-delimted text
' add convert it to a table
..InsertAfter varData
Set objTable = .ConvertToTable()

' Field names are more work since
' you must do them one at a time
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

Where do I insert this code? The fields that I will be using are: [Line
Item], [Quantity], [ItemCode], [Description], (below description on a
separate row would be [ItemComment]), [DUP], [Extension], and on the last
row would be =Sum([Extension].

Example:
Item Qty Product # Description
Unit Price Extension
1 37 965-225420-100 IR-522 Infrared Combustible
Hydrocarbon $971.25 $35,936.25
Sensor Assembly
with Splashguard


Net Total: $35,936.25
Can anyone help this beginner....

Thanks,

Dee
 
Copy all of the CreateTableFromRecordset code into a Public Module. If you
do not have any Modules in your database, create a new one and when it
opens, paste the CreateTableFromRecordset function code into it. Save the
module with the default Module1 name.

To call the function, put your code in the Click event of a command button
on a form. There are probably other ways to call it, but that scenario
seems most likely. Here is some sample code that I have used in the past:

Dim objWord As Object
Dim strSQL As String
Dim rsADO As New ADODB.Recordset

' Open Microsoft Word using automation
Set objWord = New Word.Application
objWord.Documents.Add "c:\MyTemplate.Dot"
objWord.Visible = True

' Indicate where the table is to start by selecting the bookmark you
have
' already saved in the template
objWord.ActiveDocument.Bookmarks("Charges").Select

' This function REQUIRES an ADO recordset
strSQL = "SELECT [Service] as Description, format([Rate], 'Currency') as
Charge " _
& "from tblOrderCharges " _
& "BY [ListOrder];"
rsADO.Open strSQL, CurrentProject.Connection

' The function "CreateTableFromRecordset" is found in module, Module1.
With
CreateTableFromRecordset(objWord.ActiveDocument.Bookmarks("Charges").Range,
rsADO, True)
'Apply formatting
.AutoFormat wdTableFormatGrid8
.AutoFitBehavior wdAutoFitFixed

'Paragraph alignment
.Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
.Range.Font.Name = "Franklin Gothic Book"
.Range.Font.Size = 10

End With


hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


Dee said:
It was recomended to me to use the CreateTableFromRecordset in order to get
the line items on the subreport when they export to Word in a table instead
of the tab format in which they get exported right now. I saw some code for
this by searching the groups in google but am unsure where to put the code.
Where do I insert this code.... how do I use it? Here is the code that I
found:

Function CreateTableFromRecordset( _
rngAny As Word.Range, _
rstAny As ADODB.Recordset, _
Optional fIncludeFieldNames As Boolean = False) _
As Word.Table


' From Access 2000 Developer's Handbook, Volume I
' by Getz, Litwin, and Gilbert. (Sybex)
' Copyright 1999. All Rights Reserved.


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

' Creating the basic table is easy,
' just insert the tab-delimted text
' add convert it to a table
.InsertAfter varData
Set objTable = .ConvertToTable()

' Field names are more work since
' you must do them one at a time
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

Where do I insert this code? The fields that I will be using are: [Line
Item], [Quantity], [ItemCode], [Description], (below description on a
separate row would be [ItemComment]), [DUP], [Extension], and on the last
row would be =Sum([Extension].

Example:
Item Qty Product # Description
Unit Price Extension
1 37 965-225420-100 IR-522 Infrared Combustible
Hydrocarbon $971.25 $35,936.25
Sensor Assembly
with Splashguard


Net Total: $35,936.25
Can anyone help this beginner....

Thanks,

Dee
Click to expand...
 
Back
Top