MS Word mailmerge window is autominimizing

[npearson713]

I just started having an issue with a mailmerge launching form in Access. A button I've set up, called "Order Form," uses the following code to launch Word, merge the data into the document, save the document, and close out the template. It's a large sample, and I think it may have come from this website at some point last year:

<---------START--------->

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_Confirmation, 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.*, TG_Orders.Order_Cost, TG_Orders.Order_Remembrance "
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 = "Order " & 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

<---------END--------->

Just today, this code started auto-minimizing the MS Word window, causing user error (after the documents been minimized, instead of clicking on the task bar, the user would click the button again, prompting that the document is already in use, would you like to save changes before closing the document, etc). Is there anything in this code that is telling Word to minimize? It's only happening on this one button, and there are 7 other mailmerge buttons on the same form, using the same code, that work fine.

Any help would be greatly appreciated,
Nate

 

[perlfan]

Hi there - did you manage to solve your problem? Could you maybe post your updated code and your word template? That would be cool! Thx, Frank