Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Merge to Word - Module and/or Command Button Code (Access 97 )

    This seems to be my week for problems:
    My database is housed in C:My DocumentsExpressions_EPICexpressions.mdb

    I have a Merge Command Button on frmMerges that is supposed to take records from qry4clientmergetotable (long name but necessary for identification at this stage). It then is to open Word, open a document named clientmerge.doc (housed in the same directory as the database) and run the merge.

    This is the routine for the merge command button:

    Private Sub cmdrunmergequery_Click()
    On Error GoTo Err_cmdrunmergequery_Click

    Dim stDocName As String

    stDocName = "qry4clientMergeToTable"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

    Exit_cmdrunmergequery_Click:
    Exit Sub

    Err_cmdrunmergequery_Click:
    MsgBox Err.Description
    Resume Exit_cmdrunmergequery_Click

    End Sub


    Here is the MergeIt module

    Option Compare Database
    'these constants need to be declared outside the MergeIt sub otherwise they will only
    'be available AFTER the sub has run
    Public Const strFilePath As String = "C:My DocumentsExpressions_EPICexpressions.mdb"
    Public Const strDBName As String = "expressions.mdb"
    Public Sub MergeIt(strSQL As String, strDoc As String)
    Dim appWord As Word.Application
    Dim docWord As Word.Document
    Screen.MousePointer = 11
    '/Get link to word
    On Error Resume Next
    Set appWord = GetObject(, "Word.Application")

    If Err.Number <> 0 Then
    '/Open if not currently running
    Set appWord = CreateObject("Word.Application")
    Err.Clear
    appWord.Visible = True
    End If

    On Error GoTo err_handle

    '/Open Merge document
    Set docWord = appWord.Documents.Open(strFilePath & strDoc)

    With docWord.MailMerge
    .MainDocumentType = wdFormLetters
    .SuppressBlankLines = True
    .OpenDataSource Name:=strFilePath & strDBName, _
    LinkToSource:=True, _
    SQLStatement:=strSQL
    ' Execute the mail merge.
    .Execute

    End With
    Screen.MousePointer = 0
    appWord.Activate
    'docWord.Activate
    leave:
    Set appWord = Nothing
    Set docWord = Nothing
    Exit Sub
    err_handle:
    Screen.MousePointer = 0
    MsgBox Err.Description
    GoTo leave
    End Sub



    This file could not be found
    Try one or more of the following:

    Check the spelling of the name of the document
    Try a different file name
    C:My Documents

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Merge to Word - Module and/or Command Button Code (Access 97 )

    I'm confused. You declare strFilePath like this:

    Public Const strFilePath As String = "C:My DocumentsExpressions_EPICexpressions.mdb"

    Then you use the constant in code like this:

    Set docWord = appWord.Documents.Open(strFilePath & strDoc)

    That certainly looks to me like it would return

    C:My DocumentsExpressions_EPICexpressions.mdbclientmerg e.doc

    Even with a backslash, that would be meaningless because expressions.mdb is NOT the name of a folder. Are you sure you included the right constant declaration? Shouldn't strFilePath be something like this:

    Public Const strFilePath As String = "C:My DocumentsExpressions_EPIC"
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge to Word - Module and/or Command Button Code (Access 97 )

    Thanks, Charlotte. Actually I had done that after sending the message and now all I had to do is add a back-slash. However, although this now does work I keep also getting a pesky message that says

    "The database you are trying to open or use is already in use or you do not have permission to open it exclusively. When you convert a database or the first time you open an earlier version another user can't have the database open. Try one of the following: Request other users close the database, or Using the version in which the database was written have your workgroup administrator grant you permission to open the database exclusively or Multiple users can share the database after you convert it or after you open it the first time"

    This has never happened to me before! I am running Access 97 on this computer along with Office 2000 although I have disabled Access 2000 while working on this project. However, I think the error message may be attributed to Access 2000 and wonder if you know how I can fix something like this.

    Thanks again

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Merge to Word - Module and/or Command Button Code (Access 97 )

    Definitely Access 2000 at work/play! Because you have Access 2000, it has decided that it should be used when you try to run a mail merge. Most often this happens if you have Access security active on your database, so it needs to do another logon prompt (it thinks). Another thing that will confuse Access is if you change your application title - then your system thinks Access isn't already running and tries to start it again. I've seen cases where four or five instances of Access end up opend just because you wanted to run a simple mail merge. It's been a problem since Access 95, and for some reason MS hasn't been able to make it go away. I'll take a further look at your code and see if there is something that triggers it. In the meantime, if you actually convert a copy of your DB to 2000 and use that, it should allow the merge to proceed.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge to Word - Module and/or Command Button Code (Access 97 )

    Thanks, but I cannot convert this to 2000 as the client that uses it only has Access 97. I will test all of this on their computer this week as I think the problem may disappear once it is off my computer. In the past when I disabled 2000, I was OK but this time it has decided to give me a hard time so unless someone knows of a work-around I will live with it.

    Thanks again

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Merge to Word - Module and/or Command Button Code (Access 97 )

    Sorry - I didn't mean to suggest that you permanently convert it. I was suggesting that you convert it (which makes a copy) so you coud do testing. FYI, I try not to run two versions of Access on any PC. I know that isn't always an option, but our experience suggests it nearly always causes problems of one sort or another. As an example, if you run Access 97 and then start Access 2000, especially if you have security active, Access 2000 thinks the install has been corrupted, and does a repair which resets the security pointers.
    Wendell

Posting Permissions

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