Mail merge problem

[UniqueTII]

I have a database with an automated mail merge (through a table with SQL statements) and when I manually run the SQL, I can merge it with no errors, but when I try to run the automation, it tells me that no records are found. Any ideas of what I should check, or do you need more details?

 

[UniqueTII]

Here's the code:

Private Sub cmdMerge_Click()
'Written by Helen Feddema 12-30-98
'Last modified 10-4-2001

On Error GoTo ErrorHandler

   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim strDBName As String
   Dim strTable As String
   Dim strSQL As String
   Dim ctl As Access.Control
   Dim strName As String
   Dim strJobTitle As String
   Dim strCompanyName As String
   Dim strSalutation As String
   Dim strAddress As String
   Dim strTestFile As String
   Dim varItem As Variant
   Dim intIndex As Integer
   Dim intCount As Integer
   Dim appWord As Object
   Dim strWordDoc As String
   Dim strDocsPath As String
   Dim intRow As Integer
   Dim intRows As Integer
   Dim intColumn As Integer
   Dim intColumns As Integer
   Dim strTest As String
   Dim strCountry As String
   Dim strDocType As String
   Dim strSaveName As String
   Dim intSaveNameFail As String
   Dim i As String
   
   'Check that a letter has been selected
   strWordDoc = Nz(Me![cboSelect].Value)
   Set ctl = Me![cboSelect]
   If strWordDoc = "" Then
      MsgBox "Please select a document"
      ctl.SetFocus
      ctl.Dropdown
      GoTo ErrorHandlerExit
   End If
      
   'Create a Word instance
   Set appWord = CreateObject("Word.Application")
   appWord.Visible = True
   
   'Open the selected merge document
   appWord.Documents.Open strWordDoc
   
   'Set the merge data source to the SQL statement, and do the merge
   strDBName = ctl.Column(2)
   strSQL = ctl.Column(3)
   Debug.Print "Word doc: " & strWordDoc
   Debug.Print "Database: " & strDBName
   Debug.Print "SQL statement: " & strSQL
   
   With appWord
      .ActiveDocument.MailMerge.OpenDataSource name:=strDBName, _
         LinkToSource:=True, SQLStatement:=strSQL
      .ActiveDocument.MailMerge.Destination = wdSendToNewDocument
      .ActiveDocument.MailMerge.Execute
      .Documents(strWordDoc).Close SaveChanges:=wdDoNotSaveChanges
   End With
 'Call upPrint
ErrorHandlerExit:
   Set appWord = Nothing
   Exit Sub

ErrorHandler:
   If Err.Number = 5631 Then
      MsgBox "No data records to Word for the merge - check and see if a" _
         & " parameter was entered wrong.", vbExclamation + vbOKOnly, _
         "NO DATA RECORDS!"
      Resume ErrorHandlerExit
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
      'If there any many records to merge, there may be an error when
      'attempting to close the merge doc; in that case, uncomment the following
      'lines to delay 20 milliseconds and try again
      'Delay (20)
      'Resume
   End If


End Sub

 

[UniqueTII]

[vent]
Arghhhhhh! The code was fine. I found that out after I rewrote the entire thing using a new method and it still didn't work. The problem was that a date criteria in a query was static, rather than dynamic, so it was still looking for last year's results.
[/vent]