PDF Report with clickable hyperlinks

[fat controller]

I am at the very early stages of designing some sort of solution to replace some inherited Excel files that are neither secure or user-friendly.

One part of these files is worksheets that are essentially just a series of hyperlinks that link out to documents stored on a network drive. I am in the process of moving all of these documents over into a database, mainly so that we have periodic review dates flagged up to users, and to ensure that there isn't umpteen different versions lying about on the network (there is currently a mass of Word documents with various versions that are out of date etc, but absolutely no structure or organisation to them).

If I had an Access report that listed the documents, and a hyperlink to the file location, would it be possible for that report to be produced as a PDF file, and have clickable links therein?

 

[Minty]

Yes - I seem to remember that formatting the hyperlink is a little "interesting" to get it to work in the pdf, but it's certainly possible.

 

[fat controller]

Evening all, I am at the point of giving this a shot now, but seem to have hit a bit of a wall. I have created a report, and it has text boxes each bound to their respective fields and they are displaying the hyperlink correctly.

I have set the field property as Hyperlink, Always and have also put a small amount of code as an On Click event

Dim Hyperlink As String

Hyperlink = Me.txtHypTS.Value

Application.FollowHyperlink (Hyperlink)

In Report View, the hyperlink works just fine. However, in Print Preview it doesn't, and therefore nor does it carry forward to a resulting PDF copy of the report. Is there a way to get these to work?

Also, for an extra layer of refinement (I don't want much, do I?? ) is there a way to have the text that is displayed to be different to the hyperlink address?

 

[fat controller]

I have made a bit of progress - I had the field type as Text in the table, which was causing the problem. I now have a report that outputs in PDF that has clickable links

Now the golden question - is there a way to change the text that is displayed, preferably related to part of the hyperlink itself?

For example, one of the hyperlinks is: E:\AV\TSDS081-53 160509.doc (this is purely test data by the way, to the file path will be quite a bit different in reality); is it possible to display just the filename TSDS081-53 160509.doc?

 

[Minty]

I don't think so unless you have the alternative text also stored as a separate field, you would also need to probably do some coding to display it correctly.

 

[fat controller]

As it happens, I do have the alternative text stored in a field in the same table (it is used elsewhere for another purpose).

I did a very brief bit of reading earlier and saw Hyperlink part, but I couldn't quite work out how it was to be used?

 

[fat controller]

OK, I might not need to have any text showing, so that will mean that I can set the fore and background colours to a solid colour, and all the user has to do is click the block of colour.

Something else I can't fathom though - the report has a number of textboxes, each bound to a hyperlink field in the table; the hyperlinks fields contains links that are known to be working (I also use them on a form), but when I open the report which shows multiple records, only the first record will work when clicked?

The subsequent records are showing the correct links in the textbox, but when clicked they do nothing much at all?

EDIT - I suspect it is something to do with my table; I downloaded the sample database from this thread, copied my table into it and then linked the report to my table as its data source, changing the three fields data sources accordingly, and now it is only the first record that is clickable?

FURTHER EDIT - even opening the table, it is only the first record that the hyperlink works on. All other records it does nothing despite being correct. Oddly, when I click the command button on the form it opens all of the hyperlinks?

 

[Minty]

Is this in report preview? It won't perform any event based formatting until it's printed I don't think...

 

[fat controller]

I've found it! I had originally set the field types as text, and when there was only the one record in the table, I changed them to hyperlink.

The fields that contain hyperlinks have the hyperlinks added to them programmatically based on values that are set on the form that feeds the table - the string is built using VBA, and then the string is dumped into the hyperlink field as text.....

On my way home in the car, I had one of those "Oh ya numptie!" moments when I realised that I should have it storing the value with a '#' at the start and the end.... I have just tried it, and it works. Doh!

 

[fat controller]

:banghead:This is driving me mental! I had it working, with a cloned copy of the hyperlinked files stored on my USB stick for testing - all good.

So, I went ahead and changed the hyperlink that gets saved to the correct one for our network:

\\ourcompany.co.uk\documents\sitename\sitecode\filename.doc

This saves absolutely fine, however the PDF links did not work, and when I hovered over one of thy hyperlinks, it had changed the slashes to the opposite direction:


//ourcompany.co.uk/documents/sitename/sitecode/filename.doc

And this of course means that the links don't work. Is there any way to stop it from converting them?

 

[fat controller]

Do I need to put file:/// ahead of they hyperlink address to make it work?

 

[Minty]

You might need the Https:\\ otherwise it assumes its a local address (which it might be but that won't resolve in a browser)
/caveat educated guess /

 

[fat controller]

It is the oppposite way round I'm afraid - the conversion to PDF is changing the UNC file path from a local address (which it should be) to a web URL.

I have done a bit of Googling, and it appears that I am not alone in this happening with documents converted to PDF - what I can't find if there is a solution to the problem.

It works beautifully if there is a drive letter in the (E:\documents\myfolder\myfile.doc for example would work perfectly) - - as soon as it is changed to a network location, it goes horribly wrong?

Really, really pee'd off that I can't work this out, as the idea would massively simplify document handling for hundreds of us.

 

[Minty]

Ah - Our documents are stored on an internal intranet - so we wanted the web based URL to work that way.