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.

Posted on 2006-03-27
Last Modified: 2008-03-17
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?
Question by:dataman2004
    LVL 44

    Accepted Solution

    Is it not just something *like*
    Me.YourHyperField = "active#\\jefferson\images\active#"
    LVL 44

    Expert Comment

    by:Leigh Purvis
    You'd have to select the file somehow of course - I'd recommend...

    Author Comment

    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?
    LVL 1

    Expert Comment

    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
    GoTo COPY
    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_ADD_DATE = Now
    rs.FILE_ADDED_BY = strLoggedInUserName
    rs.FILE_DESCRIPTION = Forms!frmAttachmentsSub.FILE_DESCRIPTION
    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


    Author Comment

    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?

    LVL 44

    Expert Comment

    by:Leigh Purvis
    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.

    Author Comment

    Thanks for the advice. I'll take a closer look.

    Author Comment

    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.
    LVL 44

    Expert Comment

    by:Leigh Purvis
    No problem at all.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now