Search code examples
ms-accesshyperlinksubform

MS Access 365 - Create SubForm Containing Linked Files

I've created a table and form to track sales opportunities. I'd like users to be able to "connect" associated files on our server to each opportunity. For example, they might like the opportunity to point to price quote.

Since attaching files to the database is a debatable move, I've opt to save the path and would like to use FollowHyperlink to navigate and open the file.

My strategy is to create a subform containing the links associated with a particular opportunity. The user could then click on the subform nick name in the subform to open the associated file.

By surfing the web, I've managed to create a macro allowing the user to store the selected file and path in a column called LinkLocation , assign the entry a nick name via a InputBox, and store the nickname in a column called LinkName. This macro is working as expected.

Edit: Code shared.

Sub test()

 Dim f    As Object
 Dim strSQL As String
 Dim strShorthand As String ' Short hand name for display in subform.
 Dim strFullFilePath As String ' Full file path

 Set f = Application.FileDialog(3)

 f.allowMultiSelect = False
 
 f.Show
 
 strFullFilePath = f.SelectedItems(1)
 
 strShorthand = InputBox("Enter the shorthand name here.")
  
strSQL = "INSERT INTO tblLinks (LinkLocation, LinkName) Values ('" & 
    strFullFilePath & "','" & strShorthand & " ');"
 
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

End Sub

I am having two problems.

  1. When I click to create a new record in the subform linked to tblLinks, my macro runs and the data is correctly stored. However, the newly created record does not show up in the subform.

  2. How do you create a double click event so FollowHyperlink properly engages and opens the file in question?

I cannot find an answer to either of these questions on the web.

I've written a fair amount of complex VBA for Excel, but Access is completely new to me. Any resources you can recommend are most welcome.

Thanks in advance.

Solution

  • Enter values into bound controls on form instead of running INSERT action and issue will go away. Instead of InputBox for LinkName, just enter into textbox on form. If intent is to enter a new item, make sure focus is on new record row. Then code behind subform:

    Me.LinkLocation = strFullFilePath

    Otherwise, have to Requery or Refresh form to display new record. Again, if code is behind subform, simply: Me.Requery.

    As for double click event, you already know how to create VBA code so do the same for this event using FollowHyperlink command. Can be double click of a textbox or single click of command button. Format either to look like clickable hyperlink. Set textbox as locked so user cannot accidentally edit.