Requested object is not available

[ebaily2]

I am using Microsoft Access 2010 and the following VBA script below to complete a Word Mail Merge. When I run it I get an error message saying "Requested object is not available". It opens the merge document but doesn't complete the merge. It worked fine when using Access/Word 2003 but crapped out when we switched to Access/Word 2010. Any help would be greatly appreciated.

Private Sub Open_Word_App_Click()
On Error GoTo Err_Open_Word_App_Click
 
    Dim oApp As Object
 
    Set oApp = CreateObject("Word.Application")
    oApp.Visible = True
    FacID = Me![Penske_ID]
    oApp.Documents.Open "\\ghna01aptl\\administration$\\Environmental\\SPCC_template\Penske SPCC Merge.docx"
 
    oApp.ActiveDocument.MailMerge.DataSource.QueryString = _
        "SELECT * FROM `GES_SPCC` WHERE (([Penske_ID] = '" & Me![Penske_ID] & "'))" & ""
 
        oApp.ActiveDocument.MailMerge.Destination = 0
        oApp.ActiveDocument.MailMerge.MailAsAttachment = False
        oApp.ActiveDocument.MailMerge.MailAddressFieldName = ""
        oApp.ActiveDocument.MailMerge.MailSubject = ""
        oApp.ActiveDocument.MailMerge.SuppressBlankLines = True
        oApp.ActiveDocument.MailMerge.DataSource.FirstRecord = 1
        oApp.ActiveDocument.MailMerge.DataSource.LastRecord = 1
 
        oApp.ActiveDocument.MailMerge.Execute Pause:=True
 
        oApp.Documents("Penske SPCC Merge.docx").Close SaveChanges:=0
 
        FacID = ""
 
Exit_Open_Word_App_Click:
    Exit Sub
 
Err_Open_Word_App_Click:
    MsgBox Err.Description
    Resume Exit_Open_Word_App_Click
 
End Sub

[NeoPa]

Let's start by sharing the line # the code crashes on. Without this there isn't really a question.

[zmbd]

To help get the line# that Neopa is asking for,

COMMENT out line 2, don't delete it as you can put it back after the troubleshooting.
(^_^)

ADD the STOP command at line 3

Now when your code encounters the STOP command you should go into debug mode where you can use [F8] to step thru the code until you find the error.

Please note the line the error occurs on and Please post the EXACT title, number, and text of the error message. Please do not alter, omit, nor abreviate the information provided in the error message

[ebaily2]

OK, when I get to the line of code below I get a Run time error "5852" Requested object is not available.

oApp.ActiveDocument.MailMerge.DataSource.QueryString = _
        "SELECT * FROM `GES_SPCC` WHERE (([Penske_ID] = '" & Me![Penske_ID] & "'))" & ""

[NeoPa]

That looks like line #11 then.

The obvious issue here (Obvious to us that is) is that the quote characters used are not correct quote characters, but something used when working in a document editor (Like MS Word for instance, as opposed to a text editor). If you replace these characters with single quotes then you should get past this problem.

As a very strong recommendation - never work on code in a word processor or document editor. It includes characters that make sense when displaying information but are very unhelpful when trying to produce code.