Solved

Inserting "folder" location in Hyperlink field

Posted on 2014-02-21
5
440 Views
Last Modified: 2014-02-22
Hi,

I have a hyperlink field in my form.

I want my users to
identify an excel spreadsheet in a specific folder and then

"paste" a link to that spreadsheet into my access form.


I want this to be a simply cut and paste.

Question: How can I CUT a hyperlink that hold my folder path AND my file name.


The end result should be something like;
C:\mydocuments\myExcel\Personal\Bankrec.xls

Note that I am already able to "cut" the folder path ...... but I am finding that I need to KEY in the actual file name.  I.e. I need to key in the "bankrec.xls" .... How do I avoid keying in the filename
0
Comment
Question by:Patrick O'Dea
  • 3
  • 2
5 Comments
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
ID: 39878895
You can do that with vba. The code is as follows:-

Function SelectFile() As String
      
        On Error GoTo ExitSelectFile
          
        Dim objFileDialog    As Object
        Set objFileDialog = Application.FileDialog(1)
          
        With objFileDialog
          
            .AllowMultiSelect = False
            .Show
               
            Dim varSelectedItem As Variant
      
            For Each varSelectedItem In .SelectedItems
      
                SelectFile = varSelectedItem
               
            Next varSelectedItem
                  
       End With
         
    ExitSelectFile:
      
    Set objFileDialog = Nothing
      
End Function

Open in new window


This will open the dialog box and you can select a file from any folder.

I have attached an example. Open the form and click the button to see how it works
dbselectfile.accdb
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 39879158
Purrfect!
0
 

Author Comment

by:Patrick O'Dea
ID: 39879174
Actually,

Can I re-open this question??

This link insert fine into the table BUT .... it does not open when I click it in the form..??

Perhaps, the link should be enclosed in quote?? How do I do this?

Thanks
0
 
LVL 16

Expert Comment

by:Sheils
ID: 39879801
Create a new module and paste in the following code:

Private Declare Function ShellExecuteA Lib "shell32.dll" _
(ByVal hWnd As Long, _
ByVal strOperation As String, _
ByVal strFile As String, _
ByVal strParameters As String, _
ByVal strDirectory As String, _
ByVal nShowCmd As Long) As Long
 
Private Declare Function GetDesktopWindow Lib "user32" () As Long
____________________________________________________________
 
Function OpenFile(strFilePath)
  
    lngreturn = ShellExecuteA(GetDesktopWindow(), "OPEN", strFilePath, "", "", vbNormalFocus)
  
    If Not ((lngreturn < 0) Or (lngreturn > 32)) Then
       
        MsgBox "Sorry, There was a problem opening the selected file", vbExclamation
  
    End If
  
End Function

Open in new window


Then in the form textbox on double click even add the following:

OpenFile(Me.textboxname)
0
 

Author Comment

by:Patrick O'Dea
ID: 39879842
Thanks very much!

I got it now.  Very clever!
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ms/access ftp / SFTP 3 32
strange textbox behavior MS Access 9 34
Contents of subform based upon combobox value 3 24
Run Time Error 3071 26 36
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 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

16 Experts available now in Live!

Get 1:1 Help Now