Open Word from Access with required data

[BH]

Big headache!

I want to open up a word document and display in the document data from fields that I have on my open Access form.

I have used a command button on click event to open up the required document as below:

Dim oApp As Word.Application
Dim oDoc As Word.Document

Set db = CurrentDb

Set oApp = CreateObject("Word.Application&quot
Set oDoc = oApp.Documents.Open("c:\My Documents\DocumentRequired.doc&quot

oApp.Visible = True

This works fine, what I want to do then is take text from some of the Access fields and place them into the document. I have set up bookmarks in the document but my following code does not work:

oDoc.editgoto "Name of Bookmark"
oDoc.Insert Form![Open Form]![Field Required]

I am trying to use code rather than use mail merge or doing the document as an Access report. Can anyone help PLEASE!!!!!!!!!

BH

 

[BSando]

BH,

here is some of my code based on data from a query. You may want to amend it to suit you.

Private Sub Command34_Click()
On Error GoTo Err_Command34_Click

Dim rst As Recordset
Dim qdf As QueryDef
Dim prm As Parameter
Dim objword As Word.Application

Set qdf = CodeDb.QueryDefs("query name&quot
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset()

Set objword = New Word.Application

Do Until rst.EOF
With objword
.Visible = True
.Documents.add Template:=("C:\word document.dot&quot
.Selection.Goto Name:="name"
.Selection.TypeText Text:=rst![Name]
.Selection.Goto Name:=("Address&quot
.Selection.TypeText Text:=rst![address]
.Selection.Goto Name:=("fax&quot
.Selection.TypeText Text:=rst![fax]
.Selection.Goto Name:=("Phone&quot
.Selection.TypeText Text:=rst![phone]

End With
rst.MoveNext

Loop

rst.close

Set rst = Nothing
Set prm = Nothing
Set qdf = Nothing
Set objword = Nothing

Err_Command34_Click:


End Sub

 

[BH]

BSando

Thanks for this. Copied the code and made alterations to the fields, tried it out but ran up a problem with 'Dim qdf As QueryDef'.

Is there something else that I need to do?

BH

 

[BSando]

You should check your references. go to the code. Select tools, References and make sure the following is ticked.
Microsoft DAO 3.6 object library
Microsoft ActiveX Data object library
Microsoft Word 10.0 object library (you may have something different like Word 8)
OLE automation

 

[BH]

BSando

Ticked off the references as suggested but still no joy.

My List reads:
Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data object 2.1library
MISSING: ADO Ext 2.7 for DDL and Security
Microsoft DAO 3.6 object library
Microsoft Word 9.0 object library

Do they have to be in a certain priority?

Thanks for your help again

BH

 

[OnTheFly]

Try adding the DAO referance to the Dim of the Query Def

Dim qdf AS DAO.QueryDef

If you are using both DAO and ADO in the same database you have to qulify certain objects.

 

[BH]

OnTheFly

Looks like we are starting to get there!

There is no error coming up now but when the command button is clicked, nothing happens. I have amended my code to suit the relevant query, the code now reads:

Private Sub Command34_Click()

On Error GoTo Err_Command34_Click

Dim rst As Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter
Dim objword As Word.Application

Set qdf = CodeDb.QueryDefs("qryContacts&quot
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset()

Set objword = New Word.Application

Do Until rst.EOF
With objword
.Visible = True
.Documents.Add Template:=("C:\word document.dot&quot
.Selection.Goto Name:="Surname"
.Selection.TypeText Text:=rst![Surname]
.Selection.Goto Name:=("Address1&quot
.Selection.TypeText Text:=rst![Address1]
.Selection.Goto Name:=("Telephone External&quot
.Selection.TypeText Text:=rst![Telephone External]
.Selection.Goto Name:=("Email Address&quot
.Selection.TypeText Text:=rst![Email Address]

End With
rst.MoveNext

Loop

rst.Close

Set rst = Nothing
Set prm = Nothing
Set qdf = Nothing
Set objword = Nothing

Err_Command34_Click:

End Sub

Have I missed something out?

You guys are so helpful

Thanks

BH

 

[OnTheFly]

i am kind of surprised you are not getting an error but the error handler might be defraying it so you don't know about it.

You need to qualify the other DAO objects just like you did the QueryDef

Dim rst AS DAO.Recordset
Dim prm AS DAO.Parameter

I would also add the following above and below the error handler:

Exit Sub

Err_Command34_Click:
MsgBox Err.Number & " - " & Error$
Exit Sub

This will help to determine if you are hitting an error because the way it is, it would just exit the procedure without you ever knowing there was a problem.



Hope this helps.

OnTheFly

 

[BH]

OnTheFly

Getting there!

Altered the code as suggested but still had problems.

Investigated the missing reference message from the reference list and managed to find the reference in the system. There is no longer a 'missing' message.

On clicking the command button word is now started but it could not find the template so I have created a new document and saved it as word document.dot

This now opens but no text appears on it, so I have inserted a bookmark labeling it Surname. When clicking the button I now get the Surname of the first persons surname from the query but a error message saying 3265 - item not found in this collection.

Have I now got to design the template and add bookmarks for all the required fields?

With the help of yourself and BSando I am learning a lot from this problem!

Thanks

BH

 

[OnTheFly]

Yes add the bookmarks and it should fill in the data for you.

Hope this helps.

OnTheFly

 

[grtammi]

Please see faq702-2379.


Boss quote from an office meeting: We're going to continue to have these meetings until we figure out why no work is getting done ...

 

[BH]

Big thanks for everyones input.

Not only is the problem solved but I have learnt alot.

Excellent!

grtammi, your FAQ looks good, I am going to try it out to see how it compares to the code I am now using.

Cheers

BH