Results 1 to 14 of 14
  1. #1
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55

    OnClick Code to Open a Hyperlink Partially Working

    I have a form that lists the title of records and when the title box is clicked, it opens the hyperlink which is in another field for that record titled Location.



    I acquired the code and while it does work if the hyperlink exists and is accurate, it doesn't work to show a message if the file name and path are no longer accurate and you also receive an error message if the Location field is empty. It also appears that there is two different error messaging activities of which neither work. I would like the error message to simple state "File not found".

    Could someone please provide some direction on fixing this code. Any assistance is appreciated.

    Code:
    Private Sub Title_Click()
        
        Dim File As String
        Dim hyperl As Hyperlink
        File = Me!Location.Value
        If Len(File) > 0 Then
            Call fHandleFile(File, WIN_NORMAL)
        Else
            MsgBox "The file has been moved/renamed/deleted", vbOKOnly + vbExclamation, "File Not Found"
        End If
    
    
    Exit Sub
    
    
    subErr:
        If Err.Number = 490 Then
            Call fHandleFile(File, 1)
            Resume Next
        End If
        Call MsgBox("Error " & Err.Number & " - " & Err.Description & " (in " & Me.Name & ".Location_Click)")
      
    End Sub

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This event is calling procedure fHandleFile(). We need to see its code, and if it has an error procedure.

    Also, you say an error message occurs, but you don't tell us specifically what the error is. Not much help.

  3. #3
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    My apologies, its "Run-time error 94, Invalid use of Null". fHandleFile() code below.

    Code:
    '************ Code Start **********
    ' This code was originally written by Dev Ashish.
    ' It is not to be altered or distributed,
    ' except as part of an application.
    ' You are free to use it in any application,
    ' provided the copyright notice is left unchanged.
    '
    ' Code Courtesy of
    ' Dev Ashish
    '
    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
    
    
    '***App Window Constants***
    Public Const WIN_NORMAL = 1         'Open Normal
    Public Const WIN_MAX = 3            'Open Maximized
    Public Const WIN_MIN = 2            'Open Minimized
    
    
    '***Error Codes***
    Private Const ERROR_SUCCESS = 32&
    Private Const ERROR_NO_ASSOC = 31&
    Private Const ERROR_OUT_OF_MEM = 0&
    Private Const ERROR_FILE_NOT_FOUND = 2&
    Private Const ERROR_PATH_NOT_FOUND = 3&
    Private Const ERROR_BAD_FORMAT = 11&
    
    
    '***************Usage Examples***********************
    'Open a folder:     ?fHandleFile("C:\TEMP\",WIN_NORMAL)
    'Call Email app:    ?fHandleFile("mailto:dash10@hotmail.com",WIN_NORMAL)
    'Open URL:          ?fHandleFile("http://home.att.net/~dashish", WIN_NORMAL)
    'Handle Unknown extensions (call Open With Dialog):
    '                   ?fHandleFile("C:\TEMP\TestThis",Win_Normal)
    'Start Access instance:
    '                   ?fHandleFile("I:\mdbs\CodeNStuff.mdb", Win_NORMAL)
    '****************************************************
    
    
    Function fHandleFile(stFile As String, lShowHow As Long)
    Dim lRet As Long, varTaskID As Variant
    Dim stRet As String
        'First try ShellExecute
        lRet = apiShellExecute(hWndAccessApp, vbNullString, _
                stFile, vbNullString, vbNullString, lShowHow)
                
        If lRet > ERROR_SUCCESS Then
            stRet = vbNullString
            lRet = -1
        Else
            Select Case lRet
                Case ERROR_NO_ASSOC:
                    'Try the OpenWith dialog
                    varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                            & stFile, WIN_NORMAL)
                    lRet = (varTaskID <> 0)
                Case ERROR_OUT_OF_MEM:
                    stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
                Case ERROR_FILE_NOT_FOUND:
                    stRet = "Error: File not found.  Couldn't Execute!"
                Case ERROR_PATH_NOT_FOUND:
                    stRet = "Error: Path not found. Couldn't Execute!"
                Case ERROR_BAD_FORMAT:
                    stRet = "Error:  Bad File Format. Couldn't Execute!"
                Case Else:
            End Select
        End If
        fHandleFile = lRet & _
                    IIf(stRet = "", vbNullString, ", " & stRet)
    End Function
    '************ Code End **********

  4. #4
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    And on which line of code does execution stop?

  5. #5
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    Code:
    file = Me!Location.Value

  6. #6
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    I believe I resolved the issue by altering the line to the below.

    Code:
    file = Nz(Me!Location.Value)

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Looks right. Good job.

  8. #8
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    I would add a legitimate check if the file exists first: http://allenbrowne.com/func-11.html

  9. #9
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Below modified code will check for file exists.
    Code:
    Private Sub Title_Click()
        on error goto subErr
        Dim File As String
        Dim hyperl As Hyperlink
        File = nz(Me!Location)
        if len(dir(file)) = 0 then
           msgbox "File does not exist"
           exit sub
        end if          
        Call fHandleFile(File, WIN_NORMAL)
        exit sub
    
    
    subErr:
        If Err.Number = 490 Then
            Call fHandleFile(File, 1)
            Resume Next
        End If
        Call MsgBox("Error " & Err.Number & " - " & Err.Description & " (in " & Me.Name & ".Location_Click)")
    end sub

  10. #10
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    Just a point to make - don't overlook the recommendation of Browne's FileExists - it is very useful for other common needs.

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    jwhite is correct. Those functions should be in everyone's code locker. I was just trying to keep it simple.

  12. #12
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    Thank you both for your input. I truly appreciate it. While Allen Browne code might be the cream of the crop, I am a newbie at such in depth code and tend to struggle with his code because it doesn't tell a new fish like me how to implement it. If I utilize his code to check for the existence of a file, it doesn't tell you how to implement it in a form to show whether the hyperlink exists for each record. Still learning.

    While my questions was more in line with an OnClick event to open a hyperlink, jwhite are you saying that I should used Allen Browne's code in lieu of Dev Ashish's code?

  13. #13
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The code I posted in #9 will check for the existence of the file with the dir() function. Allen Brown's code does the same thing (more complex) with a generalized method.

  14. #14
    jwhite is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Dec 2012
    Location
    North Carolina
    Posts
    349
    You call Browne's FileExists or FolderExists function just like you would any other function. It will return a boolean result of True or False. The weblink given gives several examples of how to call it and what it does - Plug 'N Play.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 04-24-2014, 01:56 PM
  2. Replies: 2
    Last Post: 08-03-2013, 03:41 PM
  3. Replies: 21
    Last Post: 02-14-2011, 02:51 PM
  4. Open up url page hyperlink not working!
    By riffraff_ashraf in forum Forms
    Replies: 1
    Last Post: 09-29-2010, 09:33 AM
  5. Replies: 2
    Last Post: 06-14-2010, 09:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums