Letters merged to Word from Access going out of order

[npearson713]

Hello everyone! I've had a mailmerge working perfectly for over a year, but just last week it started acting funny. It used to be that the letters/orders would print sequencially (1-20) but now they print in an odd sequence (could be something like 13-20, then 1-12. I've also seen it even stranger, at 5-10, 1-4, 11-20, etc.) I have no idea how this started happening, as I never touched the code for it.

The only step I've taken so far is to modify the query results so that they are sorted ascending by the number. When I first saw the query results, they were in the same screwy order as the letters, but once I clicked "Sort Ascending" they became sequencial. However, the letters still merge out of sequence. Any ideas?

Nate

 

[Remou]

Please post the code.

 

[npearson713]

Here's the code for the button's OnClick:

Private Sub OrderForm_Click()
'creates an SQL statement to be used in a query def
'n 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_Date FROM tmpCurrentTGOrderID;")

While Not rec.EOF
    val = rec("Order_Date")
    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, TG_Orders.Order_Source "
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_Date= #" + 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

And here's the code for the Call SetQuery

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

And here's the last bit of code that opens Word:

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\LOAv109.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 & "\Delete\" & 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

I had to post everything because I really have no idea where this problem is coming from! This is copy and pasted directly from my DB, so it's exactly as I'm using it. Hope someone can find it,
Nate

 

[npearson713]

Fixed the problem by adding the line "ORDER BY TG_Orders.Order_ID" to the strSQL portion of the OnClick property. This code now reads:

strSQL = "SELECT TG_Orders.Order_ID, TG_Orders.Order_Date, ... TG_Orders.Order_Source "
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_Date= #" + val + "# ORDER BY TG_Orders.Order_ID"

Nate