Display Specific Word Document From MS Access When Certain Form Field Populated

In my Access database application, I populate a reason code and its related description from a combo box.  I would like to open/display a corresponding Word document with an expanded description and possible casue(s) for the reason selected.  

Is there a way, by hyperlinking or via VBA, to display the additional information when the code is selected?
castlerjSenior AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
In this example, the code refers to a form field.  the reference can be to any registered file type.  So if the document file type is .docx, word will open, if it is .xlsx, Excel will open, if it is a URL a web page will open, etc.

Application.FollowHyperlink Me.txtFullDocName, , True

Open in new window

castlerjSenior AnalystAuthor Commented:
Below is the code I use to populate the reason code description; I assume that I can add your line after "...Column(1)" on another line.  I am wondering how to attach the specific code to the Word document.  Should I create another column within my reason code table with the hyperlink for it?

Sub DEB_REAS_tx_1_AfterUpdate()
Me![DEB_REAS_CD_1] = Me![DEB_REAS_TX_1].Column(1)
End Sub
Sub DEB_REAS_tx_2_AfterUpdate()
  Me![DEB_REAS_CD_2] = Me![DEB_REAS_TX_2].Column(1)
End Sub
Sub DEB_REAS_tx_3_AfterUpdate()
  Me![DEB_REAS_CD_3] = Me![DEB_REAS_TX_3].Column(1)
End Sub
Sub DEB_REAS_tx_4_AfterUpdate()
  Me![DEB_REAS_CD_4] = Me![DEB_REAS_TX_4].Column(1)
End Sub
GrahamSkanRetiredCommented:
You would need to identify the open document programatically. The best way to do that would be to open it programatically. You wouldn't have to create a hyperlink. You just use the document path.
Here is the sort of code that you would need. It uses early binding so needs a reference to the Microsoft Word Object Library. You would also have to tweak it to get the right document name, new text and where it is to be put on the document.
Sub UpDateDoc()
    Dim strDocName As String
    Dim doc As Word.Document
    Dim bNewInstance As Boolean
    Dim objWord As Word.Application
    
    strDocName = Me![DEB_REAS_CD_2].DocName
    
    'avoid creating multiple instances of the Word application
    On Error Resume Next 'suppress error checking for the next instruction
    Set objWord = GetObject(, "Word.Application")
    On Error GoTo 0 'resume error checking
    
    If objWord Is Nothing Then
        'no instance, so make a new one
        Set objWord = CreateObject("Word.Application")
        bNewInstance = True
    End If
    objWord.Visible = True
    
    Set doc = objWord.Documents.Open(strDocName)
    doc.Bookmarks("\EndOfDoc").Range.Text = Me![DEB_REAS_CD_3].NewText
    doc.Close wdSaveChanges
    If bNewInstance Then
        objWord.Quit
    End If
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

castlerjSenior AnalystAuthor Commented:
A couple of question. (1) your code has  "strDocName = Me![DEB_REAS_CD_2].DocName" and " doc.Bookmarks("\EndOfDoc").Range.Text = Me![DEB_REAS_CD_3].NewText" - is the REAS_CD suppose to be different, (2) are you suggesting to duplicate the code in the 4 Subs and (3) can I reference the Word doc name in the same table as the reason code, i.g., Me![DEB_REAS_CD_2].Altered
GrahamSkanRetiredCommented:
I don't know your application, so I don't know where you get the document name from, nor do I know what data you want to copy on to the document.

You need lines like:
strDocName = something
doc.somerange.text = somethingelse
castlerjSenior AnalystAuthor Commented:
Okay, I will give it a try...just a little confused on the different codes (CD_2 and CD_3) that you used; I thought that should be the same.
GrahamSkanRetiredCommented:
Yes. I should have made that clearer.
PatHartmanCommented:
The FollowHyperlink method uses a text string with the complete path.  It does NOT use a HyperLink data type.

If the document you want to open is dependent on the record,  then you need some way of identifying how they match up.
Sub DEB_REAS_tx_1_AfterUpdate()
 Me![DEB_REAS_CD_1] = Me![DEB_REAS_TX_1].Column(1)
  Application.FollowHyperlink "C:\yourpath\Doc1.doc", , True
 End Sub
 Sub DEB_REAS_tx_2_AfterUpdate()
   Me![DEB_REAS_CD_2] = Me![DEB_REAS_TX_2].Column(1)
  Application.FollowHyperlink "C:\yourpath\Doc2.doc", , True
 End Sub
 Sub DEB_REAS_tx_3_AfterUpdate()
   Me![DEB_REAS_CD_3] = Me![DEB_REAS_TX_3].Column(1)
   Application.FollowHyperlink "C:\yourpath\Doc3.doc", , True
 End Sub
 Sub DEB_REAS_tx_4_AfterUpdate()
   Me![DEB_REAS_CD_4] = Me![DEB_REAS_TX_4].Column(1)
   Application.FollowHyperlink "C:\yourpath\Doc4.doc", , True
 End Sub 

Open in new window

Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.