Results 1 to 13 of 13
  1. #1
    Chaudhary is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2019
    Posts
    5

    Why datasource locked to execute mailmerge by vba while i press save

    Hello.

    I have copyed Mailmerge Code from other website and it works properly until I press Ctrl + S or Save Option.
    After pressing Ctrl + S or Save Option, error msg popup as "Error has occured: The database has been placed in a state by user 'Admin' on Machine 'DESKTOP-RS2VFC8' that prevents it from being opened or locked."

    Why this msg popup and how to overcome from this error.

    I also want some other help.

    I don't want to save New Merge document. It should be opened but close maindocument without saving.

    I have uploded sample DB


    MailMergeTest.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Tried your code and don't get error with the ALL option. Word doc is created. Issue appears to be with the RANGE option.

    Why use Word merge instead of Access report? Or build email with data embedded?

    I have never used Word merge with Access. Access alone accommodates my requirements for data output.

    Should include a conditional so code won't run if no selection in combobox to prevent run-time error.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Chaudhary is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2019
    Posts
    5
    Thank you for your reply. Access report can fulfill all requirement for data output but in my case it is difficulty to design report each time when output needed in new formate. Other thing, I don’t want to allow other to edit my db.

    I think mailmerge is perfect solution to shift new formate easlly. But above described problem is my main problem.

    Yes, selection in combobox is necessary. With combobox i select formate to get output.

    Is there any solution to overcome from this problem?

    Thank you.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why would report format change?

    If there is no selection in combobox and user runs code, it will error. Should prevent that.

    Combobox DefaultValue property needs to be: "MailMergeTest.docx" - with quote marks, otherwise Access changes to: [MailMergeTest].[docx] and combobox will not populate value.

    Revised code:
    Code:
    Private Sub MailMerge_Click()
    On Error GoTo DoMailMerge_Err
        Me.Requery
        
        Dim oWord As Object
        Dim oWdoc As Object
        Dim wdInputName As String
        Dim wdOutputName As String
        Dim outFileName As String
        
        If Me.GTEE_FORMATE & "" <> "" Then
            ' Set Template Path
            '------------------------------------------------
            wdInputName = CurrentProject.Path & "\" & Me.GTEE_FORMATE.Value
            
            ' Create unique save filename with minutes and seconds to prevent overwrite
            '------------------------------------------------
            outFileName = "MailMergeFile_" & Format(Now(), "yyyymmddmms")
            
            ' Output File Path w/outFileName
            '------------------------------------------------
            wdOutputName = CurrentProject.Path & "\" & outFileName
            
            Set oWord = CreateObject("Word.Application")
            Set oWdoc = oWord.Documents.Open(wdInputName)
            
            ' Start mail merge
            '------------------------------------------------
            With oWdoc.MailMerge
              .MainDocumentType = 0 'wdFormLetters
              .OpenDataSource _
                Name:=CurrentProject.FullName, ReadOnly:=True, _
                AddToRecentFiles:=False, _
                LinkToSource:=True, _
                Connection:="QUERY MailMergeQueryGtTrayTbl", _
                SQLStatement:="SELECT * FROM [MailMergeQueryGtTrayTbl] "
              .Destination = 0 'wdSendToNewDocument
              With .DataSource
                Select Case MailMergeOption.Value
                Case Is = 3
                  .FirstRecord = 1
                  .LastRecord = .RecordCount ' replace 2o to .RecordCount to get record count
                Case Is = 2
                  .FirstRecord = InputBox("Enter the First Record # to merge", , 1)
                  .LastRecord = InputBox("Enter the Last Record # to merge", , .RecordCount) ' replace 2o to .RecordCount to get record count
                End Select
              End With
              .Execute Pause:=False
            End With
            
            ' Save file as PDF
            ' Uncomment the line below and comment out
            ' the line below "Save file as Word Document"
            '------------------------------------------------
            'oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17
            
            ' Save file as Word Document
            '------------------------------------------------
            oWord.ActiveDocument.SaveAs wdOutputName & ".docx", 16
            
            ' Quit Word to Save Memory
            '------------------------------------------------
            oWord.Quit savechanges:=False
    
        End If
        
    DoMailMerge_Exit:
        ' Clean up memory
        '------------------------------------------------
        If Not oWdoc Is Nothing Then Set oWdoc = Nothing
        If Not oWord Is Nothing Then Set oWord = Nothing
        Exit Sub
    
    DoMailMerge_Err:
        Select Case MsgBox(Error$)
        End Select
        Resume DoMailMerge_Exit
    End Sub
    I was able to output several Word docs switching between ALL and RANGE without issue. However, if code execution errors, an instance of Word is left running in Windows processes. This can cause issue because the Word file is in use. I suppose this should be dealt with in error handler or move the Quit line into Exit block. If you stop code execution in debugger then must manually end the Word process in Windows Task Manager.
    Last edited by June7; 07-14-2019 at 04:31 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Instead of using an Access table or query as the mail merge source it is better to export it to a text file (csv, pipe-delimited, etc.) and use that as the mail merge source. Doing that will prevent your problem to happen and is a safer option too. I usually export the query to the same folder as the front-end (currentproject.path) and I delete the file each time I do a new mail-merge and also on the unload event of the switchboard form (on closing the Access app).

    Cheers,
    Vlad

  6. #6
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Have you tried Albert Kallal's super easy word merge? It pretty easy to integrate into any project.
    http://www.kallal.ca/msaccess/msaccess.html

  7. #7
    Chaudhary is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2019
    Posts
    5
    Thank You all for help.

    My main proble started after pressing "Ctrl + S" or clicking "Save" Option. It locked my data source to execute Mail merge. Error Msg popup as
    "Error has occured: The database has been placed in a state by user 'Admin' on Machine 'DESKTOP-RS2VFC8' that prevents it from being opened or locked."
    So, Please help me to solve this problem. Please give me some ways so that my datasource should not locked to execute Mail merge after pressing "Ctrl + S" or Clicking "Save" Option.

    Thank you in advance.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Where do you click "Save" - Access Quick Access Toolbar or File > Save ? Okay, now I get this. Why would user need to Save db anyway? I would disable ribbon and prevent user saving db. However, that doesn't resolve current issue and I really have no idea how.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    http://www.kallal.ca/wordmerge/page2.html

    See the link above and scroll down to "Dont let word attach to your running MDB file"

  10. #10
    Chaudhary is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2019
    Posts
    5
    I can remove toolbar but I don't have any idea to disable short cut Key "Ctrl + S". Is there any way to disable "Ctrl + S".

    I think my problem will be solved if short cut key "Ctrl +S" can disable.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you consider other suggestion presented here?

    I use an AutoKeys macro to disable short cut key combinations. https://www.accessforums.net/showthread.php?t=51656

    Opening db with shift key bypass will prevent these option settings. That's how I open db for editing.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I get an automation error when I run your code and a copy of word remains open in the background.

    See the sample in post 7 here...https://www.accessforums.net/showthr...882#post431882

  13. #13
    Chaudhary is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2019
    Posts
    5
    Hello to all.

    My main problem solved by disabling Ctrl + S key.

    I have also posted another question in post #1.
    I don't want to save New Merge document. It should be opened but close maindocument without saving.
    I want to merge doc leave open but my temple should be closed without changing any thing. But with this code, Merged document saved having name (date and time) and it closed.

    So please some one help me by editing this code so that merged doc should not closed but templed document saved without any change.

    Thank You.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Execute SP from Access as datasource
    By jaryszek in forum Access
    Replies: 4
    Last Post: 01-18-2018, 03:32 AM
  2. Replies: 7
    Last Post: 09-11-2014, 12:26 PM
  3. Replies: 2
    Last Post: 10-03-2012, 02:58 AM
  4. Replies: 3
    Last Post: 08-12-2012, 11:53 PM
  5. Replies: 8
    Last Post: 01-10-2011, 08:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums