Problems with A Hyperlink Control in a Form

[LAD]

Using Access 2003 on Windows 2000.
My Skill Level: Med Low (Some VBA, okay with Access)
Form: Single View - based on Query of single Table to sort by field.

Application: Dealers email 'contract packages' that include as attachments: a contract (PDF or TIF), a pricing tool (Excel), a companion document (Word). Clerks detach these files into a shared drive file on our intranet.

I'm building an 'contract package management' application that needs to open the different documents for viewing only from within an Access Form. I use a multi-tabbed form. The user will capture information on the first tab, then on the following tabs I'd like the user to be able to:

- easily add a link (in a text box) to the appropriate "pdf, doc, xls" as required for each record.
- open the file using the appropriate application for viewing by clicking on the Hyperlink.
The clerks are checking the documents and tracking errors in the form.

I have added a Hyperlink field in a table (ContractPackages) for each of the documents (Contract, Pricer, CompanionDoc).

I added Controls (same name as fields I.e Contract Pricer, CompanionDoc) to the Form that allows me to insert a Hyperlink to the file.

I have control over naming conventions for the files and paths of the documents. There are no blanks in the filename or path.

All Hyperlinks that I insert into the Control do not work.

The Hyperlink works immediately after inserting it into the control using the Insert Hyperlink dialog box. I can click on it and it will open Acrobat (for example) and then the file that I'm pointing to. However, if I remove focus from the control by tabbing or clicking to another control on the form, the hyperlink will not work if I go back to the control and click on the hyperlink. The arrow changes to a 'hand' when hovering, but if, for example the control contains:

..\..\..\..\..\Test_Contracts\Contract.pdf

and I click on it, the control will then display:

..\..\..\..\..\Test_Contracts\Contract.pdf#../../../../../Test_Contracts/Contract.pdf#

and nothing happens.

The Hyperlinks work when opening the table or the query and clicking on them directly. It appears that the problem lies with the Form.

I have tried:

Changing the table field type from 'Hyperlink' to 'Text'.
Changing the form control property from Hyperlink 'Yes' to 'No'
All combinations of the above two changes.

I have updated MS Office to SP3.
I have turned Unicode compression on and off in the table field.

I have dowloaded Access DB samples of Hyperlinks in forms from tech sites and compared all of the Property Settings for the field and the control and have set mine accordingly. (The test db links work fine on my computer - both with linking to local files on my drive and to files located on the Intranet shared drive).

I have researched this on many tech sites, including here, and have found no definitive answers. I would prefer not to have to write a bunch of code to use File Open dialogs when Access provides what should be a simple solution (ha!) through the use of a Hyperlink.

Any suggestions as to what might be causing this problem?

Larry

[MMcCarthy]

Hi Larry

The data type of the field should be hyperlink and the full path to the file should be stored. On the form the Is hyperlink property should be set to yes. That should be working.

[rdemarco]

HI Larry

I had experienced the same problem and found that to get the hyperlink working all the time, I had to include the description part of the link. You simply do this by adding the name/description before the actual path/filename, and separate the two parts with a hash (#) symbol (no spaces). Eg:
MyLinkName#C:\subfolder\myfile.pdf

Hope this helps

Regards
Rob

[truthlover]

I'm having a similar problem (also using Access 2003) but what's happening is I cant get the hyperlink to open a local file. I keep getting a message saying the file name (preceeded by http://) cannot be found.

How do i get it to open a local file??

Thanks!

BTW my VBA experience is next to nothing, so please be patient with your explaination :)

HI Larry

I had experienced the same problem and found that to get the hyperlink working all the time, I had to include the description part of the link. You simply do this by adding the name/description before the actual path/filename, and separate the two parts with a hash (#) symbol (no spaces). Eg:
MyLinkName#C:\subfolder\myfile.pdf

Hope this helps

Regards
Rob

[ADezii]

Using Access 2003 on Windows 2000.
My Skill Level: Med Low (Some VBA, okay with Access)
Form: Single View - based on Query of single Table to sort by field.

Application: Dealers email 'contract packages' that include as attachments: a contract (PDF or TIF), a pricing tool (Excel), a companion document (Word). Clerks detach these files into a shared drive file on our intranet.

I'm building an 'contract package management' application that needs to open the different documents for viewing only from within an Access Form. I use a multi-tabbed form. The user will capture information on the first tab, then on the following tabs I'd like the user to be able to:

- easily add a link (in a text box) to the appropriate "pdf, doc, xls" as required for each record.
- open the file using the appropriate application for viewing by clicking on the Hyperlink.
The clerks are checking the documents and tracking errors in the form.

I have added a Hyperlink field in a table (ContractPackages) for each of the documents (Contract, Pricer, CompanionDoc).

I added Controls (same name as fields I.e Contract Pricer, CompanionDoc) to the Form that allows me to insert a Hyperlink to the file.

I have control over naming conventions for the files and paths of the documents. There are no blanks in the filename or path.

All Hyperlinks that I insert into the Control do not work.

The Hyperlink works immediately after inserting it into the control using the Insert Hyperlink dialog box. I can click on it and it will open Acrobat (for example) and then the file that I'm pointing to. However, if I remove focus from the control by tabbing or clicking to another control on the form, the hyperlink will not work if I go back to the control and click on the hyperlink. The arrow changes to a 'hand' when hovering, but if, for example the control contains:

..\..\..\..\..\Test_Contracts\Contract.pdf

and I click on it, the control will then display:

..\..\..\..\..\Test_Contracts\Contract.pdf#../../../../../Test_Contracts/Contract.pdf#

and nothing happens.

The Hyperlinks work when opening the table or the query and clicking on them directly. It appears that the problem lies with the Form.

I have tried:

Changing the table field type from 'Hyperlink' to 'Text'.
Changing the form control property from Hyperlink 'Yes' to 'No'
All combinations of the above two changes.

I have updated MS Office to SP3.
I have turned Unicode compression on and off in the table field.

I have dowloaded Access DB samples of Hyperlinks in forms from tech sites and compared all of the Property Settings for the field and the control and have set mine accordingly. (The test db links work fine on my computer - both with linking to local files on my drive and to files located on the Intranet shared drive).

I have researched this on many tech sites, including here, and have found no definitive answers. I would prefer not to have to write a bunch of code to use File Open dialogs when Access provides what should be a simple solution (ha!) through the use of a Hyperlink.

Any suggestions as to what might be causing this problem?

Larry

I provided a solution for a User who was having a very similar problem in dealing with Hyperlinks in general. Download the Test Database that I used to demonstrate how to Select Files, Write them to a Text Box as a Hyperlink, then Save them to the underlying Table as Hyperlinks. The code is very simple and uses the Office Dialog as the preferred Method of selection. Be sure to set a Reference to the Microsoft Office X.XX Object Library. Let me know how you make out. Good Luck!

[truthlover]

Ah, my friend ADesii to the rescue again :)

Ok, this is what's happened.

First I made sure the reference was set. It was because of the image button you helped me with.

Then I tested it by picking a file with the file chooser (like the one you helped me on, and is working just fine) and the link works properly.

Then I copied a path directly into the table (to make sure the table wasnt the problem) and that link worked properly.

That left me with the previously imported links giving me an error message that the file http://my local path cant be found.

So the only two conclusions I could come up with was the table wasnt properly converting the paths (which werent originally set up as hyperlinks) or something was wrong with the imported paths -- so I did a little digging...

Because of where these links came from, I assumed the paths were accurate -- That was where the error originated. The vast majority of them are wrong in some subtle way or another. Now I just have to decide whether to leave the field as a hyperlink or just text. I'm leaning toward text because no one expects functionality out of text (it still gives them an idea where to look for the file). But a hyperlink that only works 10% of the time is going to just be annoying.

I hope you (and other readers of this post) dont view the outcome of this post as a waste of time. The process is teaching me some logical steps to more thoroughly test my work, and I find that to be invaluable. I still have a long way to go, but I am learning, right?

Thanks again for all your help and patience!

I provided a solution for a User who was having a very similar problem in dealing with Hyperlinks in general. Download the Test Database that I used to demonstrate how to Select Files, Write them to a Text Box as a Hyperlink, then Save them to the underlying Table as Hyperlinks. The code is very simple and uses the Office Dialog as the preferred Method of selection. Be sure to set a Reference to the Microsoft Office X.XX Object Library. Let me know how you make out. Good Luck!