[RESOLVED] ms access create and feed a table in ms word

[bambo]

I'm sure I've seen this somewhere, but I can't find where now...

I have an ms access application , and want to create a word document with a table based on a query.

I know how to add single values, and how to go over a query values, but how do I create a table that is as long as the number of the records in my query and feed its values?

I can do this very ugly with something like:

Set qdf = CurrentDb.QueryDefs("query1")
    qdf("my_station_no") = station_no
    Set rs = qdf.OpenRecordset
    
    rs.MoveFirst
     If rs.EOF Then goto bypass   
       MyDoc.variables("Item1") = Nz(rs("item_type_name"), "")
       MyDoc.variables("Amount1") = Nz(rs("ramount"), "")
    rs.MoveNext
    If rs.EOF Then  goto bypass   
    
       MyDoc.variables("Item2") = Nz(rs("item_type_name"), "")
       MyDoc.variables("Amount2") = Nz(rs("ramount"), "")
    rs.MoveNext

and so on as long as I have records, but needless to say I don't want it this way.

anyway I can create an array of fields in ms word lined up in a table, or something similar?

[si_the_geek]

Goto?

You just need a simple loop (and do not need MoveFirst - recordsets always go there by default), eg:

Set qdf = CurrentDb.QueryDefs("query1")
    qdf("my_station_no") = station_no
    Set rs = qdf.OpenRecordset
 
Dim lngRow as Long
    lngRow = 1
    Do While Not rs.EOF 
       MyDoc.variables("Item" & lngRow) = Nz(rs("item_type_name"), "")
       MyDoc.variables("Amount" & lngRow) = Nz(rs("ramount"), "")
       rs.MoveNext
       lngRow = lngRow + 1
    Loop
'..everything after "bypass"

[bambo]

OK , what about the word documet - how do I create a table with Item0..n and Amout0..n?

[si_the_geek]

I presumed you already had that, otherwise I would have recommended what I am about to - create a table with one row, and add a new row just before the "Loop" line (if not at EOF).

Instead of setting "MyDoc.variables("name")", you would just set "MyTable.Cells(row, col)" (or something like that - I haven't used Word in a while).

[bambo]

do you mean add the table line in word from access?
I guess I could probably record a macro in word to use its code for creating a new line from access... I'll try it soon

[si_the_geek]

That's exactly what I meant, and would have suggested the macro if I had been more awake!

If you have problems converting the macro to code for your program, post it here and we'll show you whats needed.

[DKenny]

Here is an example of building a table dynamically in word. The code in this example uses an array to build the table, but it can be easily adapted to work with a recordset.

[bambo]

exactly what I need!