Access DB Report: clickable file location using a button

Question

I am trying to write some some code for my button: "Open". If click, a file with that 'file address' will be automatically opened:

I have the following VBA function that has the file location as an input string. My question is, how do I refer the C:... link in the column "File Location" to the function's input? I mean, this won't work (but if I comment out the function, and uncomment the sub, I have the pop-out Window that says Hi!):

Trying to refer to another column inside the report

So how do I do it?

Answer 1

Remove the two lines declaring and setting strURLLink.

You would call the function from the button click event.

Private Sub Command35_Click()
x = OpenDocument(Me![File Location])
End Sub

However, it doesn't really need to be a Function, could be a Sub.

Why not just directly in the button Click event?

Private Sub Command35_Click()
    On Error GoTo Catch
    Application.FollowHyperlink(Me![File Location])
    Exit Sub
Catch:
    MsgBox "Oops! Can't open file"
End Sub

And this is not macro code, it is VBA. Macro coding in Access is very different.

Answer 2

It works!!! This is how, everyone:

Click the button 'Open' in Design View. In the Property Sheet, select [Event Procedure], then click '...' to enter a window where you can write your VBA code. Make sure the strings inside the following two red boxes match like this:

Match the field name

Please note that the string 'Main_File Location' should be the exact column name you have set in the table that you are referencing to this report.

Also, make sure your file name show which type of file it is. Like, if your file name is myfile1 which is an PNG file, your file location must be: C:\somepath\myfile1.png. If it's an excel: C:\somepath\myfile1.xlsx. Or, the Sub can't open the document.