MS Access generate hyperlinks based on value of another field

I have a database that has 1300 rows and I need to create hyperlinks for the docuements associated with each record.  The record has a project_ID which is unique and the document filename is equal to the project_ID plus a string of letters.  They are all pdf files in particular folder on a shared drive.  Filename and path examples are as follows:

Project_ID = 0ST1234
Filename of Document = 0ST1234OTFO.pdf

I want to know how I could generate hyperlinks for all these records in which the project_ID is used in the path.

I would like to display the the link as the filename.  Also, I would like to know how I can quickly update this in case the files are moved to another folder.
Who is Participating?
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I have created this example that should be helpful:

Document Links 2
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:


Open in new window

Ths is not a UNC path but a Mapped drive path.

A UNC path woudl eb something like:


Open in new window

You could calculate the path like this:
strPath =   "J:\SDT\APTT\"  & [Project_ID] &   " OTFO.pdf"   

Open in new window

I find it best to store the path to PDF as a text string an use the ShellExec to open it. The FollowHyperlink method has issues with the Acrobat Reader.

dcmennealyAuthor Commented:
Thanks.  I downloaded the sample and reviewing it now. I will respond as soon as I finish reviewing.  Thanks for correcting me about UNC verses path to shared drive.
dcmennealyAuthor Commented:
I did review your database and did give me information I needed but did it in a different way.

1. I created a table with the following info:
Link_ID - autonumber
chrPathCommon - J:\SDT\APTT\
chrProjectNumber - 0ST1234
chrDocType - OTFO (there are other doc types)
chrPDF - .pdf

2.  Then I created a query that created the links by concatenating the info

3. Then created an update query to place the links with the appropriate record.

4. Finally I created a button that would run this information after a file has been added into the folder and inputted into the table.

This work out very well thank you!
dcmennealyAuthor Commented:
I gathered what the person did but I am a novice and needed more direction.  However, I did figure it out and I got what I wanted.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.