Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlink to PDF document on network (2003 SP2)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink to PDF document on network (2003 SP2)

    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:
    <code>
    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
    </code>
    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.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to PDF document on network (2003 SP2)

    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!!

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to PDF document on network (2003 SP2)

    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.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to PDF document on network (2003 SP2)

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Hyperlink to PDF document on network (2003 SP2)

    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.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlink to PDF document on network (2003 SP2)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •