Creating Word Doc from Relational Recordsets using VBA

[Zedster]

I wish to create a word document containing field values for a parent recordset and child recordsets. I have created a word template that contains a table to control layout. In each cell I have created a bookmark to insert the recordset values. This works fine for all parent recordset fields.

For the child recordset values, I decided to create a nested table inside one of the template table cells using its bookmark to insert the nested table in the correct place. Each field in this nested table can then be populated with a value from the child recordset.

The issue I am having is how do a reference the cells in the child tables.

After adding two child tables to the parent table in the word document with the code below:

    Set rDiscussions = objWordDoc.Bookmarks.Item("Discussions").Range
    objWordDoc.Tables.Add Range:=rDiscussions, NumRows:=3, NumColumns:=2   
    Set rTasks = objWordDoc.Bookmarks.Item("Tasks").Range
    objWordDoc.Tables.Add Range:=rTasks, NumRows:=5, NumColumns:=5

At the end of the routine, if I attempt to count the tables, only 1 is returned not the three I expected.

Debug.Print objWordDoc.Tables.Count

> returns 1

When I try to loop through all tables, it only loops once:

    For Each tbl In objWordDoc.Tables
        Debug.Print tbl.Id
        Debug.Print tbl.Title
    Next

So on the face of it, there appears to be only one table, yet if I open up the word document and tab though the fields, sub tables have definitely been created, see attached image (table borders have been manually created to show they exist).

So I am confused regarding how to reference the sub tables and hence populate them both DAO.

I haven't included all the code because it is very lengthy and refers to numerous equally long sub routines

Can anyone advise what is happening and how I can reference the sub tables.

 

[arnelgp]

maybe loop through the Child table collections of main table?

 

[Zedster]

maybe loop through the Child table collections of main table?

How do I do that? I tried

    For Each tbl In objWordDoc.Tables
        Debug.Print tbl.Id
        Debug.Print tbl.Title
        For Each tblChild In tbl
            Debug.Print tblChild.Id
            Debug.Print tblChild.Title
        Next
    Next

But that came back with object doesn't exist.

I am fairly new to vba with "Word" and there is not as much about it on the web as vba with "Excel"

 

[arnelgp]

Dim bm As Bookmark
Dim r As Range
Dim t As Table
For Each bm In objWordDoc.Bookmarks
    For Each t In bm.Range.Tables
        Debug.Print t.Columns.Count
    Next
Next

 

[Zedster]

Thanks Arnel, you are right they are referred to as child tables, this I confirmed with the following code:

    Set tParent = objWordDoc.Tables(1)
    Set tDiscussions = tParent.Tables(1)
    Set tTasks = tParent.Tables(2)
    With tDiscussions.Borders
        .InsideLineStyle = wdLineStyleSingle
        .OutsideLineStyle = wdLineStyleDouble
    End With
    With tTasks.Borders
        .InsideLineStyle = wdLineStyleSingle
        .OutsideLineStyle = wdLineStyleDouble
    End With

 

[Uncle Gizmo]

I'm joining this thread because it's very interesting. In particular, I thought that the initial question by the OP was very well written!

I've Added it to the Sticky HERE:- https://www.access-programmers.co.uk/forums/threads/access-modules-vba-essentials.309325/

If you see a thread that you think should be added to a forum sticky, Mention it HERE:-

Access Essentials sticky thread recommendations​

 

[Pat Hartman]

This would require no code when done as an Access report and that would be my choice for this feature.

However, I am including a sample database that shows how to create various tables for "child" table items in a Word document. The example concatenates the child table into a string and places the string in a bookmark. Then sets the format to a table. There are several format variations in the commented out code so you can pick one you like.

 

[Zedster]

This would require no code when done as an Access report and that would be my choice for this feature.

However, I am including a sample database that shows how to create various tables for "child" table items in a Word document. The example concatenates the child table into a string and places the string in a bookmark. Then sets the format to a table. There are several format variations in the commented out code so you can pick one you like.

Thanks for that I will take a look. I must confess I didn't consider reports. I used reports when I first started using Access in 1998 and could never come up with an appearance I was happy with. I haven't created one now for over 10 years and for the last 10 years I have used Access to generate all my "reports" in Excel through VBA. On this occasion since my company has a standard word template for meeting minutes I thought I would try to use it.

 

[Pat Hartman]

You're welcome. I answered your PM about the references issue.