problem with syntax for filling wordtable

[gerard1979]

I have to set up a wordDoc from Access 2000. I make a few recordset which handle this for me, but stuck on filling a table, I don't understand the neccesary syntaxis.
I set up the doc with bookmarks. I make the table in the doc. It's only one table. I set the values in the first row and need a nested loop of 2 recordsets to fill it. I have something like this but get strange errors.:

Dim mytable As Table
Dim cnn As ADODB.Connection
Dim rst, rstDetails As New ADODB.Recordset

Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Set rstDetails = New ADODB.Recordset
'voer de query uit om de juiste regel te vinden in die betrekking heeft op de offerte
strSQL = "Select * From tblLocatie WHERE [loc_werkbonID] = " & TempWerkbonID

rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly
rst.MoveLast
rst.MoveFirst
Dim rij As Integer
Dim TableStr As String
TableStr = ""
rij = 3
Set mytable = ActiveDocument.Tables(1)
With rst
mytable.Cell(rij, 1).Range.InsertAfter .Fields("loc_beschrijving&quot
rij = rij + 1
While Not .EOF
strSQL = "SELECT * FROM qryTestLocaties WHERE [locatie_ID] = " & .Fields("locatie_ID&quot
rstDetails.Open strSQL, cnn, adOpenStatic, adLockReadOnly
While Not rstDetails.EOF
TableStr = TableStr & rstDetails.Fields("sub_omschrijving&quot & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbCr
mytable.Cell(rij, 1).Range.InsertAfter TableStr
rij = rij + 1
.MoveNext
Wend
Wend
.Close
End With

can anybody tell me something about syntax or give good documents about it, or sample code?
Thnx in advance,
Gerard

 

[pgk]

Hi,
Dim mytable As Table
Dim cnn As ADODB.Connection
Dim rst, rstDetails As New ADODB.Recordset

Set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Set rstDetails = New ADODB.Recordset
'voer de query uit om de juiste regel te vinden in die betrekking heeft op de offerte
strSQL = "Select * From tblLocatie WHERE [loc_werkbonID] = " & TempWerkbonID

rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly
rst.MoveLast
rst.MoveFirst
Dim rij As Integer
Dim TableStr As String
TableStr = ""
rij = 3
Set mytable = ActiveDocument.Tables(1)
With rst
While Not .EOF
mytable.Cell(rij, 1).Range.InsertAfter .Fields("loc_beschrijving&quot
'rij = rij + 1 'Commented this out

strSQL = "SELECT * FROM qryTestLocaties WHERE [locatie_ID] = " & .Fields("locatie_ID&quot
rstDetails.Open strSQL, cnn, adOpenStatic, adLockReadOnly
While Not rstDetails.EOF
TableStr = TableStr & rstDetails.Fields("sub_omschrijving&quot & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbCr
mytable.Cell(rij, 1).Range.InsertAfter TableStr
rij = rij + 1
rstDetail.MoveNext
Wend
.MoveNext
Wend
.Close
End With

Please watch out for the word wraps. If this is not what you wanted, lpease send us more details as to the data structure in the tables and the way you want it in the Word table. A sample table data might prove useful Hope it helps. Let me know what happens.
With regards,
PGK

 

[gerard1979]

I think pushing the data into the table works fine but I get some strange errors on calling the table or something
par example: extern servercomputer doesn't exist or is not available, (number 462)

the error occurs on the line
set mytable = activedocument.table(1)

 

[pgk]

Hi,
I have posted some sample code that I use to fill up a table in word. This table has already been created in a Word document with a blank row and the number of columns required.

I use this code to access this particular Word doc and to fill up the table in it.

Private Sub fillTable_Click()
Dim db As Database
Dim rs As Recordset
Dim r As Integer
Dim wordObject As Word.Application
Dim wordDoc As New Word.Document
Dim myTable As Word.Table
Set wordObject = CreateObject("word.application&quot
Set wordDoc = wordObject.Documents.Open("C:\samp.doc&quot
wordDoc.Activate
Set db = CurrentDb
Set rs = db.OpenRecordset("Table11&quot
Set myTable = wordDoc.Tables(1)
r = 1
rs.MoveFirst
Do While Not rs.EOF
myTable.AutoFitBehavior wdAutoFitContent
myTable.Cell(r, 0).Range.InsertAfter rs.Fields(0)
myTable.Cell(r, 2).Range.InsertAfter rs.Fields(1)
myTable.Rows.Add
r = r + 1
rs.MoveNext
Loop
rs.Close
db.Close
wordDoc.Save
MsgBox "Operation Completed"
wordDoc.Close
End Sub

Hopefully you can build on this to suit your needs.
Hope it helps. Let me know what happens.
With regards,
PGK

 

[gerard1979]

thanx for ur quick and worthful reply dgp but 1 more question
How do u handle the problem with adding rows.
the rows appear before other rows. so I can't order the records in the right way. I want a row at the end. Manually this is done to go to the right border of the last cell,( do tab in the last cell) and than push <enter>
but what is the prog. way.
second question How can I convert a number to an Euro value

If u can help me this way, ur so great!!!

Gerard

 

[pgk]

Hi,
To add a row, use the <tableName>.Rows.Add ( I have done this in my code).

I don't understand what you mean by &quot;How can I convert a number to an Euro value&quot;. Are you talking of currency conversion or do you want your table to use the Euro symbol in the table?





Hope it helps. Let me know what happens.
With regards,
PGK

 

[gerard1979]

I'm talking about currency conversion.
so looking for a format(number, ....) way but don't know what's the euro notation.

I understand ur way for adding rows, the problem is: this rows come up before other rows. So I have a construction in almost the same way as u with cell(row, column): row = row + 1 etc. But now the rows get two pieces of text in it, because what's first row 4 is now row 5 etc.
so I'm looking for a way that add the rows AFTER the existing rows,

I Appreciate ur help!
Gerard

 

[pgk]

Hi,
As per my code, The rows will be correct in their positions. Row 1 will be record 1 and so on. The extra row will be at the bottom only. It is possible that your code add a row and then aassign the elements to the cells. Check it out.

In case you want to add these rows after the existing table rows, replace the r = 1 line with
r = myTable.Rows.Count + 1

This will allow existing rows and append to the table.

When you say currentcy conversion, does this mean you enter a value in say dollars and want it to be converted into Euros? If that is the case, it is difficult because the rates will keep on fluctuating.

Hope it helps. Let me know what happens.
With regards,
PGK

 

[gerard1979]

I'll check what u say about the rows, I hope ur right. But not sure now.
The second problem is.
when push the data to the table the number is 697 and I need 697,00 with an Euro sign (can't find now
format(value, &quot;standard&quot makes 697 to 697,00 but how can i convert to euro?

 

[pgk]

Hi,
Set the Format property of the field concerned to Euro. This option will be available only if the field is of type Number or Currency. Hope it helps. Let me know what happens.
With regards,
PGK