Results 1 to 6 of 6

Freezing code

  1. #1
    stevedk is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    2

    Freezing code

    Hello

    I wondered if someone might be able to help me. My understanding of VB is nil so please bear with me if I make an error.

    I have an Access database that I have set-up to help me record invoices and have written in VBA to insert values from a query into Bookmarks of a Word document (SDK-template.docx).

    The code I'm using is from a Youtube video (https://www.youtube.com/watch?v=JilB511V3AU), but I don't really understand what any of it means. Here is the end code:



    Code:
    Option Compare DatabaseOption Explicit
    
    
    Public Sub ExportInvoicesToWord()
        
        Dim wApp As Word.Application
        Dim wDoc As Word.Document
        Dim rs As DAO.Recordset
            
        Set wApp = New Word.Application
        Set wDoc = wApp.Documents.Open("C:\Users\steve\Documents\Invoices\SDK-template.docx")
        Set rs = CurrentDb.OpenRecordset("004_Export query")
        
        If Not rs.EOF Then rs.MoveFirst
        
        Do Until rs.EOF
            wDoc.Bookmarks("Invoice_date").Range.Text = Nz(rs![Invoice date], "")
            wDoc.SaveAs2 "C:\Users\steve\Documents\Invoices\" & rs!Invoice & ".docx"
            
            wDoc.Bookmarks("Invoice_date").Range.Delete wdCharacter, Len(Nz(rs![Invoice date], ""))
            
            rs.MoveNext
            
        Loop
        
        wDoc.Close False
        wApp.Quit
        
        Set wDoc = Nothing
        Set wApp = Nothing
        Set rs = Nothing
        
    End Sub
    I have a Bookmark in Word called Invoice_date and a corresponding column in my query called Invoice date.
    I have another column in the query called Invoice which is the invoice's reference and I'm trying to save the Word document as this value.
    The query that I'm trying to get values from is called 004_Export query.

    When I run the code it just freezes, and I have to crash out of Access and no files are saved in the specified folder. Is there anything anyone can spot in the above code that could be causing this? Please let me know if you need more info.

    Many thanks in advance

    Steve

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,352
    Why using mail merge and not just Access report?

    Strongly advise not to use spaces nor punctuation/special characters in naming convention. Also best not to begin names with number.

    However, don't see anything that would cause failure.

    If you want to provide files for analysis, follow instructions at bottom of my post.
    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
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    357
    Hmm, well can you add break lines and then tell us which line it freezes on.
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  4. #4
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    390
    Another easy alternative is Albert Kallal's Super Easy Word Merge.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    Would you post both the Access dB and the Word template? Only need 3 or 4 records - change any sensitive data.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    stevedk is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    2
    I've change the column names in my tables to remove spaces and this seems to have resolved it.

    Thank you!

    S

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

Similar Threads

  1. Keep UI From Freezing
    By jo15765 in forum Programming
    Replies: 6
    Last Post: 07-22-2019, 03:05 PM
  2. Scroll Detail While Freezing Header
    By EddieN1 in forum Forms
    Replies: 2
    Last Post: 02-03-2013, 08:34 PM
  3. Freezing Access Database
    By snoopy in forum Programming
    Replies: 3
    Last Post: 10-09-2012, 09:09 PM
  4. Replies: 3
    Last Post: 06-05-2012, 01:47 PM
  5. The Problem: Form Keeps Freezing
    By BuzzBamm101 in forum Forms
    Replies: 4
    Last Post: 09-15-2011, 12:07 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
  •  
Tech Forums: Microsoft Office Forums