Varying hyperlink in an Access report

Firstly, avoid the hyperlink data type; it's more trouble than it's worth.  Instead store each hyperlink as text in a column of text data type.

In the report do as follows:

1.  Bind a text box control to which ever column you wish to show as the hyperlink, and set its DisplayAsHyperlink property to 'Always'.

2.  Superimpose a command button completely over the above control and set its Transparent property to True (Yes).

3.  Add a text box control to the report bound to the column which contains the hyperlink and set its Visible property to False(No) to hide it.

4.  Set the HyperlinkAddress property of the command button to:

=[NameOfTheHiddenControlWhichContainsTheHyperlink]

Open the report in report view and click on what appears to be a hyperlink.  You will actually be clicking on the transparent button of course, so the hyperlink contained in the hidden control referenced by the button will be followed.

I wouldn't recommend it.  Hard-coding data is not a good idea.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

If data is stored correctly in this way then it is merely a matter of editing the values in a table should the need arise.  If it's hard coded in an event procedure then the code needs to be amended.

The method I described does work.  I've uploaded a simple little file named MaryC_999.accdb to my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly. 

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In the file I've included a table with a few links in a text column named Link, along with a Subject column.  I've added a simple report in which the values of the Subject column appear as clickable links, set up in the way I described with a transparent button over the control bound to the Subject column.

Sorry, but I have no experience of SharePoint.  If nobody else participates in this thread, start a new thread and include a hyperlink to this thread.

Thank you for your response. I got it to work but then I had a problem with SharePoint (the hyperlinks are pointing to a SP file).  If I use a direct link to SP it will work but this way it didn't - I got a connection closed error.  The only difference to the links is a last name.  Is it possible to use some type of code to build the link using the last name variable?

The reports I am running are run weekly for only three months.  Because of the temporary nature of the reporting, I would like to use hardcoding if nothing else works.

Thanks for this but the hyperlinks are not coming through. The field needs to be a label in order for the hyperlink to work.  Would it be possible to use an if/then statement as there are only 6 different links? Would that work?