Solved: Creating Hyperlinks in Access VBA

[f9073341]

Hi Guys,

I have a form which contains a number of text fields. Next to each text field I have a button labelled 'Make Link'. I want to be able to click this button and, using a dialogue box, select a file so that the value in the corresponding text box becomes hyperlinked to that file choice.

Everytime I open this form, the text boxes which have been 'hyperlinked' will remain hyperlinked.

Any ideas on the best way to do this?

Many Thanks.

[OBP]

If you are just using Links to Files all you need is a Field in the Table that will hold the Full Path to the File and then use the FollowHyperlink method to open the File when required.
I have a Document Management database that uses that process.

[f9073341]

Ok, that's fine. But how do I set the hyperlink in the first place? Ideally I'd like to initiate a file dialogue box so that the user can select the location of the document... then store the location of that selection - but it seems like it could be pretty tricky... what d'ya think?

[OBP]

If you would like to Private mail me your email address I will send you a copy of the database and you can copy the Forms and VBA out of that, it is too large to post on here.

[CreganTur]

If you want to use a FileDialog box, you can use this code:

Dim strFilepath As String
Dim dlgOpen As Office.FileDialog
Dim vrtSelectedItem As Variant
Dim strFilePathAs String

'select workbooks using file dialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen '<<<Opens file dialog window
.AllowMultiSelect = False '<<<User can select multiple files | False for single file
.Title = "Please select file to load" '<<<Title text for window
.Filters.Clear '<<<Removes any old File Dialog filters
.Filters.Add "Excel Files", "*.XLS, *.CSV" '<<<sets filter to Excel files

If .Show = 0 Then '<<< if User presses Cancel then Sub ends
Exit Sub
Else
'Will loop through all files selected by the Dialog window
For Each vrtSelectedItem In .SelectedItems
strFilepath = vrtSelectedItem '<<<Set filepath to Variable
Next
End If
End With

It's currently setup to allow a single file selection, but you can change it to allow multi-select; check the code comments.

The chosen filepath will be added to the strFilePath variable.

HTH:thumb

[f9073341]

Hi CreganTur, this looks like a simple piece of code... if only it worked! When I try to compile the code it says "User defined type not defined" and points to the line:

Dim dlgOpen As Office.FileDialog


Do I need to install a library of some sort? This system will also be deployed on an Access2003 system, is that a problem?

Many Thanks

[OBP]

Yes, you need the MS Office 10 or 11 library Reference for the VBA.
I show the References you need for the Document Management Database on an Excel worksheet

[CreganTur]

:doh: I keep forgetting to tell people what reference to use...

Thanks Tony.

[f9073341]

Yes, you need the MS Office 10 or 11 library Reference for the VBA.
I show the References you need for the Document Management Database on an Excel worksheet

Thanks for the e-mail OBP. I've noticed that the library you pointed to "fpdtc.dll" is not contained in Access 2007?

[OBP]

That is why in the Excel sheet I show where it is stored so that you can see if it is there and Add it to the Access List.

[OBP]

You can download that .dll if you need it.

[f9073341]

If you want to use a FileDialog box, you can use this code:

Dim strFilepath As String
Dim dlgOpen As Office.FileDialog
Dim vrtSelectedItem As Variant
Dim strFilePathAs String

'select workbooks using file dialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen '<<<Opens file dialog window
.AllowMultiSelect = False '<<<User can select multiple files | False for single file
.Title = "Please select file to load" '<<<Title text for window
.Filters.Clear '<<<Removes any old File Dialog filters
.Filters.Add "Excel Files", "*.XLS, *.CSV" '<<<sets filter to Excel files

If .Show = 0 Then '<<< if User presses Cancel then Sub ends
Exit Sub
Else
'Will loop through all files selected by the Dialog window
For Each vrtSelectedItem In .SelectedItems
strFilepath = vrtSelectedItem '<<<Set filepath to Variable
Next
End If
End With

It's currently setup to allow a single file selection, but you can change it to allow multi-select; check the code comments.

The chosen filepath will be added to the strFilePath variable.

HTH:thumb

Guys, whatever I try, it keeps coming up with "Compile Error: User-Defined type not defined". I've pointed to the reference fpdtc.dll and i've even tried downloading another version of it and browsing for it... it still doesn't work!! Ahhhh!!

One thing I do notice is that intellisense does not initiate when i put the full-stop(period) after "office" on the "dlgOpen" declaration. Is that an indication of the problem?

[f9073341]

Got it guys... I overlooked the "Microsoft Office 11.0 Object Library".

Many Thanks.