Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Access Form to Open Word Doc and merge.... (Access 2003)

    OK....I've got a form that includes 1 combo box and 1 button. Upon clicking the button (cmdCreateSWPPPBook), I wish it to open MS Word, open a particular mail merge document (always the same one), merge a particular record into the document (determined by the combo box (Combo2) on the form), and view it in print preview. Here is the code for the onClick() of the button (which opens the document but doesn't merge the data source correctly, or at all for that matter).

    Private Sub cmdCreateSWPPPBook_Click()
    On Error GoTo Err_cmdCreateSWPPPBook_Click

    Dim oApp As Object

    Set oApp = CreateObject("Word.Application")
    oApp.Documents.Add "Krake's World of WondersSWPPP BookSWPPPBookMerge.doc"

    Set myMerge = ActiveDocument.MailMerge
    If myMerge.State = wdMainAndSourceAndHeader Or _
    myMerge.State = wdMainAndDataSource Then
    If ActiveDocument.MailMerge.DataSource.DataFields("Jo bName").Value _
    = Me.Combo2 Then
    With myMerge
    .Destination = wdPrintPreview
    .Execute
    End With
    End If
    End If

    If ActiveDocument.MailMerge.State = wdMainAndDataSource Then
    ActiveDocument.MailMerge.Execute
    End If

    oApp.Visible = True

    Exit_cmdCreateSWPPPBook_Click:
    Exit Sub
    Err_cmdCreateSWPPPBook_Click:
    MsgBox Err.Description
    Resume Exit_cmdCreateSWPPPBook_Click
    End Sub

    Not sure how to tackle this one, but any help, as always, is sincerely appreciated.

    Drake

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

    Re: Using Access Form to Open Word Doc and merge.... (Access 2003)

    You can't filter by including an If statement. You should set the QueryString for the mail merge:

    Private Sub cmdCreateSWPPPBook_Click()
    Dim oApp As Object
    Dim oDoc As Object
    On Error GoTo Err_cmdCreateSWPPPBook_Click

    Set oApp = CreateObject("Word.Application")
    Set oDoc = oApp.Documents.Add("Krake's World of WondersSWPPP BookSWPPPBookMerge.doc")

    With oDoc.MailMerge
    .DataSource.QueryString = "SELECT * FROM ... WHERE JobName=" & _
    Chr(34) & Me.Combo2 & Chr(34)
    .Execute
    End With

    oApp.Visible = True

    Exit_cmdCreateSWPPPBook_Click:
    Set oDoc = Nothing
    Set oApp = Nothing
    Exit Sub

    Err_cmdCreateSWPPPBook_Click:
    MsgBox Err.Description
    Resume Exit_cmdCreateSWPPPBook_Click
    End Sub

    Substitute ... with the name of the table or query.

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Access Form to Open Word Doc and merge.... (Access 2003)

    Hans, you are a genius sir. I've been going through 3 different books trying to find the correct syntax for that. Much appreciated, as always.

  4. #4
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Access Form to Open Word Doc and merge.... (Access 2003)

    Sorry...one last little curiosity. - I'm getting an 'Requested Object not Found' message when I run the code you've so beautifully constructed. I've tried several variants for the name of the query I want after FROM in the .QueryString =

    With oDoc.MailMerge
    .DataSource.QueryString = "SELECT * FROM ([Queries]![qrySWPPPBookData]) WHERE JobName=" & _
    Chr(34) & Me.Combo2 & Chr(34)
    .Execute
    End With

    I've also tried simply...."SELECT * FROM qrySWPPPBookData WHERE"......

    Both seem to produce the same message? Simple fix I'm sure. But I'm stumped for the moment.

    Thanks again!

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

    Re: Using Access Form to Open Word Doc and merge.... (Access 2003)

    We'd have to see the database and document to be able to help.

  6. #6
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Access Form to Open Word Doc and merge.... (Access 2003)

    Of course....

    I changed the name of the merge document to SWPPPBookMergeII.doc (It was too big too include the original in the .zip file) (The actual directory and file name in the code are correct).

    Thank you sir.

  7. #7
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Access Form to Open Word Doc and merge.... (Access 2003)

    Wow....I got it to work. The only problem with this way is that it leaves an original copy of SWPPPBookMerge.doc open along with the newly merged copy, which it defaultly names 'Letters Form1'

    Is there a work-around?

    Set oApp = CreateObject("Word.Application")
    Set oDoc = oApp.Documents.Open("Krake's World of WondersSWPPP BookSWPPPBookMerge.doc")

    With oDoc.MailMerge

    .OpenDataSource _
    Name:="Krake's World of WondersNOI Tracking" & _
    "NOITracking.mdb", _
    LinkToSource:=True, AddToRecentFiles:=False, _
    Connection:="DSN=MS Access Databases;", _
    SQLStatement:="SELECT * FROM [qrySWPPPBookData] WHERE JobName=" & _
    Chr(34) & Me.Combo2 & Chr(34)
    End With

    oDoc.MailMerge.Execute
    oApp.Visible = True

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

    Re: Using Access Form to Open Word Doc and merge.... (Access 2003)

    After the line

    oDoc.MailMerge.Execute

    insert a line

    oDoc.Close SaveChanges:=False

  9. #9
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Access Form to Open Word Doc and merge.... (Access 2003)

    Works Perfect!

    Thanks!

Posting Permissions

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