Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Mail merge data source (2003)

    I have a query which serves as the datasource for a mail merge word document. The document is open with the included automation code. If I add use a criteria which references a form I get an error message that 'word was unable to open the data source ". Word does not open. If the criteria is removed than the process code works fine. I should mention that there are other criteria in the query but none are references.

    The code;

    Private Sub cmdMerge_Click()
    Dim wrdApp As Object
    Dim wrdDoc As Object
    Dim blnStartWord As Boolean
    Dim strSQL As String

    On Error Resume Next

    Set wrdApp = GetObject(, "Word.Application")
    If wrdApp Is Nothing Then
    Set wrdApp = CreateObject("Word.Application")
    If wrdApp Is Nothing Then
    MsgBox "Can't start Word.", vbExclamation
    Exit Sub
    Else
    blnStartWord = True
    End If
    End If

    On Error GoTo ErrHandler

    strSQL = "SELECT * FROM qryNew"

    Set wrdDoc = wrdApp.Documents.open("C:My DocumentsTemplates & FormsLettersNewPt.doc")
    With wrdDoc.MailMerge
    .OpenDataSource _
    Name:="", _
    LinkToSource:=True, _
    Connection:="DSN=MS Access Database;DBQ=" & CurrentDb.Name, _
    SQLStatement:=strSQL, _
    SubType:=8 ' = wdMergeSubTypeWord2000
    .SuppressBlankLines = True
    End With

    wrdApp.Visible = True
    wrdApp.Activate

    ExitHandler:
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    If Not wrdDoc Is Nothing Then
    wrdDoc.close SaveChanges:=False
    End If
    If Not wrdApp Is Nothing And blnStartWord Then
    wrdApp.Quit SaveChanges:=False
    End If
    Resume ExitHandler

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge data source (2003)

    You are using ODBC to connect to the Access data source. This is my preferred method, but it doesn't "know" about Access forms. In other words, if you have an SQL statement such as

    strSQL = "SELECT * FROM qryNew WHERE FieldName = Forms!frmSomething!txtSomething"

    opening the data source will fail. Instead, use

    strSQL = "SELECT * FROM qryNew WHERE FieldName = " & Me.txtSomething

    if FieldName is a number field, or

    strSQL = "SELECT * FROM qryNew WHERE FieldName = " & Chr(34) & Me.txtSomething & Chr(34)

    if FieldName is a text field, or

    strSQL = "SELECT * FROM qryNew WHERE FieldName = #" & Format(Me.txtSomething, "mm/dd/yyyy") & "#"

    if FieldName is a date field.

  3. #3
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge data source (2003)

    I will try your suggestions but I have two questions about this issue
    1-the criteria resides within the 'qryNew' i.e. and the query returns the appropriate and correct results
    2-if I open the word document the same query is prompted and works fine.

    Any explanation would be helpful for future issues

    Thanks
    nebbia

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge data source (2003)

    You'll have to use the SQL for qryNew than, and modify the criteria the way I indicated.

    I'm not sure I understand what you mean by "the same query is prompted". I thought it referred to a form?

  5. #5
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge data source (2003)

    I'll try be clearer and more precise
    1-the query has as a criteria an autonumber ID (primary key) from an 'open' form. Either the form must be open or the ID must be entered into the query for it to work
    2- if I directly open the word document a popup opens with; "opening this document will run the following SQL command: select * from [qryNew]. If I proceed the query results are returned and placed in the mail merge document. correctly.

    Part of what I don't understand is that the process does not seem to be bidirectional. Try to open the word document from within access and it fails. However, if all else is the same, opening the word mail merge document directly works perfectly.

    nebbia

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Mail merge data source (2003)

    Apparently, the Word document has been set up with DDE as method to connect to the database; DDE "understands" parameter queries. Your code uses ODBC, this does not "understand" parameter queries. Hence, the difference in behavior.

  7. #7
    2 Star Lounger
    Join Date
    May 2005
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Mail merge data source (2003)

    Thank you for the explanation I elected to change the criteria as it proved easier than changing the code (I still had reference problems with the syntax)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •