VBA Access 2010 Form data to Word 2010 error (1 Viewer)

historyb · Dec 24, 2011

Hello,

I am working on trying to get Access data from a form into Word when the person clicks a button. I got most of the kinks worked out by perusing the forums here but this one has me scratching my head. I keep getting a runtime error 424 access object required.

Here is my code and I highlighted in bold the relevant section that VBA highlights:

Code:

Private Sub cmd_Word_Click()

'Declare Variables
Dim sAccessAddress As String
Dim sAccessSalutation As String

'Build sAccessAddress
sAccessAddress = FirstName & " " & LastName & vbCrLf & Address & vbCrLf & StateOrProvince & " " & Zipcode

'Build sAccessSalutation
sAccessSalutation = FirstName & " " & LastName

'Declare and set Word object variables
Dim Wrd As New Word.Application
[B][COLOR=Black]Set Wrd = CreateObject(“Word.Application”)[/COLOR][/B] <- here is where the debug highlights in yellow

'Specify Path to Template
Dim sMergeDoc As String
sMergeDoc = Application.CurrentProject.Path & "C:\Users\dwilson\Desktop\Ron's Files\CHC Admin Program 2003\WordMergeDocument.dotx”"

'Open Word using template and make Word visible
Wrd.Documents.Add sMergeDoc
Wrd.Visible = True

'Replace Bookmarks with Values
With Wrd.ActiveDocument.Bookmarks
.Item(“CurrentDate”).Range.Text = Date
.Item(“AccessAddress”).Range.Text = sAccessAddress
.Item(“AccessSalutation”).Range.Text = sAccessSalutation
End With

'Open in PrintPreview mode, let user print
Wrd.ActiveDocument.PrintPreview

'Clean Up code
Set Wrd = Nothing
End Sub

MarkK · Dec 24, 2011

Those don't appear to be legitimate quote characters.

Code:

Set Wrd = CreateObject(“Word.Application”)

Note how the opening and closing quotes are not the same character??? Use the quotes you've used in this line....

Code:

sAccessSalutation = FirstName & " " & LastName

See if that makes a difference. These too ...

Code:

.Item(“CurrentDate”).Range.Text = Date
.Item(“AccessAddress”).Range.Text = sAccessAddress
.Item(“AccessSalutation”).Range.Text = sAccessSalutation

... don't look right.
Also, you don't need to do both of these....

Code:

Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")

This is enough when you use the New keyword...

Code:

Dim Wrd As New Word.Application

... which declares the variable and creates an instance all at once, or you can do ...

Code:

Dim Wrd As Word.Application
Set Wrd = CreateObject("Word.Application")

... which declares the variable and then creates an instance on the next line.
Happy holidays,
Mark

historyb · Dec 24, 2011

Thank you I copied it out my book, I will fix it post haste.

On Edit: It worked, thank you.

I want to say Merry Christmas and Happy Holidays

Now I got a run time error 5914 the requested member of collection does not exist.

MarkK · Dec 26, 2011

What line of code causes the error?

historyb · Dec 26, 2011

I fixed the error and found out that the bookmarks in my template did not save. Now when I have this line:

Code:

[B].Item("AccessSalutation").Range.Text = sAccessSalutation[/B]

Active the Address doesn't come over just the salutation. Heres my cleaned up code too

Code:

Private Sub cmd_Word_Click()

'Declare Variables
Dim sAccessAddress As String
Dim sAccessSalutation As String

'Build sAccessAddress
sAccessAddress = FirstName & " " & LastName & vbCrLf & Address & vbCrLf & StateOrProvince & " " & Zipcode

'Build sAccessSalutation
sAccessSalutation = FirstName & " " & LastName

'Declare and set Word object variables
Dim Wrd As Word.Application
Set Wrd = CreateObject("Word.Application")

'Specify Path to Template
Dim sMergeDoc As String
sMergeDoc = Application.CurrentProject.Path & "\WordMergeDocument.dotx"

'Open Word using template and make Word visible
Wrd.Documents.Add sMergeDoc
Wrd.Visible = True

'Replace Bookmarks with Values
With Wrd.ActiveDocument.Bookmarks
.Item("CurrentDate").Range.Text = Date
.Item("AccessAddress").Range.Text = sAccessAddress
.Item("AccessSalutation").Range.Text = sAccessSalutation
End With

'Open in PrintPreview mode, let user print
Wrd.ActiveDocument.PrintPreview

'Clean Up code
Set Wrd = Nothing
End Sub

VBA Access 2010 Form data to Word 2010 error (1 Viewer)

historyb

Registered User.

MarkK

bit cruncher

historyb

Registered User.

MarkK

bit cruncher

historyb

Registered User.

Similar threads

Users who are viewing this thread