MS Access - Open file or document or website form hyperlink field

Hi,

I have a table that have thousands records and there in one field that have data type "hyperlink" that have link to websites and documents. I want that when hyperlink is click in table it open the website or that document  or excel file but it's not happening. Any help?
LVL 1
WSStudentAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
It should read:

Private Sub Link_Path_Click()

    Dim path As String
    path = "\\xxx.xx.xx.xx\" & Me.Link_Path

    Application.FollowHyperlink path

End Sub

Open in new window

/gustav
0
 
Gustav BrockCIOCommented:
You can study the demo here:

Show pictures directly from URLs in Access forms and reports

It's about downloading pictures, but you'll see that the URLs are working.

/gustav
0
 
WSStudentAuthor Commented:
Thank you, this is helpful

Now, a URL is a string like "http://www.example.com/images/somepicture.png". Unfortunately, if you save this as a hyperlink  field in a table, it is saved behind the scene as " #http://www.example.com/images/somepicture.png#".

This must be taken care of, if you retrieve the URL directly from a hyperlink field. Luckily, there is a native function of Access to handle this:
CleanUrl = HyperlinkPart(SavedUrl, acAddress)

I understood this, but can you tell where i should add this function?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gustav BrockCIOCommented:
That you can see in the code right below - to "clean" the address (if needed).

/gustav
0
 
PatHartmanCommented:
I don't use the HyperLink data type.  Mostly because most of my apps use SQL Server, Oracle, or DB2 as the BE rather than ACE and only ACE supports this specialaized data type.

Just store the path or URL in a text field and use the FollowHyperlink Method to open the document.  As long as the extension is one that Windows recognizes, this will work correctly.
0
 
WSStudentAuthor Commented:
PatHartman, that sounds good too , i can go for that but what about picture and excel sheet it will open that too? Also it would be helpful if you have any example using FollowHyperlink Method opening document in table which you can share.
0
 
PatHartmanCommented:
Followhyperlink will open ANYTHING as I said - provided Windows recognizes the extension.  This opens whatever is found in the referenced form field.

Private Sub txtViewAuditDoc_Click()

On Error GoTo Proc_Err
    If Me.txtViewAuditDoc & "" = "" Then
        MsgBox "There is no document link.  Please add one on the Audit Parms form.", vbOKOnly + vbInformation
    Else
        Application.FollowHyperlink Me.txtViewAuditDoc, , True
    End If
Proc_Exit:
    Exit Sub
Proc_Err:
    Select Case Err.Number
        Case 7971
            Resume Proc_Exit
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbCritical
            Resume Proc_Exit
    End Select
End Sub

Open in new window

0
 
WSStudentAuthor Commented:
can you help me why this is not working in this code:
Private Sub Link_Path_Click()
Dim path As String
path = "\\xxx.xx.xx.xx\" & Me.Link_Path
Application.FollowHyperlink Me.path

End Sub

Open in new window

0
 
WSStudentAuthor Commented:
@Gustav, this is also not working.
0
 
Gustav BrockCIOCommented:
Then your link doesn't work. Insert a debug to view what you try to open:

Private Sub Link_Path_Click()

    Dim path As String
    path = "\\xxx.xx.xx.xx\" & Me.Link_Path

    Debug.Print path

    Application.FollowHyperlink path

End Sub

Open in new window

/gustav
0
 
WSStudentAuthor Commented:
how to insert debug when code is not giving any error and not displaying anything either?

Also note one thing the field link_path data type in hyperlink , does that create any problem?
0
 
Gustav BrockCIOCommented:
It is inserted in my code above.
Press Ctrl+G and study the output.

/gustav
0
 
WSStudentAuthor Commented:
it's just taking the me.Link_path, it's not concatenating the "\\xxx.xx.xx.xx\" path.why is this so?
0
 
Gustav BrockCIOCommented:
That is not possible, so look carefully again - and/or copy/paste your code here.

/gustav
0
 
WSStudentAuthor Commented:
i found a problem while breaking it and i think this is the problem : path= "\\xxx.xx.xx.xx\#GLO\ALL\test.pdf# , so these "#" are not letting it opening this but i haven't add these "#" , from where these "#" signs are coming??
0
 
Gustav BrockCIOCommented:
Yes, you've asked about that above, but here it is again:

    ' Strip leading and trailing octothorpes from URL string.
    Address = HyperlinkPart(Url, acAddress)
    ' If Address is a zero-length string, Url was not wrapped in octothorpes.
    If Address = "" Then
        ' Use Url as is.
        Address = Url
    End If

Open in new window

/gustav
0
 
WSStudentAuthor Commented:
where i should add this code , in the on click event between code or where?

Also what if i use datatype as text instead of hyperlink, will that work?

i use datatype text it gives run time error 490 (Cannot open the specififed file) although the file is at the specified location.
0
 
Gustav BrockCIOCommented:
Try this:

Private Sub Link_Path_Click()

    Dim path As String
    Dim Address As String

    ' Strip leading and trailing octothorpes from URL string.
    Address = HyperlinkPart(Nz(Me.Link_Path.Value), acAddress)
    ' If Address is a zero-length string, Me.Link_Path was not wrapped in octothorpes.
    If Address = "" Then
        ' Use Me.Link_Path as is.
        Address = Nz(Me.Link_Path.Value)
    End If
    path = "\\xxx.xx.xx.xx\" & Address

    Debug.Print path

    Application.FollowHyperlink path

End Sub

Open in new window

/gustav
0
 
WSStudentAuthor Commented:
it says cannot open the specified path and in the immediate window it display this "\\xxx.xx.xx.xx#\GLO\ALL\test4.xlsx#". still # are there.
0
 
Gustav BrockCIOCommented:
Can't tell. If I run similar code, I get:

s = "#\GLO\ALL\test4.xlsx#"
? Application.HyperlinkPart(s, acAddress)
\GLO\ALL\test4.xlsx

Open in new window

/gustav
0
 
WSStudentAuthor Commented:
Thank you Gustav, the code above started working when i change hyperlink field to text.

Thank you again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.