Automating Merge Crashes Word 9/10 times

[npearson713]

I'm using the information provided in this FAQ, adapted to fit my own database: faq705-3237.

I'm running it in Access 2002 (2002 file format) on a server running Windows 2000 Server. MS Word is also v.2002, and is crashing 9 out of 10 times before the merge displays a new document. After the crash, the program reloads with the document properly merged, but upon closing of the program, it crashes out again. Word works fine outside of this problem, as does all of my other Access code. If someone would like to see my specific code, I can email/PM them with it.

Thank you very much for any help you can provide with this!
Nate

 

[Axoliien]

Without delving into your code, we had somewhat the same problem earlier this year, though not as bad as your description. Eventually our solution came down to early binding using visual basic. It may not fix your issue, but it is a good place to start.
Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic

 

[RoyVidar]

What Axoliien brings up, might very well be one of the issues causing this. Just thought I'd mention I've recently implemented something mostly stolen from this faq faq181-5088 by foolio12, which runs nicely on 2000-2003.

And - these fora are dedicated to the excange of information between computer professionals, so if someone is to take a look at your code, post it here. Bringing it offsite, kind of defeats the purpose of the site. Some info on how to get the best answers/site netiquette is found in faq181-2886.

Roy-Vidar

 

[npearson713]

Thank you both for responding to my problem. I tried both of your suggestions, but I couldn't get either of them to work for me, so I just went back to what I had and re-did everything. It now works, and Word hasn't crashed yet, but I still don't trust it.

I'm going to post my code now (I'm new to the forums and didn't know I could post entire modules... cool!), and maybe someone could point out where it might possibly fail again.

Here's the query I'm referencing:

SELECT TG_Orders.Order_ID, TG_Orders.Order_Date, TG_Orders.Order_HonoredPerson, TG_Orders.Order_TreeGiver, TG_Orders.Order_PlantingState, TG_Orders.Order_Product, TG_Orders.Order_Type, TG_Orders.Order_Line1, TG_Orders.Order_Line2, TG_Orders.Order_Card, TG_Orders.Order_Occasion, TG_Orders.Order_First, TG_Orders.Order_Middle, TG_Orders.Order_Last, TG_Orders.Order_Have, TG_Orders.Order_Digits, TG_Orders.Order_CardType, TG_Orders.Order_Comments, TG_Orders.Order_Donate, TG_Customers.*, TG_Shipping.*
FROM (TG_Customers INNER JOIN TG_Orders ON TG_Customers.Customer_ID = TG_Orders.Customer_ID) INNER JOIN TG_Shipping ON TG_Orders.Order_ID = TG_Shipping.Order_ID
WHERE (((TG_Orders.Order_ID)=32));

The "32" in the last line from above is put there by the OrderForm_Click Sub.

And here's the code I'm running on the button:

Private Sub OrderForm_Click()
'creates an SQL statement to be used in a query def
'On Error GoTo ErrorHandler

Dim val As String
Dim db As Database
Dim rec As DAO.Recordset
Dim strSQL As String
Dim strDocumentName As String 'name of the template document

Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT Order_ID FROM tmpCurrentTGOrderID;")

While Not rec.EOF
    val = rec("Order_ID")
    rec.MoveNext
Wend

rec.Close

'Select all records from the record table were the table's Order_ID field matches
'that of the temporary table's.
'qry = "SELECT TG_Orders.Order_ID, TG_Orders.Order_Date, TG_Orders.Order_HonoredPerson, TG_Orders.Order_TreeGiver, TG_Orders.Order_PlantingState, TG_Orders.Order_Product, TG_Orders.Order_Type, TG_Orders.Order_Line1, TG_Orders.Order_Line2, TG_Orders.Order_Card, TG_Orders.Order_Occasion, TG_Orders.Order_First, TG_Orders.Order_Middle, TG_Orders.Order_Last, TG_Orders.Order_Have, TG_Orders.Order_Digits, TG_Orders.Order_CardType, TG_Orders.Order_Comments, TG_Orders.Order_Donate, TG_Customers.*, TG_Shipping.* FROM (TG_Customers INNER JOIN TG_Orders ON TG_Customers.Customer_ID = TG_Orders.Customer_ID) INNER JOIN TG_Shipping ON TG_Orders.Order_ID = TG_Shipping.Order_ID;"

strDocumentName = "\TG_OrderForm.doc"

strSQL = "SELECT TG_Orders.Order_ID, TG_Orders.Order_Date, TG_Orders.Order_HonoredPerson, TG_Orders.Order_TreeGiver, TG_Orders.Order_PlantingState, TG_Orders.Order_Product, TG_Orders.Order_Type, TG_Orders.Order_Line1, TG_Orders.Order_Line2, TG_Orders.Order_Card, TG_Orders.Order_Occasion, TG_Orders.Order_First, TG_Orders.Order_Middle, TG_Orders.Order_Last, TG_Orders.Order_Have, TG_Orders.Order_Digits, TG_Orders.Order_CardType, TG_Orders.Order_Comments, TG_Orders.Order_Donate, TG_Customers.*, TG_Shipping.* " 
strSQL = strSQL + "FROM (TG_Customers INNER JOIN TG_Orders ON TG_Customers.Customer_ID=TG_Orders.Customer_ID) INNER JOIN TG_Shipping ON TG_Orders.Order_ID=TG_Shipping.Order_ID "
strSQL = strSQL + "WHERE TG_Orders.Order_ID=" + val

Call SetQuery("TG_OrderFormQuery", strSQL)
Dim strNewName As String  'name to save merged document as
strNewName = "Custom " & Format(CStr(Date), "MMM dd yyyy")
Call OpenMergedDoc(strDocumentName, strSQL, strNewName)

Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
    Exit Sub

End Sub

Private Sub SetQuery(strQueryName As String, strSQL As String)
 On Error GoTo ErrorHandler
        'set the query from which the merge document will pull its info
        Dim qdfNewQueryDef As QueryDef
        Set qdfNewQueryDef = CurrentDb.QueryDefs(strQueryName)
        qdfNewQueryDef.SQL = strSQL
        qdfNewQueryDef.Close
        RefreshDatabaseWindow
Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
    Exit Sub
End Sub

Private Sub OpenMergedDoc(strDocName As String, strSQL As String, strReportType As String)
On Error GoTo WordError
    'opens an instance of word, opens a merge template which has its data source
    'already linked to a query in this database, merges the template,
    'saves the merged file with a descriptive name, then closes the merge template
    
    'Set the directory for any labels generated
    Const strDir As String = "D:\LOA-Data\Merge Templates"
    Dim objWord As New Word.Application
    Dim objDoc As Word.Document
    objWord.Application.Visible = True
    Set objDoc = objWord.Documents.Open(strDir & strDocName)
    ' Make Word visible so that if any errors occur, you can close the instance of Word manually
    objWord.Application.Visible = True
    'merge to a new document
'if you are not sure of the SQLStatement to use in your OpenDataSource string, uncomment the following four lines to have the 'current SQLstatement print in the immediate window. You can then copy the returned string to your code
'Debug.Print objWord.Application.ActiveDocument.MailMerge.DataSource.QueryString
'objWord.Quit
'Set objWord = Nothing
'Exit Sub

  objDoc.MailMerge.OpenDataSource _
        name:="D:\LOA-Data\LOAv817.mdb", _
        LinkToSource:=True, AddToRecentFiles:=False, _
        Connection:="QUERY TG_OrderFormQuery", _
        SQLStatement:="SELECT * FROM `TG_OrderFormQuery`"
'notice that this is not the SQL statement that makes up the QueryDef of the query.  It
'is the SQL statement that tells Word whether to use all the records returned by the
'Query. Notice also the funky single quotes – this is what DataSource.QueryString returned
'to me in the immediate window.  I’ve also seen the query name written in 'brackets [ ],
'but have never tested this code with them.
   
   objDoc.MailMerge.Destination = wdSendToNewDocument
   objDoc.MailMerge.Execute
'save the merged document with a descriptive name
'you can delete this line if you want to leave the document with the default name “Labels 1”  or “Letters 1”
    objWord.Application.Documents(1).SaveAs (strDir & "\" & strReportType & ".doc")
    'close the merge template
    objWord.Application.Documents(2).Close wdDoNotSaveChanges
    'release the variables
    Set objWord = Nothing
    Set objDoc = Nothing
       
Exit Sub
WordError:
        MsgBox "Err #" & Err.Number & "  occurred." & Err.Description, vbOKOnly, "Word Error"
        objWord.Quit
End Sub

'Private Sub cmdGoCustom_Click()
'creates an SQL statement  to be used in a query def
'On Error GoTo ErrorHandler
'Dim strSQL As String
'strSQL = "SELECT Contacts.PostalCode, Contacts.LastName, Contacts.FirstName, Contacts.DistrictNo, Contacts.County, Contacts.Address, Contacts.Address2, Contacts.City, Contacts.StateOrProvince, Contacts.Office, Title.Title, [Contact Types].ContactType FROM ([Contact Types] RIGHT JOIN Contacts ON [Contact Types].ContactTypeID=Contacts.ContactTypeID) INNER JOIN Title ON Contacts.TitleID=Title.TitleID ;"

'Dim strDocumentName As String  'name of the template document
'strDocumentName = "\Merge Templates\TG_OrderForm.doc"

'Call SetQuery("qryLabelQuery", strSQL)
'Dim strNewName As String  'name to save merged document as
'strNewName = "Custom " & Format(CStr(Date), "MMM dd yyyy"
'Call OpenMergedDoc(strDocumentName, strSQL, strNewName))
'Exit Sub
'ErrorHandler:
'    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
'    Exit Sub
'End Sub

Again, any help is greatly appreciated.
Nate