Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 601
  • Last Modified:

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?
0
dataman2004
Asked:
dataman2004
  • 4
  • 4
1 Solution
 
Leigh PurvisDatabase DeveloperCommented:
Is it not just something *like*
Me.YourHyperField = "active#\\jefferson\images\active#"
0
 
Leigh PurvisDatabase DeveloperCommented:
You'd have to select the file somehow of course - I'd recommend...

http://www.mvps.org/access/api/api0001.htm
0
 
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?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DYOUNG1006Commented:
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
        Else
        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
Else
GoTo CREATE
End If

CREATE:
'create request directory
MkDir NewDirPath

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

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

'Add Record to table
rs.AddNew
rs.REQUEST_ID = Me.REQUEST_ID
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
rs.Update
rs.Close
 
DoCmd.Close acForm, "frmAttachmentsSub"
Me.Refresh

Exit_Command268_Click:
    Exit Sub

Err_Command268_Click:
    MsgBox Err.Description
    Resume Exit_Command268_Click
End Sub

ANY QUESTIONS SHOOT ME AN EMAIL
0
 
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
    Else
        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?

0
 
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.
0
 
dataman2004Author Commented:
Thanks for the advice. I'll take a closer look.
0
 
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( http://www.mvps.org/access/api/api0001.htm ), 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.
0
 
Leigh PurvisDatabase DeveloperCommented:
No problem at all.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now