VBA MS Access 2007 hyperlink insert button

I have a button which inserts a hyperlink into a new record. The field's IsHyperlink property is set to "yes", so I get the hand, but clicking on the inserted path does not go anywhere. I believe the button is updating the record with the path of the file as "text to display" rather than "address."

   Private Sub MSDS_btn_Click()
   Dim fd As Office.FileDialog
'Create a FileDialog object as a File Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFilePicker)
'Use a With...End With block to reference the FileDialog object.
   With fd
'Set the initial path to the D:\Documents\ folder.
   .InitialFileName = "D:\Documents\"
   .Title = "Select MSDS"
'Use the Show method to display the File Picker dialog box and return the user's action.
'If the user presses the action button...
   If .Show = -1 Then
   DoCmd.GoToRecord , "", acNewRec
   Me![Link MSDS] = .SelectedItems(1)

  **

'If the user presses Cancel...
   Else
   End If
 End With

'Set the object variable to Nothing.
   Set fd = Nothing
   End Sub

I know that putting the following code in at the ** works in Excel, I am after something like it which will work in Access!

 ActiveSheet.Hyperlinks.Add Anchor:=Cells(ActiveCell.row, Range("LinkCol").Column), Address:=.SelectedItems(1), T开发者_如何学GoextToDisplay:="MSDS"

Try this if you want the file path as both the hyperlink address and display text.

Me![Link MSDS] = "#" & .SelectedItems(1) & "#"

If you want the address with only the file name (without the path) as the display text, try this:

Me![Link MSDS] = Dir(.SelectedItems(1)) & "#" & .SelectedItems(1) & "#"

See HyperlinkPart Method for more background information. You might even prefer to manipulate your hyperlink field data using HyperlinkPart.