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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leigh PurvisDatabase DeveloperCommented:
Is it not just something *like*
Me.YourHyperField = "active#\\jefferson\images\active#"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.