It's a bit hard to debug that from here. I have used that procedure in one
project and did not have the problem.
Anyway, lets try another method. This will involve adding a userform to
your template that will contain a ListBox which will be populated by the
UserForm Initialize event with the data from a table in your database. For
the basics of creating a UserForm, see the article “How to create a
Userform” at:
http://word.mvps.org/FAQs/Userforms/CreateAUserForm.htm
In the Initialize event for the userform, use the following code
Private Sub UserForm_Initialize()
'allocate memory for the database object as a whole and for the active
record
Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Integer, j As Integer, m As Integer, n As Integer
'Open a database
Set myDataBase = OpenDatabase("D:\Access\ResidencesXP.mdb") 'modify the
path\filename for your database
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Owners", dbOpenForwardOnly)
'Modify the table name
'Get the number of fields in the table
j = myActiveRecord.Fields.Count
'Get the number of Records in the table
'Loop through all the records in the table until the end-of-file marker is
reached
i = 0
Do While Not myActiveRecord.EOF
i = i + 1
'access the next record
myActiveRecord.MoveNext
Loop
myActiveRecord.Close
'Set the number of columns in the listbox
ListBox1.ColumnCount = j
' Define an array to be loaded with the data
Dim MyArray() As Variant
'Load data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 2
Set myActiveRecord = myDataBase.OpenRecordset("Owners",
dbOpenForwardOnly)
m = 0
Do While Not myActiveRecord.EOF
MyArray(m, n) = myActiveRecord.Fields(n + 1)
m = m + 1
myActiveRecord.MoveNext
Loop
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
'Then close the database
myActiveRecord.Close
myDataBase.Close
End Sub
This will give you a multicolumn Listbox displaying all of the data from the
Access Table. Adjust the width of the columns in the ListBox to display the
data as required. To hide a column, set its width to 0.
Use code something like the following in a CommandButton click event
transfer the data from the record selected in the listbox into your document
Private Sub CommandButton1_Click()
ListBox1.BoundColumn = 1
ActiveDocument.Bookmarks("bookmarknameforthedatafromcolumn1").Range.InsertBefore
ListBox1.ValueNext i
ListBox1.BoundColumn = 2
ActiveDocument.Bookmarks("bookmarknameforthedatafromcolumn2").Range.InsertBefore
ListBox1.ValueNext i
ListBox1.BoundColumn = 3
ActiveDocument.Bookmarks("bookmarknameforthedatafromcolumn3").Range.InsertBefore
ListBox1.ValueNext i
ListBox1.BoundColumn = 4
ActiveDocument.Bookmarks("bookmarknameforthedatafromcolumn4").Range.InsertBefore
ListBox1.ValueNext i
' etc.
UserForm1.Hide
End Sub
As an alternative to using bookmarks, you can use document variables and
{DOCVARIABLE "varname" } fields in the document at the locations where you
want the data to appear. For this approach, you would use:
Private Sub CommandButton1_Click()
ListBox1.BoundColumn = 1
ActiveDocument.Variables("varnameforthedatafromcolumn1").Range.InsertBefore
ListBox1.ValueNext i
ListBox1.BoundColumn = 2
ActiveDocument.Variables("varnameforthedatafromcolumn2").Range.InsertBefore
ListBox1.ValueNext i
ListBox1.BoundColumn = 3
ActiveDocument.Variables("varnameforthedatafromcolumn3").Range.InsertBefore
ListBox1.ValueNext i
ListBox1.BoundColumn = 4
ActiveDocument.Variables("varnameforthedatafromcolumn4").Range.InsertBefore
ListBox1.ValueNext i
' etc.
ActiveDocument.Fields.Update
UserForm1.Hide
End Sub
In this case, everything will be done from Word and the user need not even
know about the Access database.
In all of this thread, I have assumed that you just wanted the data from a
single record to be used in the Word Document. If that is not the case, and
I should have brought this up before, then you should be using mailmerge.
--
Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.
Hope this helps
Doug Robbins - Word MVP
