Is it possible to attach (VBA) code to a hyperlink field in an Access Table

[Phil Davis]

Hi, I have an Access table containing 160 rows. Each row contains a hyperlink to an Excel file whose name is derived from the primary key of the record.

The Excel files initially do not exist but will be created by copying a master file.

Question is, can I attach code to the click event on each hyperlink so that I can determine whether the target file needs to be created by copying in the master file?

Many thanks in anticipation
Phil

[Stewart Ross]

Hi Phil. If you create a form and access the hyperlink field through a control on the form you will have access to the address contained in the hyperlink, and to the control's events - including On Enter, On Click, On Got Focus, On Lost Focus, and so on.

You'd need to do some testing to see which of the control's events, if any, is the most suitable for you, as it is possible to shift focus to the control in which you have the hyperlink without executing the hyperlink contained within it (for example, by tabbing into the field from another control).

the most likely candidate is probably the On Enter event, which is likely to occur before the hyperlink contained in the control is followed.

You can trigger the Hyperlink's Follow event from code, but of course you will have to have set up your file first before you do so. Whatever you do, you'll have to do it before the Follow event is triggered, because you can't intercept the Follow event itself as far as I know.

-Stewart

[ADezii]

I'm not a hugh fan of Hyperlinks in general, so what I do is:

1. Store the Absolute Path to a File in a TEXT Field within a Table.
2. Have a Field (Text Box) on a Form Bound to this Field in the Table.
3. Set the Forecolor to Blue and Underlined to True.
4. In the Click() Event of the Text Box, you can insert Code that will Validate the Path to the File and either Open it as a Hyperlink, or provide an Error Message to the User, as in:
   

   Private Sub Hyper_Click()
     If Dir$(Me![Hyper]) <> "" Then
       Application.FollowHyperlink Me![Hyper], , True
     Else
       MsgBox "Cannot Navigate to Hyperlink!", vbExclamation, "Hyperlink Error"
     End If
   End Sub

5. The disadvantage to this Method is that you won't get the Itty Bitty Hand, but you can perform some special effects in the MouseMove() Event of the Control.