How to create a button which opens a specifically named PDF file based on access form field (1 Viewer)

[GS1089]

Hi everyone,

I work for a company which produces technical reports for our customers. Each time a report is created, a unique number is generated using our access database and the report PDF file is saved as this same number. All reports are then stored in the same folder on our drive.

When I access a customers form on the database I have a separate form which shows 'past work' for the customer, and it is a tabular form where each line represents a report issued to them. What I would like to do is add a button which appears on each line of the form, which opens the appropriate report PDF based on the report number field on the form. Is this possible to do?

My initial thinking is that I would need a VBA code to combine the folder path "Q:\Compliance\Radiation Protection Adviser\Equipment reports\PDF\Issued\DB\" and then the report number [Report no] and then ".pdf" but I'm having a hard time trying to put together a code which doesn't break on me! I'm also happy to entertain solutions which don't involve VBA code, but I'm guessing this may be too advanced a function to be able to avoid VBA.

Please be aware that I'm by no means fluent in VBA so please dumb it down for me!

Thanks in advance.
Gary

 

[theDBguy]

Hi Gary. Welcome to AWF!

How is the code breaking on you? Can you show it to us?

 

[GS1089]

Hi DBguy,

Thanks for the quick response, and please try not to laugh at my first attempt as VBA really isn't one of my strengths! I must admit I threw this together after viewing various other forums and a couple of internet videos on the topic so it may make no sense whatsoever, the point at which it keep breaking is the line in red, I get run time error 91 "Object Variable or With Block Variable not set".

Thanks
Gary

Private Sub Command35_Click()

Dim DB As Database

Set DB = CurrentDb()
Set FNIW = Form

Dim Form_NonIDHWork As Form_NonIDHWork
Set Form_NonIDHWork = Form_NonIDHWork

Dim ReportNo As FormField

Set ReportNo = Form_NonIDHWork.Report_no.Value
VBA.Shell "Adobe Reader"

VBA.Shell "Q:\Compliance\Radiation Protection Adviser\Equipment reports\PDF\Issued\DB\" & ReportNo & ".pdf"

End Sub

 

[theDBguy]

Hi. What happens if you used this instead?

Private Sub Command35_Click() 
Dim strPath As String

strPath =  "Q:\Compliance\Radiation Protection Adviser\Equipment reports\PDF\Issued\DB\" 

Application.FollowHyperlink strPath & Me.Report_no & ".pdf"

End Sub

Hope that helps...

 

[GS1089]

Thanks for the suggestion DB guy, I'm getting this error message appear;

Do the 'Me.Report_no' reference the report number on the form the button is placed on?

 

[GS1089]

Hi DB guy,

My apologies, i still had some other bits still on the VBA form and it is working now, thank you so much for your help!

Thanks
Gary

 

[theDBguy]

Hi DB guy,

My apologies, i still had some other bits still on the VBA form and it is working now, thank you so much for your help!

Thanks
Gary

Hi. You're welcome. I would suggest giving your objects more meaningful names, so you can easily tell them from each other. Good luck!