Page 1 of 4 1234 LastLast
Results 1 to 15 of 48
  1. #1
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234

    Open external file with command button

    I'd like to program a command button to automatically open up a specific external file.



    For example, if I'm looking at record number 18962, if I click on the command button, it pulls up the contract named "...proposal 18962.pdf" from it's location in the document folder. If I'm looking at record number 17453, and I click the command button, it pulls up contract "...proposal 17453.pdf" etc.

    Can this be done?

  2. #2
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Let me be a little clearer: When the command button is clicked I want it to automatically navigate to "\\Spare\SharedDocs...\..proposal [EventID]" on the server's hard drive.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try FollowHyperlink.

    Application.FollowHyperlink "\\Spare\SharedDocs\proposal\" & Me.EventID & ".pdf"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Hmm...I'm getting "Cannot open the specified file" error.

    The full link is \\SPARE\SharedDocs\Rob's Valet Docs\Valet Proposals\[date][client name][EventID]

    Basically, what I want it to do is go into this folder \\SPARE\SharedDocs\Rob's Valet Docs\Valet Proposals and find the .pdf that has [EventID] in it.

    For the code, I did what you wrote, with the path changed to match where it's going:

    Private Sub Command599_Click()
    Application.FollowHyperlink "\\SPARE\SharedDocs\Rob's Valet Docs\Valet Proposals" & Me.EventID & ".pdf"
    End Sub

    (I haven't renamed the command button yet)

  5. #5
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Interestingly enough, when I remove the "& ".pdf"", and I don't have an event on the screen (EventID not populated), when I click it, it does open up the correct folder. But when I have an event on the screen, and EventID is populated, I get the error message of "can't open specified file."

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is Valet Proposals a folder or part of the pdf file name?

    Application.FollowHyperlink "\\SPARE\SharedDocs\Rob's Valet Docs\Valet Proposals\" & Me.EventID & ".pdf"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Valet Proposals is a folder. A complete link might look like this: "\\SPARE\SharedDocs\Rob's Valet Docs\Valet Proposals\9.26.15 Bob's Valet Proposal #15635.pdf"

    In this case, the "15635" is the EventID.

    I already tried putting the \ after Valet Proposals in the path. Still didn't help.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The apostrophe and/or # characters might be an issue. http://allenbrowne.com/func-GoHyperlink.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    I figured they might be. I had already removed the # from the file I was testing, and that didn't help. I'm going to remove the apostrophe also, and we'll see what happens.

  10. #10
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    Hmm... There's something screwy here. In order to make it all easier, I took the file I want to open moved it a few folders back, so the path is just \\SPARE\Shared Docs\[file name].

    I changed the code to read Application.FollowHyperlink "\\SPARE\SharedDocs\" & Me.EventID

    Still got the error message of "Cannot open the specified file"

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try opening a file at the C:\ root location.

    I had issues with FollowHyperlink so went with Shell method.

    Private Sub btnLink_Click()
    On Error GoTo ErrProc
    'FollowHyperlink is not working properly
    ''Application.FollowHyperlink Me.tbxLink
    Dim wsShell As Object
    Set wsShell = CreateObject("WScript.Shell")
    wsShell.Run Chr(34) & Me.tbxLink & Chr(34)
    Me.Title.SetFocus
    ExitProc:
    Set wsShell = Nothing
    Exit Sub
    ErrProc:
    MsgBox "Cannot open document. Contact database administrator. : " & Err.Number
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    I'm not sure how to apply that to this situation... I'm trying to avoid putting the path into a textbox. I was hoping it could just be opened with a button click.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I use expression in textbox. You can have the expression in VBA. Instead of referencing Me.tbxLink, put your expression there.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    bradp1979 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2015
    Location
    San Francisco, CA
    Posts
    234
    So I got it to work by manipulating the target files around a bit, so that it's just opening up a folder. This is my code now:
    Private Sub Command599_Click()
    Application.FollowHyperlink "\\SPARE\SharedDocs\Rob's Valet Docs\Valet Proposals\" & Me.EventID
    If (IsNull(Me.EventID)) Then
    Application.FollowHyperlink "\\SPARE\SharedDocs\Rob's Valet Docs\Valet Proposals\"
    End If


    End Sub

    However, I want to add another If statement of "If the folder with Me.EventID doesn't exist, open \\SPARE\SharedDocs\Rob's Valet Docs\Valet Proposals\ instead."

    What would the proper syntax for that be?

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am confused. Is EventID the name of folder or pdf file? If you want to open specific file, the string must include file extension.

    If Dir("\\SPARE\SharedDocs\Rob's Valet Docs\Valet Proposals\" & Me.EventID, vbDirectory) = "" Then
    ...
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Open an attachment using a command button
    By Eddy Sincere in forum Forms
    Replies: 3
    Last Post: 04-03-2015, 11:43 AM
  2. External script to call Command Button Click event handler
    By Sean Nakasone in forum Programming
    Replies: 5
    Last Post: 10-29-2014, 12:35 PM
  3. Command button open url
    By patrickmcdiver in forum Programming
    Replies: 2
    Last Post: 03-06-2012, 11:08 AM
  4. Command button to open Report
    By swagger18 in forum Programming
    Replies: 0
    Last Post: 11-17-2011, 02:56 AM
  5. Use a command button to open a form
    By johnpaul in forum Forms
    Replies: 24
    Last Post: 09-23-2010, 12:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums