vba - Open Hyperlinks in Access

Question

I have a table of products where there is say a pdf for a specific products user manual. I'm storing the model name and it's file path in my products table (in Access). I've created a form in Access that allows the user to search by product name and it narrows down the number of files and shows the results from the search in a list box. However my biggest problem is opening the actual PDF. It opens the file, but I have to store the file path exactly how it is and the path of the files are long. Is there a way to open the PDF hyperlinks without using the Followhyperlink command? Or is there a way that I can show only the file name of the pdf in my list box rather than the entire path name? If I change the display text in my products table it doesn't open the hyperlink, I get an error. Any help would be greatly appreciated!

Answer 1

Application.FollowHyperLink() has problems with security, especially when opening files on a network drive. See e.g. here: http://blogannath.blogspot.de/2011/04/microsoft-access-tips-tricks-opening.html

A better method is the ShellExecute() API function. Essentially it looks like this (trimmed from http://access.mvps.org/access/api/api0018.htm ):

' This code was originally written by Dev Ashish.
' http://access.mvps.org/access/api/api0018.htm

Private Declare Function apiShellExecute Lib "shell32.dll" _
    Alias "ShellExecuteA" _
    (ByVal hwnd As Long, _
    ByVal lpOperation As String, _
    ByVal lpFile As String, _
    ByVal lpParameters As String, _
    ByVal lpDirectory As String, _
    ByVal nShowCmd As Long) _
    As Long

Public Const WIN_NORMAL = 1         'Open Normal
Private Const ERROR_SUCCESS = 32&


Public Function fHandleFile(stFile As String) As Boolean

    Dim lRet As Long

    lRet = apiShellExecute(hWndAccessApp(), "Open", stFile, vbNullString, vbNullString, WIN_NORMAL)

    If lRet > ERROR_SUCCESS Then
        ' OK
        fHandleFile = True
    Else
        Select Case lRet
            ' Handle various errors
        End Select
        fHandleFile = False
    End If

End Function

Now for your listbox: Set it to 2 columns, the first being the model name, the second the file path. Set the column width of the second column to 0, so it will be invisible.

And in the doubleclick event, call fHandleFile with the second column (file path):

Private Sub lstManuals_DblClick(Cancel As Integer)

    Call fHandleFile(Me.lstManuals.Column(1))

End Sub