Hyperlink to PDF document on network (2003 SP2)

Topic

New Reply

Our admin assistants have a database to track employee travel. I have no idea who built it, but it has one table (called TRAVEL). Each record has an autonumber primary key, which the admins use as the travel authorization number on the printed form employees fill out when they travel. The admin scans the hard copy authorization form and saves it to our network with the name being the autonumber primary key in the database table.

It’s worked fine for the past 10 years. Now, a new admin has asked if we can link the pdf’s to the database so that supervisors can go to the database, see the employee’s travel record, click a button on the form, and it will open the corresponding PDF from the network. I don’t know how to write code to make the button go to the network drive and open the PDF.

From experience, I can tell you that not every admin is diligent about scanning the hard copy and saving it to the network right away, so there will be times when there is a record in the database that doesn’t have a pdf on the network, yet. In that case, they want a message that says something about the PDF file doesn’t exist.

Can someone help me with this, please? The next question is, the database is split – data table (TRAVEL) backend and the data entry form (TravelDataEntry) is on each admin’s desktop. How can we get this button on all the admins’ data entry form?

Thank you. I can post a copy of the database (with bogus info) if that would help.

Reply | Quote

Replies

I assume that the admin will view a form bound to the TRAVEL table.
Let’s say that
– The primary key is named ID.
– You create a command button cmdOpenPDF on the form.
– The folder containing the PDF files is MyServerMyShareMyFolder.

The code for the command button could look like this:

Sub cmdOpenPDF_Click()
' Modify as needed, but keep trailing backslash
Const strPath = "MyServerMyShareMyFolder"
Dim strFile As String
strFile = Dir(strPath & Me.ID & ".pdf")
If strFile = "" Then
MsgBox "PDF file for " & Me.ID & " not found!", vbExclamation
Else
' Open file in default PDF reader
Application.FollowHyperlink strPath & strFile
End If
End Sub

Have the admins customized their frontends?

If not, I’d add the button to the form in your own copy of the frontend, then send it to all admins with the request to save it to their desktop, replacing the old version.

If they do have customized frontends, you could make your modified frontend available on the network to them, and instruct them to import the form into their own copy.

Reply | Quote

That all makes perfect sense to me, Hans. Thank you. I’ll let you know how it works. You are right, the admins did not customize the front end. So yep, I can modify it and send it to them. Thank you!!

Reply | Quote

Ok I tried it, Hans. I’ve done something wrong, though. When I click the command button I get the message, ‘the object doesn’t contain the Automation object ‘Open_PDF.’

I am very very basic when it comes to inserting and using code, so please excuse my ignorance.
here’s what I did:
Clicked on Modules, New
Copied/pasted your code – changed the first line to: Sub cmdOpen_PDF()
Saved it
Closed the window

Created a New Macro (clicked Macros, new)
Under Action, clicked RunCode
Function name at bottom I typed Open_PDF
Saved macro
Closed screen

Created cmd button on form with the command button wizard and picked Miscellaneous, Run Macro and picked the Open_PDF macro.

Tested the button and got an Action Failed screen.
Macro name: Open_PDF
Condition: True
Action Name: RunCode
Arguments: Open_PDF

Pressed the Halt button
received the error message.

Soooo what did I do wrong?

Thanks for your patience.

Reply | Quote

Correction: Copied/pasted your code – changed the first line to: Sub cmdOpen_PDF_Click()

Reply | Quote

The code was intended to be used as the On Click event procedure for the command button. This means that the code should NOT be in a standard module, you should remove it. Do the following:
– Open the form in design view.
– If you don’t have a command button yet, create it *without* using the Wizard.
– Click on the command button to select it.
– Activate the Event tab of the Properties window.
– Click in the On Click event.
– Select [Event Procedure] from the dropdown list.
– Click the builder button, i.e. the button with the three dots … to the right of the dropdown arrow.
– This will open the form module, and create the first and last lines of the event procedure.
– Copy the code I posted *without the first and last line* into the new procedure.
– Modify the path and the name of the primary key as needed.

Reply | Quote

That works beautifully!!! Your instructions were perfect, Hans. Thank youuuuuu!

Reply | Quote