Retrieving repeatable data from a database

[K. Bryan]

Hi There,

I've got a quick question that I hope someone here can help put me in the
right direction on.

I have data stored in SQL Server that I would like to pull into a formatted
Word document.

Imagine the document having header information and then a table of
additional data (kinda like an Access report with a subform)

I've taken care of the header data; retrieving it and pushing it into
bookmarks, but my question is how do I create a table for the repeatable
data?

Meaning once I come back with a recordset of X number of records, how do I
loop thru them formatting them into a table?

Any help would be GREATLY appreciated.

Thanks,

_K Bryan

 

[Jezebel]

That's precisely what mailmerge does. Read Help on using MailMerge to create
a catalog.

 

[Doug Robbins - Word MVP]

Using a catalog or directory type mailmerge, you would come up against the
issue that you would be performing a "multiple items per condition (=key
field)" mailmerge which Word does not really have the ability to do:

See the "Group Multiple items for a single condition" item on fellow MVP
Cindy Meister's website at

http://homepage.swissonline.ch/cindymeister/mergfaq1.htm#DBPic


Or take a look at the following Knowledge Base Article

http://support.microsoft.com/default.aspx?scid=kb;en-us;211303


http://www.knowhow.com/Guides/CompoundMerges/CompoundMerge.htm

I have no experience of connecting to SQL Server data, but you may be able
to modify the following routine that creates a table in a document that is
populated with data from an Access database.



Dim myDataBase As Database

Dim myActiveRecord As Recordset

Dim i As Long

Dim dtable As Table, drow As Row

'Open a database

Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb")

'Access the first record from a particular table

Set myActiveRecord = myDataBase.OpenRecordset("Currencies",
dbOpenForwardOnly)

'Add a table to the document with one row and as many fields as there are in
the database table

Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1,
numcolumns:=myActiveRecord.Fields.Count)

Set drow = dtable.Rows(1)

'Loop through all the records in the table until the end-of-file marker is
reached

Do While Not myActiveRecord.EOF

'Populate the cells in the Word table with the data from the current
record

For i = 1 To myActiveRecord.Fields.Count

drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1)

Next i

'Add a new row to the Word table and access the next record

Set drow = dtable.Rows.Add

myActiveRecord.MoveNext

Loop

'The last row will be empty, so delete it

drow.Delete

'Then close the database

myActiveRecord.Close

myDataBase.Close

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP