Create/Fill Word Tables from Access

[adrienne_r30]

Sorry about the late Friday entry:

I have a form in Access that I use to create a word document (I created form fields in word and filled them from VBA code in Access). Everything works just fine. Now I am trying to create a table in Word that's filled with my Access fields. I am having trouble figuring this out. The Access fields are from a continuous report, so I'm guessing it has to be some sort of loop as well.

can someone please help

Thanks so much

 

[Uncle Gizmo]

See item 9 on Helens site:- http://www.helenfeddema.com/Code%20Samples.htm

 

[adrienne_r30]

Do you have any experience using this VBA? I have been at this all day and just can't seem to get it to work. below is my code:

Set rs = CurrentDb.OpenRecordset("tbl_PHOTO", dbOpenDynaset)
Selection.GoTo What:=wdGoToBookmark, Name:="fldPhoto"
With rs
Do While Not .EOF
Selection.TypeText Text:=![Number]
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=![Location]
Selection.MoveRight Unit:=wdCell
.MoveNext
Loop
.Close
End With
Selection.SelectRow
Selection.Rows.Delete

 

[JHB]

There must be more code as that!
And where do you've the code, in MS-Access or Word?
What problem do you've, some error message or ...?

 

[adrienne_r30]

the above code was put into my already existing code in access. Since that code works just find, and is lengthy, I just added the part of the code that included the loop. It does two things;

it will either do nothing, or (when I tweak it), it will output the first continuous record and wont stop. So it will give me the same record forever unless I close the word document. It seems to not be looping or stopping after the loop.

 

[The_Doc_Man]

This seems like you are dealing with extraneous issues.

Let's get down to a few simple ideas. Here is the overview. You can look up the details online.

You open a Word.Application object using

Set {object-variable} = CreateObjects( "Word.Application" )

Open a document using

{object-variable}.Documents.Open {filename}

You can make that the active document, which means the ActiveDocument shortcut works for you.

You find Word tables in the Tables collection of the current document. Each table is a collection of Rows, which in turn is a collection of Columns. (OR you can do the collection of Columns as a collection of Rows - the interface works either way). To find a given cell in a table requires something like

Set {Word.Cell-object} = ActiveDocument.Tables.Row(j).Column(k)

From there, you have the cell pointer and can start playing with the properties of that cell. So the loop might at worst be one loop through the Documents collection to find the document you want (and make it active) followed by a loop through that document's Tables collection to find the right table. After that, your loop is a nested loop through the Rows(j).Columns(k) collection to see individual cells. The innermost part of that loop is where you would diddle with the cell's attributes.

If your problem is that you always get the first record, your recordset needs tweaking. If you have a way to tell from your recordset where a given value should go, you could do the OTHER kind of loop - read through the recordset, extract the Row and Column number from data in the recordset, and randomly access the cells based on knowing the row and column number.

Your only REAL problem with this is that somewhere you must decide how to store the data in the recordset to define where it goes in the table, or must otherwise define a formula in your Word code that computes where the data goes. But somehow you have to decide your layout before you actually code that VBA segment.

 

[adrienne_r30]

I figured it out!! Wow that took some work but here is the code for anyone interested. I was missing the first line in the code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_PHOTO")

.FormFields("fldPhoto").Result = "****"
Selection.GoTo What:=wdGoToBookmark, Name:="fldPhoto"
With rs
Do While Not rs.EOF
Selection.TypeText Text:=![Number]
Selection.MoveRight Unit:=wdCell
Selection.TypeText Text:=![Location]
Selection.MoveRight Unit:=wdCell
rs.MoveNext
Loop
rs.Close
End With
Selection.SelectRow
Selection.Rows.Delete