Access (2003) VBA solution needed to allow the user, using a form, to insert a hyperlink for a file of their choosing, into a record. Also, need to start from a default directory.

Hello Experts. I had a very useful little send-keys solution which allowed the user to insert a hyperlink, for a file of their choosing, into a record. The user, seeing the record fields as displayed on a form, would click on a button, titled “Add Link”. Upon doing so, the focus would go to the hyperlink field and the “send-keys” actions would navigate the user to the proper directory where the files were located. When I converted the database to Access 2003, the send-keys have simply refused to work consistently. I have tweaked them to fit the navigation changes in the dialog box, however, the code does not consistently work.

In all honesty, I’d like to get out of using the send-keys altogether.

Here’s the VBA that I had in the Access 97 database:

Private Sub cmdAddLink_Click()

Me.path.SetFocus    'Sets the focus to the path field on subform
SendKeys "%II"      'Brings up the Hyperlink Menu Option and then brings up “Insert”
SendKeys "{DOWN}"  
SendKeys "I"
SendKeys "{Tab}"
SendKeys "{ENTER}"
SendKeys "\\jefferson\images\active"  'Enters the path in the dialog box
SendKeys "{ENTER}"
SendKeys "{Tab}"
SendKeys "A"
SendKeys "{ENTER}"

Exit Sub
End Sub

. . .  from this point, the user takes over and selects the image of their choosing.

Does anyone have a good way to do this without using Send-Keys?
Who is Participating?
Leigh PurvisDatabase DeveloperCommented:
Is it not just something *like*
Me.YourHyperField = "active#\\jefferson\images\active#"
Leigh PurvisDatabase DeveloperCommented:
You'd have to select the file somehow of course - I'd recommend...
dataman2004Author Commented:
Me.YourHyperField = "active#\\jefferson\images\active#"

I'm not finding "YourHyperField" to be a VBA method.

I can get the Dialog opened with "acCmdEditHyperlink"

DoCmd.GoToControl "path"
DoCmd.RunCommand acCmdEditHyperlink

But, getting the path inserted into the dialog box is the trick. Is there not a way to easily/cleanly insert the path into the "Address" field in the Edit Hyperlink Dialog Box?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Private Sub Command268_Click()
On Error GoTo Err_Command268_Click
Dim FilePathName As String
Dim FileName As String
Dim result As Integer
Dim NewDirPath As String
Dim myfso As Scripting.FileSystemObject
Dim db
Dim rs
Dim hyplink As Long
Dim txt
Dim rwsc
Set myfso = New Scripting.FileSystemObject
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAttachments")

NewDirPath = "\\g4\DRMA\Damien\DRMA_FILES\REQ" & Me.RequestID & "\"
'///////FROM HERE TO
    With Application.FileDialog(msoFileDialogFilePicker)
        .Title = "Select File To Attach"
        .AllowMultiSelect = False
        .InitialFileName = CurrentProject.path
        result = .Show
        If (result = 0) Then
        MsgBox "No File Selected"
        Exit Sub
        FilePathName = Trim(.SelectedItems.Item(1))
        FileName = Right(FilePathName, Len(FilePathName) - InStrRev(FilePathName, "\", -1))
        End If
      End With
 '//////Opens a window save file to select the given file
If Len(Dir(NewDirPath)) > 0 Then
End If

'create request directory
MkDir NewDirPath

'Copy file to our directory
myfso.CopyFile FilePathName, NewDirPath

DoCmd.OpenForm "frmAttachmentsSub", acNormal, , , , acDialog

'Add Record to table
rs.file = FileName & "#" & NewDirPath & FileName & "#"                                 'This field file is a hyperlink field
rs.FILE_NAME = FileName
rs.FILE_ADDED_BY = strLoggedInUserName
rs.FILE_TYPE = Forms!frmAttachmentsSub.Combo8
rs.PROPOSAL_ID = Me.Combo295
DoCmd.Close acForm, "frmAttachmentsSub"

    Exit Sub

    MsgBox Err.Description
    Resume Exit_Command268_Click
End Sub

dataman2004Author Commented:
LPurvis, although daunting looking, the code seems to work well, however my last hurdle is getting the hyperlink inserted into the "path" field on the form, (The actual txtbox control is named "path")

Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
    intPos = InStr(strItem, vbNullChar)
    If intPos > 0 Then
        TrimNull = Left(strItem, intPos - 1)
        Forms![frmParent]![subfrm_frmChild].Form![path] = TrimNull
        TrimNull = strItem
    End If
End Function

With this code, I am able to insert the entire path string into the "path" textbox, but this is not apparently inderting the actual underlying hyperlink, the string of which is the same. Can you tell me where I am going wrong?

Leigh PurvisDatabase DeveloperCommented:
You've had a more explicit version of my suggestion offered.

Rather than me explain further to you - I'd suggest you just take the easy way and look at that.
dataman2004Author Commented:
Thanks for the advice. I'll take a closer look.
dataman2004Author Commented:
OK, this seems to work well.

        Forms![frmParent]![subfrmChild].Form![path] = TrimNull & "#" & TrimNull & "#" 'Hyperlink string to go into Access Table's Hyperlink field

Note: I've seen this around EE before( ), but was not convinced that it was the best way to bring up the dialog box ( no, I am no expert ). On the recommendation above, I pursued it and found it to work very well.

Thanks for your help with the hyperlink, and for the recommendation of the API to call the standard Windows File Open/Save dialog box.
Leigh PurvisDatabase DeveloperCommented:
No problem at all.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.