Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Location
    Cockeysville, Maryland, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    Good afternoon, all.

    This would be my first post at Woody's, and I look forward to learning more than I already have by just watching. Now it's my turn to ask questions.

    I'm trying to figure out how (in code) to export current record information from a form into a Word template I've created. Actually, I'm able to do that, but I want to be able to smarten it up a bit.

    I'm currently using bookmarks and using the below method:
    <hr>.ActiveDocument.Bookmarks("COMPANY").Select
    .Selection.Text = (CStr(Forms!frmContacts!COMPANY))<hr>
    It works fine, but I would like to be able to replace words multiple times (ie. a "FirstName" field that will be used throughout the letter). Am I going to have to stray away from using bookmarks to do this? If so, how would you recommend going about accomplishing this?

    ALSO, I'm wondering if it's possible (I assume it is) to have the code in that area remove lines of text in the Word template if a certain bookmark, or whatever else should be used, isn't there. For instance, if a letter is to go to a person without a place of employment, there would be no "Company" line underneath his/her name in the letter; however, it should be in the template, as it's very rare that a record won't have a value for each field.

    I've looked through the forums and I've found quite a few references to standard bookmarking, but nothing like this. If I'm missing it, please point me to it.

    Thanks very much in advance for any and all help I can get!

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

    Re: Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    Welcome to Woody's Lounge (this time officially)!

    You can refer to a bookmark in other locations in a document by inserting a cross reference. Say that you want to refer to the Company bookmark:
    - Position the insertion point where you want the reference.
    - Select Insert | Reference | Cross Reference...
    - Select Bookmark as Reference Type.
    - Select Company in the list of bookmarks.
    - If you don't want the cross reference to act as a hyperlink, clear the corresponding check box.
    - Click OK.

    The code you have will replace the bookmark. To keep the bookmark, change it like this:
    <code>
    Dim rng As Word.Range
    Set rng = .ActiveDocument.Bookmarks("Company ").Range
    rng.Text = CStr(Forms!frmContacts!Company)
    .ActiveDocument.Bookmarks.Add Name:="Company ", Range:=rng
    </code>
    The cross reference will not be updated automatically. When you're done setting the bookmarks and cross references, update them all:
    <code>
    .ActiveDocument.Fields.Update
    </code>
    You could mark the text to be deleted with a bookmark too, and delete the range of the bookmark if necessary:
    <code>
    If IsNull(Forms!frmContacts!Company) Then
    .ActiveDocument.Bookmarks("CompanyLine").Range.Del ete
    End If
    </code>
    CompanyLine is the bookmark that covers the entire line or paragraph to be deleted.

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

    Re: Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    Bookmark names cannot contain spaces, so "FirstName " is not valid.
    Without the trailing spaces, the code should work.
    Does this also solve the delete problem? If not, please provide more info about the code you're using.

  4. #4
    New Lounger
    Join Date
    Nov 2006
    Location
    Cockeysville, Maryland, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    <P ID="edit" class=small>(Edited by Cake on 27-Nov-06 16:28. )</P>Thank you for the official welcome and quick reply, Hans.

    I just edited this post, as the code you provided worked beautifully once I just attempted it.

    I'm am still getting the error I received before, however, when there was no Company value in the record, despite tossing in your If IsNull code. It's as if Access isn't even checking for the If statement, and it's proceeding directly to the Bookmark insertion. What do you make of this?

  5. #5
    New Lounger
    Join Date
    Nov 2006
    Location
    Cockeysville, Maryland, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    The code, as I have it now, is as follows. I'm getting an "Invalid use of Null" error.

    <code> Set rng = .ActiveDocument.Bookmarks("Company").Range
    rng.Text = CStr(Forms!frmContacts!CompanyName)
    .ActiveDocument.Bookmarks.Add Name:="Company", Range:=rng
    If IsNull(Forms!frmContacts!CompanyName) Then
    .ActiveDocument.Bookmarks("CompanyLine").Range.Del ete
    End If
    .ActiveDocument.Fields.Update</code>

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

    Re: Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    If CompanyName is Null, the CStr function will fail. Do you want to set the Company bookmark at all if CompanyName is Null? If so, use

    rng.Text = Nz(Forms!frmContacts!CompanyName, "")

    The Nz function replaces the first argument with the second one if the first one is Null.

  7. #7
    New Lounger
    Join Date
    Nov 2006
    Location
    Cockeysville, Maryland, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    You rule. It works perfectly once I throw it in the correct order:

    <code> Set rng = .ActiveDocument.Bookmarks("Company").Range
    rng.Text = Nz(Forms!frmContacts!CompanyName, "")
    .ActiveDocument.Bookmarks.Add Name:="Company", Range:=rng
    If IsNull(Forms!frmContacts!CompanyName) Then
    .ActiveDocument.Bookmarks("CompanyLine").Range.Del ete
    End If</code>

    Thanks very much for your help with that!

  8. #8
    New Lounger
    Join Date
    Nov 2006
    Location
    Cockeysville, Maryland, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    Hans:

    One last question.

    My ZipCode value is actually a two-columned textbox. The first column is a ZipCode ID that I've assigned and have it related to another table that pulls the city and state. The second column is the actual zipcode. What must change in the below code to specify the column?

    <code>rng.Text = CStr(Forms!frmContacts!ZipCode)</code>

    If I attempt to add ".Column(2)" after ZipCode, the CStr function fails. If I change it to an Nz fuction, it returns a Null value.

    Thanks very much for ALL of your help,
    Brandon

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

    Re: Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    The column index of a list box or combo box is zero-based, so Column(0) is the first column, Column(1) is the second column etc. So try

    rng.Text = Nz(Forms!frmContacts!ZipCode.Column(1), "")

  10. #10
    New Lounger
    Join Date
    Nov 2006
    Location
    Cockeysville, Maryland, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    How about that: you're right again.

    Thanks a bunch for all of the help! I'll have more questions later tonight or tomorrow regarding doing this multiple times via a multiselect listbox! Until then.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    There's another way to update a Word document that I have found more reliable and powerful. It's using Document Properties. This makes it very easy to update in multiple places, and you don't need bookmarks. (I find users often accidentally delete bookmarks, because you can't see them by default.)

    In the document, create a custom document property with File, Properties, Custom.
    Wherever you want to use the custom properties value, Insert, Field, Document Information, DocProperty "custom property name". You can use the one property in multiple places.
    In the code, update the document property with
    doc.CustomDocumentProperties.Item(sDocumentPropert yName).Value = sNewValue
    Viola!

    Peter

  12. #12
    New Lounger
    Join Date
    Nov 2006
    Location
    Cockeysville, Maryland, USA
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access - Word Automation (Duplicates and Removals) (XP 2003 (11.0))

    Thanks for the tip, Peter. I'll definitely give it a go and see which I tend to prefer.

    Ideas rule.

Posting Permissions

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