Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2016
    Posts
    9

    Coding to export data from single record to Word Bookmarks

    Hi everyone. I'm a relative newbie to Access coding and I have a bug that i'm hoping someone can help. I've adapted a tutorial that I've found online to come up with the code below.

    What I want it to do:
    I have a record open in a form. There is a button with the code below. I want it to open word, open specific document (same document everytime) fill in the bookmarks with data from the current record only. Save a copy with added file name extensions, and then close the word document.

    What is actually does: It performs perfectly, however, it doesn't matter what record I have on the screen, when I hit the button, it fills out the word file with the records from ID 1. I suspect it has something to do with the recordset, but I'm having trouble figuring it out. I'd appreciate any help. Thanks


    Public Sub Command908_Click()
    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("N:\Dropbox\Dropbox\BCNU\Labou r Relations\Templates\step3presentation-Access.docx")
    Set rs = CurrentDb.OpenRecordset("LRO Database")


    wDoc.Bookmarks("GFirstName").Range.Text = Nz(rs!GFirstName, "")
    wDoc.Bookmarks("GLastName").Range.Text = Nz(rs!GLastName, "")
    wDoc.Bookmarks("GPhone").Range.Text = Nz(rs!GHomePhone, "")
    wDoc.Bookmarks("Issue").Range.Text = Nz(rs!Issue, "")
    wDoc.Bookmarks("Primary").Range.Text = Nz(rs!Keyword, "")
    wDoc.Bookmarks("SLastName").Range.Text = Nz(rs!SLastName, "")
    wDoc.Bookmarks("SFirstName").Range.Text = Nz(rs!SFirstName, "")
    wDoc.Bookmarks("SPhone").Range.Text = Nz(rs!SPhone, "")
    wDoc.SaveAs2 "N:\Dropbox\Dropbox\BCNU\Labour Relations\Individual Grievances" & rs!GFirstName & rs!GLastName & "_Step3Prep.docx"



    wDoc.Close False
    wApp.Quit

    Set wDoc = Nothing
    Set wApp = Nothing
    Set rs = Nothing

    End Sub

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You've simply opened the recordset on the table, so you get the first record. You'd need an SQL statement with a criteria. Since the record is on the form, just refer to the controls on the form:

    wDoc.Bookmarks("GFirstName").Range.Text = Me.ControlName
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Join Date
    Nov 2016
    Posts
    9

    Gratitude

    Thanks greatly. As I said, I'm new and the piece that exscaped me was the recordsets contain all records and not just the record on the form where the button exists. I do intend to get through all the tutorials, I just got ahead of myself. Again, thanks.


    Quote Originally Posted by pbaldy View Post
    You've simply opened the recordset on the table, so you get the first record. You'd need an SQL statement with a criteria. Since the record is on the form, just refer to the controls on the form:

    wDoc.Bookmarks("GFirstName").Range.Text = Me.ControlName

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with Paul; just refer to the controls on the form.

    However, I wanted to comment on the code.

    "Primary" is a reserved word in Access and shouldn't be used as an object name.
    Spaces in object names is a very bad idea. ("LRO Database")
    And "Database" as part of a table name (or a query)???

    Code:
    Public Sub Command908_Click()
        Dim wApp As Word.Application
        Dim wDoc As Word.Document
        Dim rs As DAO.Recordset
        Dim sSQL As String
    
        Set wApp = New Word.Application
        Set wDoc = wApp.Documents.Open("N:\Dropbox\Dropbox\BCNU\Labou r Relations\Templates\step3presentation-Access.docx")
    
        sSQL = "SELECT GFirstName, GLastName, GPhone, Issue, Primary, SLastName, SFirstName, SPhone"  '<<  "Primary" is a reserved word
        sSQL = sSQL & " FROM [LRO Database]"
        sSQL = sSQL & " WHERE ID = " & Me.ID  ' "Me.ID" refers to a control  on the form
        Set rs = CurrentDb.OpenRecordset(sSQL)
    
        wDoc.Bookmarks("GFirstName").Range.Text = Nz(rs!GFirstName, "")
        wDoc.Bookmarks("GLastName").Range.Text = Nz(rs!GLastName, "")
        wDoc.Bookmarks("GPhone").Range.Text = Nz(rs!GHomePhone, "")
        wDoc.Bookmarks("Issue").Range.Text = Nz(rs!Issue, "")
        wDoc.Bookmarks("Primary").Range.Text = Nz(rs!Keyword, "")    
        wDoc.Bookmarks("SLastName").Range.Text = Nz(rs!SLastName, "")
        wDoc.Bookmarks("SFirstName").Range.Text = Nz(rs!SFirstName, "")
        wDoc.Bookmarks("SPhone").Range.Text = Nz(rs!SPhone, "")
        wDoc.SaveAs2 "N:\Dropbox\Dropbox\BCNU\Labour Relations\Individual Grievances" & rs!GFirstName & rs!GLastName & "_Step3Prep.docx"
    
        wDoc.Close False
        wApp.Quit
    
        'clean up & exit
        rs.Close
        Set rs = Nothing
    
        Set wDoc = Nothing
        Set wApp = Nothing
    
    End Sub

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Fatmonkeymedia View Post
    Thanks greatly. As I said, I'm new and the piece that exscaped me was the recordsets contain all records and not just the record on the form where the button exists. I do intend to get through all the tutorials, I just got ahead of myself. Again, thanks.
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Join Date
    Nov 2016
    Posts
    9
    Thanks for the input, I've renamed everything now to remove spaces. Was causing all sorts of headaches!
    Appreciate it!
    Mycal.

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

Similar Threads

  1. MS Word template with bookmarks
    By jonnyh in forum Import/Export Data
    Replies: 2
    Last Post: 09-07-2016, 07:42 AM
  2. Replies: 11
    Last Post: 01-26-2012, 09:28 AM
  3. Access Form to Word Bookmarks
    By markod in forum Forms
    Replies: 5
    Last Post: 12-27-2010, 05:18 PM
  4. Export single records to new Word Document
    By karmaimages in forum Import/Export Data
    Replies: 0
    Last Post: 11-19-2009, 03:37 PM
  5. font of word bookmarks
    By emilylu3 in forum Programming
    Replies: 1
    Last Post: 03-09-2006, 12:07 PM

Tags for this Thread

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