Hyperlink Problem

[PhilOfWalton]

I have Hyperlinks in 2 tables, both work absolutely normally.
These 2 tables (and others) get combined and end up in a form.
One hyperlink works correctly.

The other one, when I click on it, appends a hash "#" at both begining and and of the link and nothing happens.

Usually, when I click on another field, the Hashes disappear.

If I right click on the link and select Open Hyperlink, all works OK.

There is no code associated with this field.

I have used this program, unaltered for years and this is a recent problem.

Interestingly, in a totally different part of the program, using VBA, I open the website, extract some data to load into Access with no problem.

Any ideas please?

Phil

[jforbes]

That's weird, sounds like it is getting formatted as a Date somehow. Do you have some code that you can provide that is used to launch the Hyperlink?

[PhilOfWalton]

Thanks for quick reply.
No code used - no code needed (it works in the table)
Format is "Plain Text"
Display as Hyperlink is "If Hyperlink"

BE database compacted / repaired
FE database decompiled compacted / repaired

Phil

[jforbes]

I don't use the IsHyperlink Property. There are a lot of different places throughout our databases that I would want to launch a URL, so I use the same Function to launch them regardless of how the URL is provided. Part of that single launch point approach is to more tightly control how the URL is launched instead of just giving control over to Access to do as it wants with it. I can't remember the details, but Access would do some strange things from time to time. ... maybe it's what you are running into.

Using a single launch point in the case of a Datasheet, for the URL field, the ForeColor is set to Blue and the Text is Underlined so that it looks like a Hyperlink to the user. They just think it's a hyperlink and they don't give it a second thought. Then in the OnClick for the Field it calls the same function that is used through the database to open an attachment.

This is the function I use with some of our database specific stuff ripped out (Actually a lot ripped out, so hopefully it didn't introduce errors). It doesn't look as complicated this way, but you should get the point:

Function OpenAttachment(ByVal sTargetAndLocation As String) As Boolean
 
    Dim dTaskID As Double
 
    OpenAttachment = False
 
    If InStr(sTargetAndLocation, "http") > 0 Then
        FollowHyperlink (sTargetAndLocation)
        OpenAttachment = True
    Else
        dTaskID = Shell("explorer.exe "& sTargetAndLocation, vbNormalFocus)
        OpenAttachment = (dTaskID >= 0)
    End If
 
End Function

[ADezii]

Thye '#' Symbol serves as a Delimiter for the various Components of a Hyperlink Field, namely: displaytext#address#subaddress#screentip. Under certain circumstances such as when you are typing an address part directly into a Hyperlink field, Microsoft Access adds the two # symbols that delimit parts of the hyperlink data. In effect it attempts to seperate the displaytext, address, and subaddress Components. This appears to be what is happening here. How, exactly, are the 2 Tables combined, specifically the Hyperlink Fields?

[PhilOfWalton]

Firstly thanks to both for replying
Jforbes solution works perfectly, and I may well adopt it, but for the moment I am trying to understand why the problem has arisen.

So to answer ADezii,
3 tables .. only relevant fields shown

TblSuppliers
    AccID      Auto   PK
    AccName    Text
    AccWevsite Hyperlink
 
TblJAccInvestments
    InvestmentID  Long Joint FK
    AccID         Long  Joint FK
 
 

[PhilOfWalton]

Sorry, hit the wrong button and half a message got sent.
Will try again

 
TblSuppliers
    AccID           Auto   PK
    AccName         Text
    AccWebsite      Hyperlink
 
TblJAccInvestments
    InvestmentID    Long Joint FK
    AccID           Long Joint FK
 
TblInvesments
    InvestmentID    Auto   PK
    InvestmentName  Text
    WebPage         Hyperlink
 

This is set up as a many to many relationship, and the form basically shows the Investment and the associated Supplier.
The Hyperlink for the supplier works perfectly, but the WebPage Hyperlink displays the symptoms above.

To try to get further I added a field "WebText" to the TblInvestments and ran the following update query to try to see what was in the WebPage Hyperlink

 
UPDATE TblInvestments SET TblInvestments.WebText = [WebPage]
WHERE ((Not (TblInvestments.WebPage) Is Null));
 

Not sure what I should see, but a typical examply is the the Hyperlink field I have
https://uk.finance.yahoo.com/q/?s=BNC.L
and the corresponding text field has
#Https://uk.finance.yahoo.com/q/?s=BNC.L#

Somehow I expected to see more.
Incidentally, the Hyperlink in the query works as expected.

NB On this website both are showing as Hyperlinks. The latter one should just be plain text.

Phil

[ADezii]

Odd problem, I'll see if I can reproduce this anomaly tomorrow when I will recreate the DB.

[PhilOfWalton]

I think you will struggle to reproduce it. As I said, it has worked for many months now, but thanks for having a go.

Phil

[PhilOfWalton]

Sorry for late reply, I was hoping things would sort themselves out, but alas no.

So I have gone for jforbes elegant solution which works perfectly.

Thanks again

Phil