Need help with sending record to Word from Access

H

hlock

I really need some help! I am trying to send a record to Word through a
table in Access. The user enters information on a form and clicks a button
to run a make-table query and export the information to Word. I've been at
this for what seems like forever and it's just not working. My knowledge on
coding is very limited so I try to see what others have done. I'm afraid now
that I am just so confused that I don't know where to go. And - I still
haven't gotten to automating the make-table query. If anyone could take the
time to help, I would really appreciate it.

I'm getting a Object Required (424) error message at the InsertABookmark
lines. I am using Access 2003 and have referenced the Word 11.0 Object
Library, the Office 11.0 Object Library, and the DAO 3.6 Object Library

This is what I have so far:

Behind the button that the user clicks on:
Option Compare Database
Private Sub cmdBookMark_Click()

On Error GoTo Err_Handler
Dim strTemplateDoc As String
strTemplateDoc = ("P:\hlock\winword\New Assignment Letter.dot")

If Not IsNull(Me!ClaimNo) Then
CreateLetter strTemplateDoc
Else
MsgBox "No current record.", vbInformation, "Create Letter"
End If

Exit_here:
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation, "Error"
Resume Exit_here

End Sub


The coding to send the data to the bookmarked Word document:

Private Sub InsertAtBookmarks(objW As Object, _
objD As Object, _
strBookmark As String, _
varText As Variant)

' select bookmark
objD.Bookmarks(strBookmark).Select
' insert text at bookmark
objW.Selection.Text = Nz(varText, "")

End Sub
Sub CreateLetter(strTemplate As String)

' Opens a document in Word and inserts values from
' current record at bookmarks in Word document.
' Accepts: path to Word template file - String

On Error GoTo Err_Handler

Dim objWord As Object
Dim objDoc As Object
Dim tbl As Table

' return reference to form
Set tbl = Table![All Information for Letter]

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
End If

AppActivate "Microsoft Word"
On Error GoTo Err_Handler


' open Word document in maximised window
objWord.Visible = True
Set objDoc = objWord.Documents.Add(strTemplate)
objWord.WindowState = wdWindowStateMaximize

' insert text at bookmarks, getting values from form
InsertAtBookmarks objWord, objDoc, "Clmnum", Table![All Information for
Letter]!CLAIM
InsertAtBookmarks objWord, objDoc, "Clmoffce", Table![All Information
for Letter]!Loction
InsertAtBookmarks objWord, objDoc, "Csecat", Table![All Information for
Letter]!CsCt
InsertAtBookmarks objWord, objDoc, "Csetyp", Table![All Information for
Letter]!CsTp
InsertAtBookmarks objWord, objDoc, "DOL", Table![All Information for
Letter]!ACCDTE
InsertAtBookmarks objWord, objDoc, "Examnr", Table![All Information for
Letter]!Name
InsertAtBookmarks objWord, objDoc, "InsNam", Table![All Information for
Letter]!INSNAM
InsertAtBookmarks objWord, objDoc, "Party", Table![All Information for
Letter]!Party
InsertAtBookmarks objWord, objDoc, "Polnum", Table![All Information for
Letter]!POLICY

Set objDoc = Nothing
Set objWord = Nothing

Exit_here:
On Error GoTo 0
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume Exit_here

End Sub

Private Function GetWordApp() As Object

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set GetWordApp = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set GetWordApp = CreateObject("Word.Application")
End If

End Function

Private Sub ShowWord(wApp As Object, wDoc As Word.Document, wActiveDoc As
Word.Document)

wApp.Visible = True
wApp.WindowState = wdWindowStateMaximize
wDoc.Close (wdDoNotSaveChanges)
wActiveDoc.Activate
wApp.Activate

End Sub
 
L

Len Robichaud

Your code is not passing the variable to CreateLetter. The variable must be
enclosed in parenthesis.

If Not IsNull(Me!ClaimNo) Then
CreateLetter(strTemplateDoc)
Else
MsgBox "No current record.", vbInformation, "Create Letter"
End If
 
H

hlock

Thank you - that I should have caught. I did add the parentheses, but it's
still not working - I'm getting the same error.
 
K

Kevin K. Sullivan

hlock,

I don't understand why you want to run a make-table query. It shouldn't
be necessary. The main problem in your code is your variable defined:

Dim tbl As Table

and your construction of:

Table![All Information ....

You don't need tbl, and the object type Table is from Word (Word.Table),
so I don't think it is what you think it is. You can remove all
references to tbl. The Table![... construction does not exist in Access
- you can create a recordset object to reach values within a table, but
you really don't need to do that to solve this problem.

The good news is that I think this will work with a few changes: Have
the form send its current values to the routine:

For each line like this:
---old code-------
' insert text at bookmarks, getting values from form
InsertAtBookmarks objWord, objDoc, "Clmnum", Table![All Information
for Letter]!CLAIM
---------

try

---new code-----
' insert text at bookmarks, getting values from form
InsertAtBookmarks objWord, objDoc, "Clmnum", Forms!xyz!CLAIM
---------

You must replace the xyz with the name of the form that has the data on
it. If your form name has spaces or funky characters, use
Forms![Irregularly Named Form #37]!CLAIM. Access will pass the value of
the field in the form's current record into the InsertAtBookmarks
subroutine.

HTH,

Kevin
I really need some help! I am trying to send a record to Word through a
table in Access. The user enters information on a form and clicks a button
to run a make-table query and export the information to Word. I've been at
this for what seems like forever and it's just not working. My knowledge on
coding is very limited so I try to see what others have done. I'm afraid now
that I am just so confused that I don't know where to go. And - I still
haven't gotten to automating the make-table query. If anyone could take the
time to help, I would really appreciate it.

I'm getting a Object Required (424) error message at the InsertABookmark
lines. I am using Access 2003 and have referenced the Word 11.0 Object
Library, the Office 11.0 Object Library, and the DAO 3.6 Object Library

This is what I have so far:

Behind the button that the user clicks on:
Option Compare Database
Private Sub cmdBookMark_Click()

On Error GoTo Err_Handler
Dim strTemplateDoc As String
strTemplateDoc = ("P:\hlock\winword\New Assignment Letter.dot")

If Not IsNull(Me!ClaimNo) Then
CreateLetter strTemplateDoc
Else
MsgBox "No current record.", vbInformation, "Create Letter"
End If

Exit_here:
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation, "Error"
Resume Exit_here

End Sub


The coding to send the data to the bookmarked Word document:

Private Sub InsertAtBookmarks(objW As Object, _
objD As Object, _
strBookmark As String, _
varText As Variant)

' select bookmark
objD.Bookmarks(strBookmark).Select
' insert text at bookmark
objW.Selection.Text = Nz(varText, "")

End Sub
Sub CreateLetter(strTemplate As String)

' Opens a document in Word and inserts values from
' current record at bookmarks in Word document.
' Accepts: path to Word template file - String

On Error GoTo Err_Handler

Dim objWord As Object
Dim objDoc As Object
Dim tbl As Table

' return reference to form
Set tbl = Table![All Information for Letter]

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
End If

AppActivate "Microsoft Word"
On Error GoTo Err_Handler


' open Word document in maximised window
objWord.Visible = True
Set objDoc = objWord.Documents.Add(strTemplate)
objWord.WindowState = wdWindowStateMaximize

' insert text at bookmarks, getting values from form
InsertAtBookmarks objWord, objDoc, "Clmnum", Table![All Information for
Letter]!CLAIM
InsertAtBookmarks objWord, objDoc, "Clmoffce", Table![All Information
for Letter]!Loction
InsertAtBookmarks objWord, objDoc, "Csecat", Table![All Information for
Letter]!CsCt
InsertAtBookmarks objWord, objDoc, "Csetyp", Table![All Information for
Letter]!CsTp
InsertAtBookmarks objWord, objDoc, "DOL", Table![All Information for
Letter]!ACCDTE
InsertAtBookmarks objWord, objDoc, "Examnr", Table![All Information for
Letter]!Name
InsertAtBookmarks objWord, objDoc, "InsNam", Table![All Information for
Letter]!INSNAM
InsertAtBookmarks objWord, objDoc, "Party", Table![All Information for
Letter]!Party
InsertAtBookmarks objWord, objDoc, "Polnum", Table![All Information for
Letter]!POLICY

Set objDoc = Nothing
Set objWord = Nothing

Exit_here:
On Error GoTo 0
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume Exit_here

End Sub

Private Function GetWordApp() As Object

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set GetWordApp = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set GetWordApp = CreateObject("Word.Application")
End If

End Function

Private Sub ShowWord(wApp As Object, wDoc As Word.Document, wActiveDoc As
Word.Document)

wApp.Visible = True
wApp.WindowState = wdWindowStateMaximize
wDoc.Close (wdDoNotSaveChanges)
wActiveDoc.Activate
wApp.Activate

End Sub
Click to expand...
 
H

hlock

Some of the information to be passed to Word is pulled from a connection to
our AS400 system. The fields DOL, Insnam, Policy are pulled over from the
AS400, by a query, based on the Clmnum that the user enters on the table.
The fields Party, Csetyp, and Csecat are fields that simply pass through to
Word after being entered by the user on the form. The query takes quite a
while to run.

If I change out table for form, how do I get the information from pulls over
to pass to Word along with what the user enters? I know this has got to be
easier than I'm making it.

Kevin K. Sullivan said:
hlock,

I don't understand why you want to run a make-table query. It shouldn't
be necessary. The main problem in your code is your variable defined:

Dim tbl As Table

and your construction of:

Table![All Information ....

You don't need tbl, and the object type Table is from Word (Word.Table),
so I don't think it is what you think it is. You can remove all
references to tbl. The Table![... construction does not exist in Access
- you can create a recordset object to reach values within a table, but
you really don't need to do that to solve this problem.

The good news is that I think this will work with a few changes: Have
the form send its current values to the routine:

For each line like this:
---old code-------
' insert text at bookmarks, getting values from form
InsertAtBookmarks objWord, objDoc, "Clmnum", Table![All Information
for Letter]!CLAIM
---------

try

---new code-----
' insert text at bookmarks, getting values from form
InsertAtBookmarks objWord, objDoc, "Clmnum", Forms!xyz!CLAIM
---------

You must replace the xyz with the name of the form that has the data on
it. If your form name has spaces or funky characters, use
Forms![Irregularly Named Form #37]!CLAIM. Access will pass the value of
the field in the form's current record into the InsertAtBookmarks
subroutine.

HTH,

Kevin
I really need some help! I am trying to send a record to Word through a
table in Access. The user enters information on a form and clicks a button
to run a make-table query and export the information to Word. I've been at
this for what seems like forever and it's just not working. My knowledge on
coding is very limited so I try to see what others have done. I'm afraid now
that I am just so confused that I don't know where to go. And - I still
haven't gotten to automating the make-table query. If anyone could take the
time to help, I would really appreciate it.

I'm getting a Object Required (424) error message at the InsertABookmark
lines. I am using Access 2003 and have referenced the Word 11.0 Object
Library, the Office 11.0 Object Library, and the DAO 3.6 Object Library

This is what I have so far:

Behind the button that the user clicks on:
Option Compare Database
Private Sub cmdBookMark_Click()

On Error GoTo Err_Handler
Dim strTemplateDoc As String
strTemplateDoc = ("P:\hlock\winword\New Assignment Letter.dot")

If Not IsNull(Me!ClaimNo) Then
CreateLetter strTemplateDoc
Else
MsgBox "No current record.", vbInformation, "Create Letter"
End If

Exit_here:
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation, "Error"
Resume Exit_here

End Sub


The coding to send the data to the bookmarked Word document:

Private Sub InsertAtBookmarks(objW As Object, _
objD As Object, _
strBookmark As String, _
varText As Variant)

' select bookmark
objD.Bookmarks(strBookmark).Select
' insert text at bookmark
objW.Selection.Text = Nz(varText, "")

End Sub
Sub CreateLetter(strTemplate As String)

' Opens a document in Word and inserts values from
' current record at bookmarks in Word document.
' Accepts: path to Word template file - String

On Error GoTo Err_Handler

Dim objWord As Object
Dim objDoc As Object
Dim tbl As Table

' return reference to form
Set tbl = Table![All Information for Letter]

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
End If

AppActivate "Microsoft Word"
On Error GoTo Err_Handler


' open Word document in maximised window
objWord.Visible = True
Set objDoc = objWord.Documents.Add(strTemplate)
objWord.WindowState = wdWindowStateMaximize

' insert text at bookmarks, getting values from form
InsertAtBookmarks objWord, objDoc, "Clmnum", Table![All Information for
Letter]!CLAIM
InsertAtBookmarks objWord, objDoc, "Clmoffce", Table![All Information
for Letter]!Loction
InsertAtBookmarks objWord, objDoc, "Csecat", Table![All Information for
Letter]!CsCt
InsertAtBookmarks objWord, objDoc, "Csetyp", Table![All Information for
Letter]!CsTp
InsertAtBookmarks objWord, objDoc, "DOL", Table![All Information for
Letter]!ACCDTE
InsertAtBookmarks objWord, objDoc, "Examnr", Table![All Information for
Letter]!Name
InsertAtBookmarks objWord, objDoc, "InsNam", Table![All Information for
Letter]!INSNAM
InsertAtBookmarks objWord, objDoc, "Party", Table![All Information for
Letter]!Party
InsertAtBookmarks objWord, objDoc, "Polnum", Table![All Information for
Letter]!POLICY

Set objDoc = Nothing
Set objWord = Nothing

Exit_here:
On Error GoTo 0
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume Exit_here

End Sub

Private Function GetWordApp() As Object

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set GetWordApp = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set GetWordApp = CreateObject("Word.Application")
End If

End Function

Private Sub ShowWord(wApp As Object, wDoc As Word.Document, wActiveDoc As
Word.Document)

wApp.Visible = True
wApp.WindowState = wdWindowStateMaximize
wDoc.Close (wdDoNotSaveChanges)
wActiveDoc.Activate
wApp.Activate

End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Making a macro only work on certain files 2
Recordset merge 1
Directing/Specifying a Print Request/Location 9
Word automation - selections/formating during automation 3
Letter merge using backend data 2
Access automation error calling a Word template 3
VBA Problems ... (since windows update?) 14
Access list box merged with Word 0

Top