Access Report Hyperlink not working as it should (1 Viewer)

Berkley2020

New member
Local time
Today, 06:56
Joined
May 7, 2020
Messages
4
Hi there,

I am new to this forum so hopefully I'm doing things right! ;)

I have had lots of Access experience, although that was quite a while ago. Unfortunately, I'm pretty green to SharePoint. I have a feeling my question will be quite easy to answer given the vast experience everyone has on this forum.

I have an Access report which contains hyperlinks to a SharePoint site, specifically a SharePoint list. This list contains all the records from the Access table in question. The report is pretty basic that runs from an Access query with a date parameter to narrow down the rows returned. This query is based on the records in the Access table in question.

What I am trying to do is when the end user clicks on one of these links on the report, I want the user to be taken to that specific item on the SharePoint list based on the unique identifier.

I must be getting the syntax wrong or need to add some vb code to the 'Click' event procedure?

The steps I took are the following:
  • Added all the fields needed on report.
  • Selected the field in question, Category, changed the fields', properties to...
  • Ran the report, clicked on a Category link on the report and was taken to the correct SharePoint list.
  • So that's all good, except the link displayed all Categories on the SharePoint list; where as it should have taken me to the specific Category record based on the unique ID Access field.
Obviously the ID parameter on the link is not working?

Any assistance would be greatly appreciated. Thanks!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF! You might try constructing the link in the query source of the report, so you can see if it is correct.
 

Berkley2020

New member
Local time
Today, 06:56
Joined
May 7, 2020
Messages
4
Hi. Welcome to AWF! You might try constructing the link in the query source of the report, so you can see if it is correct.

Hi,

Thanks for reply. I did as you suggested and when I ran the query, the URL address along with the ID field displayed for each record in the field where I created an expression.

Here's the expression I created: Test1: HyperlinkPart("https://applications.mysite.com/All Categories.aspx?ID=" & [Category]![ID] ,0)

So that's promising.

However, how do I display the Category Name field on the report and then click on it so that will take me to the specific Category Name record on the SharePoint list, based on the ID for that category?

I played around a little bit more with the report. I changed the hyperlink property to "https://applications.mysite.com/All Categories.aspx?ID=" & [Category]![ID] and when I ran the report, I now get an error stating "Cannot open the specified file".

I don't know what I'm doing wrong! :(

Any advice?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,358
Hi,

Thanks for reply. I did as you suggested and when I ran the query, the URL address along with the ID field displayed for each record in the field where I created an expression.

Here's the expression I created: Test1: HyperlinkPart("https://applications.mysite.com/All Categories.aspx?ID=" & [Category]![ID] ,0)

So that's promising.

However, how do I display the Category Name field on the report and then click on it so that will take me to the specific Category Name record on the SharePoint list, based on the ID for that category?

I played around a little bit more with the report. I changed the hyperlink property to "https://applications.mysite.com/All Categories.aspx?ID=" & [Category]![ID] and when I ran the report, I now get an error stating "Cannot open the specified file".

I don't know what I'm doing wrong! :(

Any advice?
Hi. Try not using the HyperlinkPart() method and simply concatenate your string. For example:

Link: [CategoryName] & "#https://webaddress/...aspx?ID=" & [CategoryID]

Then use it on your report. If you need more help, maybe you can post a sample copy of your db with test data.
 

Berkley2020

New member
Local time
Today, 06:56
Joined
May 7, 2020
Messages
4
Hi. Try not using the HyperlinkPart() method and simply concatenate your string. For example:

Link: [CategoryName] & "#https://webaddress/...aspx?ID=" & [CategoryID]

Then use it on your report. If you need more help, maybe you can post a sample copy of your db with test data.

Thanks. I got it to work. When I click on the link in the Access report, it takes me to the SharePoint list, as I wanted. However, it does not take me to the actual row in that list based on the CategoryID shown in the link as a parameter.

This particular SharePoint list contains many rows. Because of that, I want to be able to take the end user directly to the specific row of the category in the SharePoint list when that report link is clicked on. So basically when the end user runs this report, they are prompted to enter a date, which returns a much smaller dataset. Then they click on the link of the category name and it should take them to that specific category in the SharePoint list.

So it seems like the parameter value is not getting passed on this link. I'm going to need to research this a little more. If you think of anything, send a post. If I get it resolved I will report back the resolution on this post. Thanks for all the help!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
21,358
Thanks. I got it to work. When I click on the link in the Access report, it takes me to the SharePoint list, as I wanted. However, it does not take me to the actual row in that list based on the CategoryID shown in the link as a parameter.

This particular SharePoint list contains many rows. Because of that, I want to be able to take the end user directly to the specific row of the category in the SharePoint list when that report link is clicked on. So basically when the end user runs this report, they are prompted to enter a date, which returns a much smaller dataset. Then they click on the link of the category name and it should take them to that specific category in the SharePoint list.

So it seems like the parameter value is not getting passed on this link. I'm going to need to research this a little more. If you think of anything, send a post. If I get it resolved I will report back the resolution on this post. Thanks for all the help!
Hi. Don't mean to state the obvious, you tested the correct url format manually, correct?
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:56
Joined
Sep 21, 2011
Messages
14,051
I've just tried my charity sharepoint site and I get pretty much the same.?

I can only get to the home page from Access, yet the url will take me several levels down.?
 

Users who are viewing this thread

Top Bottom