0

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

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?

Labra
  • 1
  • 2

2 Answers2

0

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.

June7
  • 19,874
  • 8
  • 24
  • 34
  • @ June7 Hey thanks so far. I tried this: `Private Sub Command35_Click() On Error GoTo Catch Application.FollowHyperlink ([Reports]![Search Report]![File Location]) Exit Sub Catch MsgBox "Oops! Can't open file" End Sub` After clicking the button: Compile error: Sub or Function not defined. However, after I comment out the 4 lines above Msgbox and click the button again, the Msgbox pops out. Thoughts? – Labra Jul 14 '17 at 01:29
  • Ooops, I should have caught this. Use `Catch:`. See revised answer. Also, don't think you need the reports class and report name reference prefix. – June7 Jul 14 '17 at 02:56
  • @ June7 Yup now the error is gone. I tried all these three: 1) Your way: `Application.FollowHyperlink (Me![File Location])` 2) Get from report: `Application.FollowHyperlink ([Reports]![Search Report]![File Location])` 3) Get from table `Application.FollowHyperlink ([Tables]![Main Document]![Main_File Location])`. All three give me the same pop out window: 'Oops! Can't open file'... So...? The column name in my table named 'Main Document' really is 'Main_File Location'... – Labra Jul 14 '17 at 03:42
  • You didn't try the code without the form name prefix, as I show in my example which is using the Me alias. Certainly can't reference table. The function works for me. Test it with a static file path and name instead of the variable reference. – June7 Jul 14 '17 at 05:14
  • @ June7 Thanks it works!! Please see my own answer to my own post underneath :) I did try your way. That was the first way I tried among the three above. You were right about the file path thing but it wasn't about variable referencing. I relocated the folder yesterday :P... Thanks again~ – Labra Jul 14 '17 at 06:05
0

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.

Labra
  • 1
  • 2