Hyperlink on a Form

[Emma35]

Hi Guys,


I'd appreciate your help on a little problem. On a form i would like to have a text box with a command button beside it, which when activated, allows the user to select a file from a shared drive and a hyperlink to this file would then appear in the text box. Is this possible ?

Thanks,
Em

[June7]

Are you trying to save a hyperlink to a hyperlink field?

For a better understanding hyperlinks, review
http://www.allenbrowne.com/casu-09.html
http://allenbrowne.com/func-GoHyperlink.html

What you want will probably require opening file dialog and saving the path\name of selected file.

[Emma35]

Thanks June,
I'd like to allow the user to select a file using the command button and then the hyperlink to that file will be displayed in the text box. Any other users viewing the form will then be able to open the file by clicking on the hyperlink.

[June7]

Didn't answer question. Do you want to save to hyperlink type field? or just a text field?

Do you need to know how to use file dialog object? http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[Emma35]

My apologies....yes i'd like to save the link in a hyperlink field. I know you can just create a hyperlink field and then right click and go Edit Hyperlink but i want to make it foolproof for users who know nothing about Access.

Thanks again

[June7]

Use the file dialog (in file picker mode) to allow user to select file and code constructs the selected path\filename as a hyperlink and saves in the field. Or save the path\filename as normal text in text field and use Allen Browne's GoHyperlink function.

No such thing as foolproof.

Attempt code and when you encounter specific issue, post question.

[Emma35]

Hi....sorry for the delay in replying i've been off work sick for a couple of weeks. Just back today.
I've been looking at the GoHyperlink instructions but am not sure how to incorporate them into the database. It says that using this method will open the file but i just want to put the address of the file into the hyperlink field. Also it mentions that you must put the file name into the code, but i want to allow the user to choose a random file. Hope this makes sense

Thanks again

[June7]

You have two options for saving string into hyperlink field.

1. use the Edit Hyperlink dialog

2. use file dialog object to select file and code to construct the hyperlink string - the string must be structured as shown in the first Allen Browne link

Alternative is to save the file path/name selected with the file dialog into a regular text field and use GoHyperlink to open the file.

[Emma35]

Alright i've managed to get a file dialog box working which enters the file path into a list box. How do i get the GoHyperlink function to work ?....Allen Browne's instructions say to set up a module but where do i put the code

 Call GoHyperlink(CurDir & "\MyDoc.doc")

??

Cheers

[June7]

Call the function from an event procedure, possiby the same procedure that gets the file path\name with the file dialog.

Post your file dialog code.


Why a list box?

[Emma35]

I've got the code below in the OnClick event of the command button. There was no particular reason for a list box....i couldn't get Allen Browne's code to work so i found another example elsewhere and gave it a try. I don't know how to change the code if i put a regular text box in instead.

Option Compare Database
Option Explicit

Private Sub cmdFileDialog_Click()

Dim fDialog As Office.FileDialog
Dim varFile As Variant
' Clear the list box contents.
Me.FileList.RowSource = ""
' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to make multiple selections in the dialog box.
.AllowMultiSelect = True

' Set the title of the dialog box.
.Title = "Select One or More Files"
' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Access Databases", "*.MDB"
.Filters.Add "Access Projects", "*.ADP"
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
' Loop through each file that is selected and then add it to the list box.
For Each varFile In .SelectedItems
Me.FileList.AddItem varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub

[June7]

The code you show allows selection of multiple files and adds them as items in a listbox. It doesn't create hyperlink nor open the file nor save file name to table.

Exactly what do you want to happen? Do you want:

1. only 1 file selected
2. save path\filename string to text field
3. open the file in its native program

[Emma35]

The code you show allows selection of multiple files and adds them as items in a listbox. It doesn't create hyperlink nor open the file nor save file name to table.

Exactly what do you want to happen? Do you want:

1. only 1 file selected
Yes ideally just one file

2. save path\filename string to text field
Yes i'd like to save the path and filename in the table but i'd also like it to work as a hyperlink when in Form View

3. open the file in its native program
Yes

I really didn't want a List Box but it was the only code i could find. I'm trying to use the command button to select a file, whose path/filename will be displayed on the form and can be clicked to open the file in question

Thanks again

[June7]

Try:

Option Compare Database
Option Explicit

Private Sub cmdFileDialog_Click()

Dim fDialog As Office.FileDialog
Dim varFile As Variant
' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to make one selection in the dialog box.
.AllowMultiSelect = False
' Set the title of the dialog box.
.Title = "Select One File"
' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Access Databases", "*.MDB"
.Filters.Add "Access Projects", "*.ADP"
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
   ' Loop through each file that is selected and then add it to the text box.
   For Each varFile In .SelectedItems
      Me.textboxname = varFile
   Next
   GoHyperlink(varFile)
Else
   MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub

[Emma35]

Ok thanks again for your patience with this. I've added the new code and the file dialog box pops up and allows me to select a file. But when i click OK, no file path appears in the text box. Here's the code i have now

Private Sub cmdFileDialog_Click()
Dim fDialog As Office.FileDialog
Dim varFile As Variant
' Set up the File dialog box.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
' Allow the user to make one selection in the dialog box.
.AllowMultiSelect = False
' Set the title of the dialog box.
.Title = "Select One File"
' Clear out the current filters, and then add your own.
.Filters.Clear
.Filters.Add "Access Databases", "*.MDB"
.Filters.Add "Access Projects", "*.ADP"
.Filters.Add "All Files", "*.*"
' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then
   ' Loop through each file that is selected and then add it to the text box.
   For Each varFile In .SelectedItems
      Me.txtFile = varFile
   Next
   GoHyperlink (varFile)
Else
   MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub