Drop feature of the Hyperlink box not working on form in MS-Access

[AviT]

To give an idea of what I'm trying to achieve, I've placed the sample code (and how to try it - very easy)...

I have a MS Access form that is bound to a query in the database. I added a textbox and added a row source to a Hyperlink datafield in the query. When I open the form, it won't allow me to drop a file into the textbox.

Here's the deal. When I follow this EXACT same process on a new form, it works perfectly. That is, I open a blank form and I bind the form to some new query, let's say qryNewIdeas. Then on the blank form i add a textbox, let's say Text0. Then, when I select Text0, I go to the data tab - row source and choose the dropdown arrow to select the hyperlink field, lets say NewIdeasAddress. I click SAVE and open the form. It works PERFECTLY.

BUT, when I repeat this on an existing form (the one where I need the thing to actually work), it does nothing. It adds and changes the textbox to a hyperlink box (I note this because of the blue underlined word that appears in the box). But it won't allow me to drop files to this location.

I tried commenting out ALL of my VBA code to try to make sure that something wasn't hindering it (say in an OnLoad event) AND I've checked all of the form and control box properties and they seem to be the same in both the existing form and the test form.

I can't determine why it works on one form and not the other; Solution needed. OR Is there some better way to create the DragNDrop simulation where my users can drop a file from their desktop on to my ms-access form and I retrieve the file path?

'Citation Record:
 
'Kreszch68. (2012, January 12).
'Re: Re: Drag And Drop File VBA - on MrExcel.com [Web log comment].
'Retrieved April 9, 2016,
'from http://www.mrexcel.com/forum/microsoft-access/408038-drag-drop-file-visual-basic-applications.html
 
'**** HOW TO TRY THIS EXAMPLE
'create a simple table with just two fields, FileHyperlink (type = hyperlink) and FilePath(type = text). 
'Then create a form and place the two fields on the form. Copy this code to the form code.
'
 
Option Compare Database
Option Explicit
 
Private Sub FileHyperLink_AfterUpdate()
Dim hlink As HyperlinkMe.FileHyperLink.Value = RelativeToAbsoluteHyperlink(Me.FileHyperLink.Value)
Set hlink = Me.FileHyperLink.Hyperlink
Me.FilePath.Value = hlink.Address
Me.FileHyperLink.Value = vbNullString
DoCmd.RunCommand acCmdSaveRecord
End Sub
Function ExtractDirName(strPathName As String, Optional strDelimiter As String = "\") As String
  Dim intIndex As Integer
  For intIndex = VBA.Len(strPathName) To 1 Step -1
    If Mid(strPathName, intIndex, 1) = strDelimiter Then Exit For
  Next
  If intIndex <= 1 Then
    ExtractDirName = ""
  Else
    ExtractDirName = VBA.Left(strPathName, intIndex - 1)
  End If
End Function
Function ExtractFileName(strPathName As String, Optional strDelimiter As String = "\") As String
  Dim intIndex As Integer
  For intIndex = VBA.Len(strPathName) To 1 Step -1
    If Mid(strPathName, intIndex, 1) = strDelimiter Then Exit For
  Next
  ExtractFileName = VBA.Right(strPathName, VBA.Len(strPathName) - intIndex)
End Function
Function RelativeToAbsoluteHyperlink(strHyperlink As String) As String
  Dim strTemp() As String
  Dim intIndex As Integer
  Dim strResult As String
  If Nz(strHyperlink, "") <> "" Then
    strTemp() = Split(strHyperlink, "#", , vbTextCompare)
    For intIndex = LBound(strTemp) To UBound(strTemp)
      If Len(strTemp(intIndex)) > 0 Then
        If Left(strTemp(intIndex), 2) = ".." Then
          strTemp(intIndex) = Replace(strTemp(intIndex), "/", "\")
        End If
        strTemp(intIndex) = RelativeToAbsolutePath(strTemp(intIndex))
      '  Debug.Print strTemp(intIndex)
      End If
      If intIndex = LBound(strTemp) Then
        strResult = strTemp(intIndex)
      Else
        strResult = strResult & "#" & strTemp(intIndex)
      End If
    Next
  End If
  RelativeToAbsoluteHyperlink = strResult
End Function
Function RelativeToAbsolutePath(strRelativePath As String, _
  Optional strStartPath As String = "", _
  Optional strDelimiter As String = "\") As String
 
  Dim intCount As Integer
  Dim intIndex As Integer
  Dim intIndex2 As Integer
 
  Dim strFileName As String
  Dim strPathName As String
  Dim strResult As String
  Dim strSplit() As String
  Dim strSplit2() As String
  Dim strTemp As String
 
  If strStartPath = "" Then
    strStartPath = Application.CurrentProject.Path
  End If
  If (Left(strRelativePath, 2) = "\\") Or _
    (Mid(strRelativePath, 2, 1) = ":") Or _
    (Left(strRelativePath, 5) = "http:") Or _
    (Left(strRelativePath, 6) = "https:") Or _
    (Left(strRelativePath, 4) = "ftp:") Or _
    (Left(strRelativePath, 7) = "mailto:") Or _
    (Left(strRelativePath, 7) = "callto:") Then
    'Path is already absolute
    RelativeToAbsolutePath = strRelativePath
    Exit Function
  End If
 
  strPathName = ExtractDirName(strRelativePath, strDelimiter)
  strFileName = ExtractFileName(strRelativePath, strDelimiter)
  If Left(strPathName, 2) = ".." Then
    'Go up
    intCount = 0
    strSplit() = Split(strPathName, strDelimiter, -1, vbTextCompare)
    strSplit2() = Split(strStartPath, strDelimiter, -1, vbTextCompare)
    For intIndex = 0 To UBound(strSplit())
      If strSplit(intIndex) = ".." Then
        intCount = intCount + 1
        strResult = ""
        For intIndex2 = 0 To UBound(strSplit2()) - intCount
          If strResult <> "" Then
            strResult = strResult & strDelimiter
          End If
          strResult = strResult & strSplit2(intIndex2)
        Next
      Else
        If strResult <> "" Then
          strResult = strResult & strDelimiter
        End If
        strResult = strResult & strSplit(intIndex)
      End If
    Next
    strResult = strResult & strDelimiter & strFileName
  Else
    strResult = strRelativePath
  End If
 
  RelativeToAbsolutePath = strResult
End Function

[zmbd]

Would you please post the SQL for the query the form is bound to?

Please enclose the SQL in the same [CODE/] format that you used for the VBA.

thnx
-z