Results 1 to 15 of 15
  1. #1
    drew2020 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2020
    Posts
    11

    Hyperlink help


    When I am adding hyperlink from a form. It need to right click on the textbox >Hyperlink>Edit hyperlink. Is there any expresion build or macro for this one that when I click on the textbox the hyperlink dialog box appear that makes me add the hyperlink?Click image for larger version. 

Name:	HYPERLINK.PNG 
Views:	40 
Size:	26.3 KB 
ID:	43139HYPERLINK.accdb

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,550
    dont mess w 'hyperlinks'.
    just put the web address in a field, then use the code below to open it. (it will open anything)

    Paste this code into a module, and it will open ANY file in its native application.
    In a form put the field and a button to open it.


    if the file is myFile.pdf, will open it in acrobat
    if the file is myFile.doc, it will open the doc in Word
    if the file is web address, will open it in a browser
    if its just a file path, it will open in file explorer.
    etc..


    usage:
    OpenNativeApp txtBox


    paste this code into a module
    Code:
    'Attribute VB_Name = "modNativeApp"
    'Option Compare Database
    Option Explicit
    
    
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
    
    
    Private Declare Function GetDesktopWindow Lib "user32" () As Long
    Const SW_SHOWNORMAL = 1
    Const SE_ERR_FNF = 2&
    Const SE_ERR_PNF = 3&
    Const SE_ERR_ACCESSDENIED = 5&
    Const SE_ERR_OOM = 8&
    Const SE_ERR_DLLNOTFOUND = 32&
    Const SE_ERR_SHARE = 26&
    Const SE_ERR_ASSOCINCOMPLETE = 27&
    Const SE_ERR_DDETIMEOUT = 28&
    Const SE_ERR_DDEFAIL = 29&
    Const SE_ERR_DDEBUSY = 30&
    Const SE_ERR_NOASSOC = 31&
    Const ERROR_BAD_FORMAT = 11&
    
    
    
    
    Public Sub OpenNativeApp(ByVal psDocName As String)
    Dim r As Long, msg As String
    
    
    r = StartDoc(psDocName)
    If r <= 32 Then
        'There was an error
        Select Case r
            Case SE_ERR_FNF
                msg = "File not found"
            Case SE_ERR_PNF
                msg = "Path not found"
            Case SE_ERR_ACCESSDENIED
                msg = "Access denied"
            Case SE_ERR_OOM
                msg = "Out of memory"
            Case SE_ERR_DLLNOTFOUND
                msg = "DLL not found"
            Case SE_ERR_SHARE
                msg = "A sharing violation occurred"
            Case SE_ERR_ASSOCINCOMPLETE
                msg = "Incomplete or invalid file association"
            Case SE_ERR_DDETIMEOUT
                msg = "DDE Time out"
            Case SE_ERR_DDEFAIL
                msg = "DDE transaction failed"
            Case SE_ERR_DDEBUSY
                msg = "DDE busy"
            Case SE_ERR_NOASSOC
                msg = "No association for file extension"
            Case ERROR_BAD_FORMAT
                msg = "Invalid EXE file or error in EXE image"
            Case Else
                msg = "Unknown error"
        End Select
    '    MsgBox msg
    End If
    End Sub
    
    
    
    
    Private Function StartDoc(psDocName As String) As Long
    Dim Scr_hDC As Long
    
    
    Scr_hDC = GetDesktopWindow()
    StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:\", SW_SHOWNORMAL)
    End Function



    usage:


    Code:
    sub btnOpenFile_click()
      OpenNativeApp ME.txtBox
    end sub

  3. #3
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    193
    Apologies for opening an old thread, but my question is directly related to this.

    I've been using Ranman256 solution to this in my DB ever since I discovered it. It's been great and worked flawlessly. Thank you!

    Now Windows 11 has reared it ugly head and I have found that it no longer works when executing the function via a command button ( I pass an eddress to Google Maps using this code)

    Code:
    stGoogleMap = "http://maps.google.com/maps?q="
    stURL = stGoogleMap & [Address] & " " & [City] & " " & [PostalCode]
    I recieve the error with GetDesktopWindow() selected.

    Compile Error
    Sub or Function not defined

    The VB editor then opens and displays this;


    Click image for larger version. 

Name:	Screenshot 2021-12-13 151744.png 
Views:	27 
Size:	13.7 KB 
ID:	46879

    If I run the same app on a computer with Windows 10, then there is no issue.

    I've checked to ensure that the same references are selected on both computers and they are the same.

    Do you have any idea how to resolve this?

  4. #4
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    193
    Addendum

    It seem the issue revolves around copatibility with the OS version

    If I add PtrSafe to the code

    Code:
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
    Private Declare PtrSafe Function GetDesktopWindow Lib "User32" () As Long
    Works fine in Windows 11, but crashes in Windows 10.

    So now I'm thinking that I need to test for which version of Windows is running and execute the correct Declare statement.

    Problem is I can't seem to find how to test for which O/S version is running in Access.

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    6,574
    Are you sure it is not the Access bitness?
    Are you 32bit on PC with win 10 and 65bit on the win 11 computer?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    Micron is online now Very Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,425
    see post 6 here for the right way to declare for 32 and 64 bit

    https://www.accessforums.net/showthr...ght=%23If+VBA7
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    193
    Thank you for the replies. I thought that I had addressed this in the past. Obviously not. I've come up with this solution that seems to work across 32bit & 64bit.

    Is this a good way to go?

    Code:
    ' Changes to enable compatibility with 64bit systems
    #If Win64 Then
        Declare PtrSafe Function LaunchApp Lib "User32" (ByVal n As LongLong) As Long
        Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
        Private Declare PtrSafe Function GetDesktopWindow Lib "User32" () As Long
        
    #Else
        Private Declare Function GetDesktopWindow Lib "user32" () As Long
        Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
    #End If

  8. #8
    Micron is online now Very Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,425
    I forget why, but the VBA version should be more reliable than Win64. Can be hard to remember why to do or not do things when you already have adopted a best practice.
    You could try searching the forum for posts by isladogs that contain "#If VBA7" and see if you can find one where he explains why.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    isladogs's Avatar
    isladogs is offline Access MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    6,205
    There are lots of mistakes in your API declarations in post #7
    1. use #If VBA7 as already mentioned - not #If Win64. All versions of Access since 2010 have VBA7 and the modified API code will work in either bitness
    2. need to convert any Long values in handles/pointers such as hWnd to LongPtr
    3. I've never heard of a LaunchApp API for use with VBA and can find no record of it online...so I've omitted it below.
    However it looks completely wrong as written & its not in the #Else section anyway.
    If you can give me a link for its use, I'll look into it further

    Corrected version:
    Code:
    #If VBA7 Then 'A2010 or later (32/64-bit)    
       Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As        String, ByVal nShowCmd As Long) As LongPtr
        Declare PtrSafe Function GetDesktopWindow Lib "user32" Alias "GetDesktopWindow" () As LongPtr
        
    #Else 'A2007 or earlier
         Declare Function GetDesktopWindow Lib "user32" Alias "GetDesktopWindow" () As Long
        Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
    #End If
    BTW if necessary, I have code to test for the Windows version. For example, this gives e.g. 10.0.19044 on my machine

    Code:
    Function GetOSVersion()
    
    'gets Win version & major build e.g. 10.0.19044
    
    
     Dim localHost       As String
        Dim objWMIService   As Variant
        Dim colOperatingSystems As Variant
        Dim objOperatingSystem As Variant
    
    
        On Error GoTo Err_Handler
    
    
        localHost = "." 'Technically could be run against remote computers, if allowed
        Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & localHost & "\root\cimv2")
        Set colOperatingSystems = objWMIService.ExecQuery("Select * from Win32_OperatingSystem")
    
    
        For Each objOperatingSystem In colOperatingSystems
            GetOSVersion = objOperatingSystem.Version
        Next
    
    
    Exit_Handler:
       ' On Error Resume Next
        Exit Function
    
    
    Err_Handler:
        FormattedMsgBox "Error " & Err.Number & " in GetOSVersion procedure :             " & _
            "@" & Err.Description & "            @", vbCritical, "Program error"
        Resume Exit_Handler
        
    End Function
    Or if you want even more detail, I have another function that will return the full Windows build info
    e.g.. Windows 10 Pro Version 21H2 - Build 10.0.19044.1348 64-bit
    BUT I suspect that's overkill for your needs

    I don't have Windows 11 so I've no idea what either will return but presumably it will start with 11.0.
    Last edited by isladogs; 12-14-2021 at 06:00 PM. Reason: Corrected code error
    Colin Riddington, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I know I don't know, I keep quiet!

  10. #10
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    193
    Hi Micron and Colin

    Thanks for the replies. I'll examine your suggestions and tidy up/correct the code. LaunchApp is a module I have in my DB and is shown below. It's actually something I came across online years ago and has been working fine for me up until now. There is every liklihood that I have not implemented it correctly, but, as I say it has been working. Though, of course, just because it works doesn't mean that it is right.

    Truly appreciat your replies and assistance.

    Code:
    Option Compare Database
    Option Explicit
    
    
    ' Changes to enable compatibility with Office 64bit
    #If Win64 Then
        Declare PtrSafe Function LaunchApp Lib "User32" (ByVal n As LongLong) As Long
        Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
        Private Declare PtrSafe Function GetDesktopWindow Lib "User32" () As Long
        
    #Else
        Private Declare Function GetDesktopWindow Lib "user32" () As Long
        Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
    #End If
    
    
    Const SW_SHOWNORMAL = 1
    Const SE_ERR_FNF = 2&
    Const SE_ERR_PNF = 3&
    Const SE_ERR_ACCESSDENIED = 5&
    Const SE_ERR_OOM = 8&
    Const SE_ERR_DLLNOTFOUND = 32&
    Const SE_ERR_SHARE = 26&
    Const SE_ERR_ASSOCINCOMPLETE = 27&
    Const SE_ERR_DDETIMEOUT = 28&
    Const SE_ERR_DDEFAIL = 29&
    Const SE_ERR_DDEBUSY = 30&
    Const SE_ERR_NOASSOC = 31&
    Const ERROR_BAD_FORMAT = 11&
    
    
    Public Sub OpenNativeApp(ByVal psDocName As String)
    Dim r As Long, Msg As String
    
    
    r = StartDoc(psDocName)
    If r <= 32 Then
        'There was an error
        Select Case r
            Case SE_ERR_FNF
                Msg = "File not found"
            Case SE_ERR_PNF
                Msg = "Path not found"
            Case SE_ERR_ACCESSDENIED
                Msg = "Access denied"
            Case SE_ERR_OOM
                Msg = "Out of memory"
            Case SE_ERR_DLLNOTFOUND
                Msg = "DLL not found"
            Case SE_ERR_SHARE
                Msg = "A sharing violation occurred"
            Case SE_ERR_ASSOCINCOMPLETE
                Msg = "Incomplete or invalid file association"
            Case SE_ERR_DDETIMEOUT
                Msg = "DDE Time out"
            Case SE_ERR_DDEFAIL
                Msg = "DDE transaction failed"
            Case SE_ERR_DDEBUSY
                Msg = "DDE busy"
            Case SE_ERR_NOASSOC
                Msg = "No association for file extension"
            Case ERROR_BAD_FORMAT
                Msg = "Invalid EXE file or error in EXE image"
            Case Else
                Msg = "Unknown error"
        End Select
    '    MsgBox msg
    End If
    End Sub
    
    
    Private Function StartDoc(psDocName As String) As Long
    Dim Scr_hDC As Long
    Scr_hDC = GetDesktopWindow()
    StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:\", SW_SHOWNORMAL)
    End Function

  11. #11
    isladogs's Avatar
    isladogs is offline Access MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    6,205
    Please remove or comment out the LaunchApp line.
    Also, replace your incorrect declarations code with my corrected code which I've checked.

    You will also need to amend the Dim scr_hDC declaration in line with the above

    Code:
    #If VBA7 Then
       Dim Scr_hDC As LongPtr
    #Else
       Dim Scr_hDC As Long
    #End If
    BTW If all your users run A2010 or later you can scrap all conditional compilation and just use the code in each of the #If VBA7 code blocks

    =================
    If your still fails after that the problem is elsewhere in your code.
    Colin Riddington, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I know I don't know, I keep quiet!

  12. #12
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    193
    Hi Colin

    Thank you again for your reply. I'm sorry but I don't quite follow all of your suggestions. I will admit that I am a bit (maybe a lot) out of my depth here, but I'm willing to keep plugging away. Again, I really appreciate your help.


    In your corrected code, unless I am mistaken, you seem to have duplicated the Declare PtrSafe Function

    Code:
    #If VBA7 Then 'A2010 or later (32/64-bit)
       Private Declare PtrSafe Function Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
       Declare PtrSafe Function GetDesktopWindow Lib "User32" () As LongPtr
        
    #Else 'A2007 or earlier
        Declare Function GetDesktopWindow Lib "user32" Alias "GetDesktopWindow" () As Long
        Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
    #End If
    The code below will run correctly, however if I comment out my lines of code, and remove the comments from your code, I recieve a Type mismatch error on this line with ShellExecute highlighted;

    StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:", SW_SHOWNORMAL)


    Code:
    Option Compare Database
    Option Explicit
    
    #If VBA7 Then 'A2010 or later (32/64-bit)
      'Declare PtrSafe Function LaunchApp Lib "User32" (ByVal n As LongLong) As Long
       Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
       Declare PtrSafe Function GetDesktopWindow Lib "User32" () As Long
    #Else 'A2007 or earlier
        Private Declare Function GetDesktopWindow Lib "User32" () As Long
        Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
    #End If
    
    
    '#If VBA7 Then 'A2010 or later (32/64-bit)
    '   Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As LongPtr, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
    '   Declare PtrSafe Function GetDesktopWindow Lib "User32" () As LongPtr
        
    '#Else 'A2007 or earlier
    '    Declare Function GetDesktopWindow Lib "user32" Alias "GetDesktopWindow" () As Long
    '    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
    '#End If
    
    
    Const SW_SHOWNORMAL = 1
    Const SE_ERR_FNF = 2&
    Const SE_ERR_PNF = 3&
    Const SE_ERR_ACCESSDENIED = 5&
    Const SE_ERR_OOM = 8&
    Const SE_ERR_DLLNOTFOUND = 32&
    Const SE_ERR_SHARE = 26&
    Const SE_ERR_ASSOCINCOMPLETE = 27&
    Const SE_ERR_DDETIMEOUT = 28&
    Const SE_ERR_DDEFAIL = 29&
    Const SE_ERR_DDEBUSY = 30&
    Const SE_ERR_NOASSOC = 31&
    Const ERROR_BAD_FORMAT = 11&
    
    
    Public Sub OpenNativeApp(ByVal psDocName As String)
    Dim r As Long, Msg As String
    
    
    r = StartDoc(psDocName)
    If r <= 32 Then
        'There was an error
        Select Case r
            Case SE_ERR_FNF
                Msg = "File not found"
            Case SE_ERR_PNF
                Msg = "Path not found"
            Case SE_ERR_ACCESSDENIED
                Msg = "Access denied"
            Case SE_ERR_OOM
                Msg = "Out of memory"
            Case SE_ERR_DLLNOTFOUND
                Msg = "DLL not found"
            Case SE_ERR_SHARE
                Msg = "A sharing violation occurred"
            Case SE_ERR_ASSOCINCOMPLETE
                Msg = "Incomplete or invalid file association"
            Case SE_ERR_DDETIMEOUT
                Msg = "DDE Time out"
            Case SE_ERR_DDEFAIL
                Msg = "DDE transaction failed"
            Case SE_ERR_DDEBUSY
                Msg = "DDE busy"
            Case SE_ERR_NOASSOC
                Msg = "No association for file extension"
            Case ERROR_BAD_FORMAT
                Msg = "Invalid EXE file or error in EXE image"
            Case Else
                Msg = "Unknown error"
        End Select
    MsgBox Msg
    End If
    End Sub
    
    
    Private Function StartDoc(psDocName As String) As Long
    Dim Scr_hDC As Long
    
    
    '#If VBA7 Then
    '   Dim Scr_hDC As LongPtr
    '#Else
    '   Dim Scr_hDC As Long
    '#End If
    
    
    Scr_hDC = GetDesktopWindow()
    StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:\", SW_SHOWNORMAL)
    End Function

    For reference, the routine is executed from a command button on a form;

    Code:
    Private Sub GoogleMapsBtn_Click()
    Dim stGoogleMap As String
    Dim stURL As String
    
    
    On Error GoTo Err_GoogleMapsBtn_Click
    
    stGoogleMap = "http://maps.google.com/maps?q="
    stURL = stGoogleMap & [Address] & " " & [City] & " " & [PostalCode]
    
    Exit_GoogleMapsBtn_Click:
        Exit Sub
    
    Err_GoogleMapsBtn_Click:
        MsgBox "An unexpected error has prevented this function from launching.", vbCritical + vbOKOnly, "Mapping Error"
        Resume Exit_GoogleMapsBtn_Click
        
    End Sub

  13. #13
    isladogs's Avatar
    isladogs is offline Access MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    6,205
    In your corrected code, unless I am mistaken, you seem to have duplicated the Declare PtrSafe Function
    OOPS! Sorry. Well spotted. Copy & paste error. I've now corrected it in post #9

    The code below will run correctly, however if I comment out my lines of code, and remove the comments from your code, I recieve a Type mismatch error on this line with ShellExecute highlighted;

    StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:", SW_SHOWNORMAL)
    Its getting difficult to follow all your code as I've never used the OpenNativeApp procedure (I have a similar alternative which works for me!)
    I notice that code includes these lines:
    Code:
    Dim r As Long, Msg As String
    r = StartDoc(psDocName)
    ...so to get this working in 64-bit you probably need to do exactly the same conditional compilation with the variable r as I did with Scr_hDC....
    BUT this is getting increasingly messy!

    Suggest you ask whether Ranman has ever converted his OpenNativeApp to 64-bit.
    Or scrap that procedure and use my alternative fHandleFile code that does work in 64-bit(as I've already done the conversion)!

    I've given several examples below on how to use the code

    Code:
    Option Compare DatabaseOption Explicit
    
    'Original code courtesy of Dev Ashish
    '###############################################
    'updated for 64-bit Access by Colin Riddington (Mendip Data Systems) - 06/03/2019
    #If VBA7 Then 'A2010 or later
        Private Declare PtrSafe Function apiShellExecute Lib "shell32.dll" _
            Alias "ShellExecuteA" _
            (ByVal hWnd As LongPtr, _
            ByVal lpOperation As String, _
            ByVal lpFile As String, _
            ByVal lpParameters As String, _
            ByVal lpDirectory As String, _
            ByVal nShowCmd As Long) _
            As Long
    #Else 'A2007 or earlier
        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
    #End If
    '###############################################
    
    Public Const WIN_NORMAL = 1         'Open Normal
    Public Const WIN_MAX = 2            'Open Maximized
    Public Const WIN_MIN = 3            'Open Minimized
    
    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&
    
    Function fHandleFile(stFile As String, lShowHow As Long)
    
    On Error GoTo Err_Handler
    
    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)
                    
    Exit_Handler:
        Exit Function
        
    Err_Handler:
        MsgBox "Error " & Err.number & " in fHandleFile procedure : " & Err.description, vbOKOnly + vbCritical
        Resume Exit_Handler
    
    End Function
    
    '====================================================
    'Now all you need to do is call the application with the path of the file and let Windows do the rest.
    'This code can be used to start any registered applications, including another instance of Access.
    'If it doesn't know what application to open the file with, it just pops up the standard "Open With.." dialog.
    'It can even handle URL's and mailto:
    
    
    'Open a folder:
    '  fHandleFile "C:\Temp\",WIN_NORMAL
    
    'Open a text file
    '  fHandleFile "C:\Temp\test.txt",WIN_NORMAL
    
    'Open an Access db
    '  fHandleFile  "C:\Temp\Database1.accdb",WIN_NORMAL
    
    'Open Email app:
    '  fHandleFile "mailto:bpo@yahoo.com",WIN_NORMAL
    
    'Open URL:
    ' fHandleFile "https://isladogs.co.uk";, WIN_NORMAL
    
    'Handle Unknown extensions:
    ' fHandleFile "C:\TEMP\TestThis",Win_Normal
    '=======================================================================================
    BTW - you didn't answer my question about whether any users are running A2007 or earlier, If not, conditional compilation isn't needed
    Colin Riddington, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I know I don't know, I keep quiet!

  14. #14
    sheusz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2015
    Posts
    193
    Hi Colin

    Thanks again for your reply. You've given me a lot to look at. I think I'll scrap my code and start again, which will take some time (end of year craziness has set in).

    I'm checking through the organisation to see if there are any A2007 or earlier installations in use. I don't think there are, but I need to be sure.

    I'll keep you posted.

  15. #15
    isladogs's Avatar
    isladogs is offline Access MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    6,205
    OK. Good luck
    Colin Riddington, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I know I don't know, I keep quiet!

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

Similar Threads

  1. Hyperlink
    By DMT Dave in forum Access
    Replies: 1
    Last Post: 04-17-2019, 01:35 AM
  2. Hyperlink
    By Froody11 in forum Access
    Replies: 5
    Last Post: 09-24-2018, 02:35 PM
  3. Replies: 4
    Last Post: 01-05-2013, 11:07 AM
  4. Hyperlink
    By gor in forum Access
    Replies: 1
    Last Post: 07-11-2012, 08:37 AM
  5. Replies: 4
    Last Post: 01-31-2011, 03:19 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