By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 3,019 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Hyperlink Not Working, Run-time error 7971

P: 6
Hi,
I'm a newbie here and does not really have any formal training on VBA. I just need help to get my hyperlink to work. I have a form in MS Access with a browse button where a user can select a folder from a specified directory from our network. I was able to get the browse button working where it saves a path that is selected onto the textbox that is set as hyperlink. The problem appears when you click on the hyperlink... it will work a few times and afterwards I get this error message that it cannot follow the hyperlink. If I click the "End" button on the MS VB error message it works again. When I click "Debug" it sends me to the code where FollowHyperlink Me.ReqLink (where Reqlink is the name of my hyperlink box) is highlighted.

Below is my code for the browse button On Click command:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SelFolder_Click()
  2. Me.ReqLink = GetFolder(Me.hWnd, "J:\27 - BSSG\03 - ADMF\13 - Ellipse Change Requests")
  3. End Sub
For the Click command on the Hyperlink:

Expand|Select|Wrap|Line Numbers
  1. Private Sub ReqLink_Click()
  2. FollowHyperlink Me.ReqLink
  3. End Sub
And the code for the Browse Button:


Expand|Select|Wrap|Line Numbers
  1. Private Type BrowseInfo
  2.    hWndOwner As Long
  3.    pIDLRoot As Long
  4.    pszDisplayName As String
  5.    lpszTitle As String
  6.    ulFlags As Long
  7.    lpfnCallback As Long
  8.    lParam As Long
  9.    iImage As Long
  10. End Type
  11.  
  12. Public Const BIF_RETURNONLYFSDIRS = &H1
  13. Public Const BIF_DONTGOBELOWDOMAIN = &H2
  14. Public Const BIF_STATUSTEXT = &H4
  15. Public Const BIF_RETURNFSANCESTORS = &H8
  16. Public Const BIF_EDITBOX = &H10
  17. Public Const BIF_VALIDATE = &H20
  18. Public Const BIF_NEWDIALOGSTYLE = &H40
  19. Public Const BIF_USENEWUI = (BIF_NEWDIALOGSTYLE Or BIF_EDITBOX)
  20. Public Const BIF_BROWSEINCLUDEURLS = &H80
  21. Public Const BIF_UAHINT = &H100
  22. Public Const BIF_NONEWFOLDERBUTTON = &H200
  23. Public Const BIF_NOTRANSLATETARGETS = &H400
  24. Public Const BIF_BROWSEFORCOMPUTER = &H1000
  25. Public Const BIF_BROWSEFORPRINTER = &H2000
  26. Public Const BIF_BROWSEINCLUDEFILES = &H4000
  27. Public Const BIF_SHAREABLE = &H8000
  28. Private Const MAX_PATH = 260
  29. Private Const WM_USER = &H400
  30. Private Const BFFM_INITIALIZED = 1
  31. Private Const BFFM_SELCHANGED = 2
  32. Private Const BFFM_SETSTATUSTEXT = (WM_USER + 100)
  33. Private Const BFFM_SETSELECTION = (WM_USER + 102)
  34.  
  35. Public Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long
  36. Public Declare Function SHBrowseForFolder Lib "shell32.dll" Alias "SHBrowseForFolderA" (lpBrowseInfo As BrowseInfo) As Long
  37. Public Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal pv As Long)
  38. Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long
  39.  
  40. Private mstrSTARTFOLDER As String
  41. Public Function GetFolder(ByVal hWndModal As Long, Optional StartFolder As String = "", Optional Title As String = "Please select a folder:", _
  42.    Optional IncludeFiles As Boolean = False, Optional IncludeNewFolderButton As Boolean = False) As String
  43.     Dim bInf As BrowseInfo
  44.     Dim RetVal As Long
  45.     Dim PathID As Long
  46.     Dim RetPath As String
  47.     Dim Offset As Integer
  48.     'Set the properties of the folder dialog
  49.     bInf.hWndOwner = hWndModal
  50.     bInf.pIDLRoot = 0
  51.     bInf.lpszTitle = Title
  52.     bInf.ulFlags = BIF_RETURNONLYFSDIRS Or BIF_STATUSTEXT
  53.     If IncludeFiles Then bInf.ulFlags = bInf.ulFlags Or BIF_BROWSEINCLUDEFILES
  54.     If IncludeNewFolderButton Then bInf.ulFlags = bInf.ulFlags Or BIF_NEWDIALOGSTYLE
  55.     If StartFolder <> "" Then
  56.        mstrSTARTFOLDER = StartFolder & vbNullChar
  57.        bInf.lpfnCallback = GetAddressofFunction(AddressOf BrowseCallbackProc) 'get address of function.
  58.    End If
  59.     'Show the Browse For Folder dialog
  60.     PathID = SHBrowseForFolder(bInf)
  61.     RetPath = Space$(512)
  62.     RetVal = SHGetPathFromIDList(ByVal PathID, ByVal RetPath)
  63.     If RetVal Then
  64.          'Trim off the null chars ending the path
  65.          'and display the returned folder
  66.          Offset = InStr(RetPath, Chr$(0))
  67.          GetFolder = Left$(RetPath, Offset - 1)
  68.          'Free memory allocated for PIDL
  69.          CoTaskMemFree PathID
  70.     Else
  71.          GetFolder = ""
  72.     End If
  73. End Function
  74. Private Function BrowseCallbackProc(ByVal hWnd As Long, ByVal uMsg As Long, ByVal lp As Long, ByVal pData As Long) As Long
  75.    On Error Resume Next
  76.    Dim lpIDList As Long
  77.    Dim ret As Long
  78.    Dim sBuffer As String
  79.    Select Case uMsg
  80.        Case BFFM_INITIALIZED
  81.            Call SendMessage(hWnd, BFFM_SETSELECTION, 1, mstrSTARTFOLDER)
  82.        Case BFFM_SELCHANGED
  83.            sBuffer = Space(MAX_PATH)
  84.            ret = SHGetPathFromIDList(lp, sBuffer)
  85.            If ret = 1 Then
  86.                Call SendMessage(hWnd, BFFM_SETSTATUSTEXT, 0, sBuffer)
  87.            End If
  88.    End Select
  89.    BrowseCallbackProc = 0
  90. End Function
  91. Private Function GetAddressofFunction(add As Long) As Long
  92.  GetAddressofFunction = add
  93. End Function
*******************
Any help you can provide will be highly appreciated.

Many thanks,
Vspsdca
Aug 22 '08 #1
Share this Question
Share on Google+
6 Replies


ADezii
Expert 5K+
P: 8,638
In my opinion, your code is much too complicated for such a relatively simple task:
  1. Create a Reference to the Microsoft Office XX.X Object Library
  2. Execute the following code whereever appropriate:
    Expand|Select|Wrap|Line Numbers
    1. Dim strFolderName As String
    2. Dim result As Integer
    3.  
    4. With Application.FileDialog(msoFileDialogFolderPicker)
    5.   .Title = "View Folders For Hyperlink"     'Change if necessary
    6.   .AllowMultiSelect = False        
    7.   .ButtonName = "Select Folder"     'Change if necessary
    8.   .InitialFileName = "C:\Windows\System32\"         'Change
    9.   .InitialView = msoFileDialogViewLargeIcons        'Change if necessary
    10.      result = .Show
    11.      If (result <> 0) Then
    12.        strFolderName = Trim(.SelectedItems.Item(1))
    13.         Me![ReqLink] = strFolderName
    14.      Else
    15.        Me![ReqLink] = vbNullString
    16.      End If
    17. End With
  3. Navigate to the Selected Folder:
    Expand|Select|Wrap|Line Numbers
    1. Application.FollowHyperlink Me![ReqLink], , True
  4. Any questions, don't hesitate to ask.
Aug 24 '08 #2

P: 6
Thank you for your reply. Being a newbie I hope you wouldn't mind me asking you a few more questions regarding the code you have provided.

1. Do I replace my module code with the the code below or do i leave the module code as is? Do I put on this the On Click event of my browse button?

Execute the following code whereever appropriate:

Code: ( text )
Dim strFolderName As String
Dim result As Integer

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "View Folders For Hyperlink" 'Change if necessary
.AllowMultiSelect = False
.ButtonName = "Select Folder" 'Change if necessary
.InitialFileName = "C:\Windows\System32\" 'Change
.InitialView = msoFileDialogViewLargeIcons 'Change if necessary
result = .Show
If (result <> 0) Then
strFolderName = Trim(.SelectedItems.Item(1))
Me![ReqLink] = strFolderName
Else
Me![ReqLink] = vbNullString
End If
End With
Navigate to the Selected Folder:

2. Do I put the following code on the On Click event of my hyperlink textbox?

Code: ( text )
Application.FollowHyperlink Me![ReqLink], , True


3. A user ended up pasting a link on the Hyperlink textbox instead of using the browse for folder button, I noticed that when you click on the link the same error occurs. Is it because the code does not accept a link that did not come from the browse for folder button?

Thanks.
Sep 8 '08 #3

ADezii
Expert 5K+
P: 8,638
In my opinion, your code is much too complicated for such a relatively simple task:
  1. Create a Reference to the Microsoft Office XX.X Object Library
  2. Execute the following code whereever appropriate:
    Expand|Select|Wrap|Line Numbers
    1. Dim strFolderName As String
    2. Dim result As Integer
    3.  
    4. With Application.FileDialog(msoFileDialogFolderPicker)
    5.   .Title = "View Folders For Hyperlink"     'Change if necessary
    6.   .AllowMultiSelect = False        
    7.   .ButtonName = "Select Folder"     'Change if necessary
    8.   .InitialFileName = "C:\Windows\System32\"         'Change
    9.   .InitialView = msoFileDialogViewLargeIcons        'Change if necessary
    10.      result = .Show
    11.      If (result <> 0) Then
    12.        strFolderName = Trim(.SelectedItems.Item(1))
    13.         Me![ReqLink] = strFolderName
    14.      Else
    15.        Me![ReqLink] = vbNullString
    16.      End If
    17. End With
  3. Navigate to the Selected Folder:
    Expand|Select|Wrap|Line Numbers
    1. Application.FollowHyperlink Me![ReqLink], , True
  4. Any questions, don't hesitate to ask.
Just subscribing for now, will check back later when I have the time.
Sep 8 '08 #4

ADezii
Expert 5K+
P: 8,638
Thank you for your reply. Being a newbie I hope you wouldn't mind me asking you a few more questions regarding the code you have provided.

1. Do I replace my module code with the the code below or do i leave the module code as is? Do I put on this the On Click event of my browse button?

Execute the following code whereever appropriate:

Code: ( text )
Dim strFolderName As String
Dim result As Integer

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "View Folders For Hyperlink" 'Change if necessary
.AllowMultiSelect = False
.ButtonName = "Select Folder" 'Change if necessary
.InitialFileName = "C:\Windows\System32\" 'Change
.InitialView = msoFileDialogViewLargeIcons 'Change if necessary
result = .Show
If (result <> 0) Then
strFolderName = Trim(.SelectedItems.Item(1))
Me![ReqLink] = strFolderName
Else
Me![ReqLink] = vbNullString
End If
End With
Navigate to the Selected Folder:

2. Do I put the following code on the On Click event of my hyperlink textbox?

Code: ( text )
Application.FollowHyperlink Me![ReqLink], , True


3. A user ended up pasting a link on the Hyperlink textbox instead of using the browse for folder button, I noticed that when you click on the link the same error occurs. Is it because the code does not accept a link that did not come from the browse for folder button?
1. Do I replace my module code with the the code below or do i leave the module code as is? Do I put on this the On Click event of my browse button?
There has been a slight code modification, replace your code with the following, and yes it can go in the Click() Event of your Browse Button.
Expand|Select|Wrap|Line Numbers
  1. Dim strFolderName As String
  2. Dim result As Integer
  3.  
  4. With Application.FileDialog(msoFileDialogFolderPicker)
  5.   .Title = "View Folders For Hyperlink"     'Change if necessary
  6.   .AllowMultiSelect = False
  7.   .ButtonName = "Select Folder"     'Change if necessary
  8.   .InitialFileName = "C:\Windows\System32\"         'Change
  9.   .InitialView = msoFileDialogViewLargeIcons        'Change if necessary
  10.      result = .Show
  11.      If (result <> 0) Then
  12.        strFolderName = Trim(.SelectedItems.Item(1))
  13.         Me![ReqLink] = strFolderName & "#" & strFolderName
  14.      Else
  15.        Me![ReqLink] = vbNullString
  16.      End If
  17. End With
2. Do I put the following code on the On Click event of my hyperlink textbox?
No code is needed to Navigate via the Hyperlink, as long as the Control Source for the Text Box (ReqLinq) is the HyperLink Data Type. This functionality is self contained within the Data Type, so a single Click will do the trick.
3. A user ended up pasting a link on the Hyperlink textbox instead of using the browse for folder button, I noticed that when you click on the link the same error occurs. Is it because the code does not accept a link that did not come from the browse for folder button?
You can manually enter a Hyperlink Path in any manner you wish, as long as it is valid, you can Navigate to it. You must, however, move off the Field then re-enter it for the Hyperlink to work.
Sep 8 '08 #5

P: 6
Thanks so much!!!! It finally worked!
Sep 18 '08 #6

ADezii
Expert 5K+
P: 8,638
Thanks so much!!!! It finally worked!
You are quite welcome.
Sep 18 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.