Can't Change Hyperlink

[raweber]

I've built a database to track active engineering studies in my department. Mostly it works great, but I'm having one niggling bug that I can't figure out. Note that I'm an engineer and not a programmer.



When a new study is created, I have code to create a network folder with a unique, study-related name. A hyperlink to this folder is stored in a field in the record for that study. Then there is a button on the main form that will follow that hyperlink and open the folder. It all works great until I have to edit the name of the folder (generally because some information about the study was mis-entered and thus the folder is mis-named).

I can go into the back end of the database and change the hyperlink, even verifying that it works and can click directly to the folder from the back end, but that button on the main form will never work again for that record. When I click on the button I first get a warning that "A potential security concern has been identified" and an option to proceed to the hyperlink. When I hit Yes I get the following error: "Run-time error '7971': Microsoft Access cannot follow the hyperlink to '#\\ (and then here is the hyperlink address)'

Please let me know what other information I can provide in trying to suss out what's going on here. I'm not sure where the "#" is coming from in the error because it's not part of the hyperlink.

Thanks,
Rob

[ItsMe]

How did you create the Command Button that opens the link? I don't know for sure but, it sounds as though you have an embedded macro that is not considering the text field that contains the literal string. As you mentioned, there is text within your table that describes the path to the folder. You need to use VBA that dynamically links to your folder.

[raweber]

Thanks for the feedback. The VBA underlying the button is simply:

FollowHyperlink ([Link])

where Link is the name of the field containing the hyperlink.

I have a little code prior to this to identify if the field is blank and pop a message to the user in that case. The field on the back end is definitely being stored as a hyperlink and not just text.

Would there be a more reliable way to do this without the hyperlink? To store the address as text and have the button interpret that as a link?

Thanks, Rob

[ItsMe]

... To store the address as text and have the button interpret that as a link?
...

Apparently, that is what is happening already. There are a couple of different ways to code it, though.

Perhaps we should focus on the hashtag. First, locate one of the records (from within the table) that is creating the issue and copy all of the text from that field named Link. Post it here. Post the name of the field as well (just to keep things clear here).

[raweber]

Here is a field from a record that is giving me problems. The name of the field is "Link"

\\COV-DOT-HRFile2\Groups\Traffic\Studies\AC\AC-0695-20150501-FT\

In Design View it tells me that the Data Type is "Hyperlink"

[ItsMe]

In Design View it tells me that the Data Type is "Hyperlink"

This is the problem. It is probably adding an http:// or something to the front of your string. Copy your table for a backup. Then, in your original table, change the field named Link to Text. Click OK for the warning. Then, check that you still have text that looks like \\COV-DOT-HRFile2\Groups\Traffic\Studies\AC\AC-0695-20150501-FT\

The fact that it used to work and then, when you edit a field, it does not work, has me concerned. You will want to analyze how the field was populated originally. Compare the difference between that (original process) and simply editing the text within.

Here is some code that I use within a Button Click event named ButtonName. The click event for the button name is the same as the button name in the line of code!

Me.ButtonName.HyperlinkAddress = "\\ServerName\FolderName\SubfolderName\FileName.Extension"
Me.ButtonName.HyperlinkAddress = "http://google.com"
Me.ButtonName.HyperlinkAddress = "C:\Test"

By doing it this way, you can avoid warnings when linking over the network. If you want to open a folder or file on your C drive, consider copying the file(s) to a share or sharing the folder on the C drive. Use the UNC path vs a drive letter (C: ).

It is best to use plain old text in your tables whenever possible. Obviously, use Integers, Doubles, Booleans, too. However, if it is alphanumeric, consider the Text data type first. My code example shows how you can adjust properties of a control in a form vs. adjusting properties in a field within a table. I try to keep my tables as pure as possible and use code to change stuff when needed.

[raweber]

Thanks - seems like a reasonable suggestion. I'll give it a try and let you know how it works.

[ItsMe]

BTW

Me.ButtonName.HyperlinkAddress = Me![Link]

[raweber]

That did the trick, thanks!

Interesting, when I converted the Link field to Text there appeared hashtags surrounding all the links that I had edited. It edited them out and tested and they work fine, now. This seems to work really well and I like the philosophy of keeping the table simple and using the forms to interpret it.

Thanks, again,

Rob

[ItsMe]

Glad you were able to get things sorted.