#http://www.bloomberg.com/quote/ABT:CI# (1 Viewer)

[Gasman]

Ok, lets try and start from the beginning.

Why are you copying and pasting to the clipboard, as a hyperlink is meant to take you to that link directly.?

I have not used hyperlink fields myself yet, but I remember someone posting that it comes in two parts, one that is the text shown, and the other the actual link?

Found it
From this thread https://www.access-programmers.co.uk/forums/showthread.php?t=302051&highlight=hyperlink post #3, that confirms my memory.

Arne solved it with Split(), and we can use that to solve both problems, whether you have the description and link or just the link

Private Sub Vendor_Website_Click()
' = = = K copy button
' = = = Source of data = Vendor_Contacts_VC_WebSite
If Nz(Me.Vendor_Contacts_VC_WebSite, "") <> "" Then
    'Me.Vendor_Contacts_VC_WebSite = Replace(Me.Vendor_Contacts_VC_WebSite, "#", "")
    [COLOR="Red"]Me.Vendor_Contacts_VC_WebSite = Split(Me.Vendor_Contacts_VC_WebSite, "#")(1)[/COLOR]
    With Me.Vendor_Contacts_VC_WebSite
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
    End With
    DoCmd.RunCommand acCmdCopy
    'MsgBox "Text Copied into Memory"
Else
    MsgBox "There is no text for copying"
End If

End Sub

and while we are on the subject of #, please use the # icon above the editing window before pasting code. this then places it in code blocks and makes it easier to read.

HTH

 

[Gasman]

I've since found a problem if there is no # in the control and I have no idea as to the quality of your data.

However using the code below it will now work when no # are in the control.

    If InStr(Me.Vendor_Contacts_VC_WebSite, "#") > 0 Then
        Me.Vendor_Contacts_VC_WebSite = Split(Me.Vendor_Contacts_VC_WebSite, "#")(1)
    End If

 

[access2010]

Thank you for your code and note.
The reason we have been copying the web address to the clipboard, is that on some of the Secure Web Sites the Web Hyperlink is not accepted.
===

When we manually copy the field we receive = www.vistaprint.ca
When we run your code the first time we receive = http://www.vistaprint.ca
===

When we run your code the second time the following error message is received
Run-time error "9"
Subscript out of range
===

The error line is shown below
Me.Vendor_Contacts_VC_WebSite = Split(Me.Vendor_Contacts_VC_WebSite, "#")(1)
===

Thank you for trying to help us.
Nicole

 

[Gasman]

You have not include my second amendment.

The solution suggested was to replace # with nothing.
You should have noticed that that when you use the button, the # is removed, so any subsequent copy will not find a #. That is why I posted again and used the If statement to check if there is a # present.

I have also copied and pasted one of my secure website urls into one record, that shows as having a https url and if you use that, then all this copy and paste is redundant. When you do paste it in you will get # as you know.

HTH

 

[access2010]

Thank you all for your help.

Paul and Nicole