dcmennealy
asked on
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
UNC = J:\SDT\APTT\0ST1234OTFO.pd
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.
Project_ID = 0ST1234
Filename of Document = 0ST1234OTFO.pdf
UNC = J:\SDT\APTT\0ST1234OTFO.pd
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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!
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!
ASKER
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.
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:
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.