Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    May 2009
    Posts
    104

    Unanswered: Open PDF with Access

    I want to be able to have Access open a particular pdf that has the same name as a field in a record. I have the macro built that will open a pdf if i input the exact name of the PDF but i want it to be dynamic so as i click on different records it will open the appropriate pdf for that record.
    Example:
    The field name is [PartNumber] and the data in that field is 151-1525, which is the number for a particular part. The code that i have that works is as follows:


    Private Sub Command53_DblClick(Cancel As Integer)
    Dim stAppName As String
    stAppName = "D:\Program Files (x86)\Adobe\Reader 10.0\Reader\AcroRd32.exe C:\Users\owner\Desktop\151-1525.pdf"
    Call Shell(stAppName, 1)
    End Sub

    I may be missing the select record command or something, I built this database 4 years ago and it's been running flawless ever since so i'm a little out of practice with the troubleshooting. I would like the code to read something like this but my syntax may be a little flawed.

    This does not work
    Private Sub Command53_DblClick(Cancel As Integer)
    Dim stAppName As String
    stAppName = "D:\Program Files (x86)\Adobe\Reader 10.0\Reader\AcroRd32.exe C:\Users\owner\Desktop\[PartNumber].pdf"
    Call Shell(stAppName, 1)
    End Sub
    This should open a pdf with the same name as the field data, assuming the pdf is there.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Pretty close, try:

    stAppName = "D:\Program Files (x86)\Adobe\Reader 10.0\Reader\AcroRd32.exe C:\Users\owner\Desktop\" & [PartNumber] & ".pdf"

  3. #3
    Join Date
    May 2009
    Posts
    104
    You guys never cease to amaze me. That did the trick.

  4. #4
    Join Date
    May 2009
    Posts
    104
    moved to another thread
    Last edited by dbshaft; 07-05-11 at 20:45.

  5. #5
    Join Date
    Jan 2015
    Posts
    10

    PDF Open Help

    Please help real newb in reguards to VB etc

    Im trying to do the above (open a PDF related to the field selection on the push of a button

    I have a table called "SOP Data" with the following fields
    ID No, Area, Description and Link.

    The ID Field Contains a numeber, EG: SOP045
    Area Field & Description field - Not important at this time
    The Link Field contains a Link to a PDF (this for original plan of clicking clink to open file, I dont belive I need this)

    I have a form (Form1)
    This has a button (Command8)
    and a drop down box associated with the ID NO used to select relitive document.

    I Have a folder containing the PDF files in C:\PDF\
    These are saved as the same no as recorded in the ID No - e.g - SOP045.pdf

    I then used the following code as robbed from above posts:

    Private Sub Command8_Click()
    Dim stAppName As String
    stAppName = "C:\Program Files (x86)\Adobe\Reader 10.0\Reader\AcroRd32.exe"C:\PDF\" & [ID No] & ".pdf"
    Call Shell(stAppName, 1)
    End Sub


    I get a Error and the 1st line is highlighted, the 3rd line also shows in red, I guess this is the errors ?


    Appriciate any help, have been playing with this for a day or so a need some help / advice

    Thankss, hope the above makes sence

  6. #6
    Join Date
    May 2009
    Posts
    104
    There is more to this than you think. My code for opening a pdf has gotten a lot more efficient. It's been a long time since i did this but basically i added a module that looks to see what the default program for that file is and opens with that. So you don't need the first part of your code. Example:
    Instead of
    Code:
    stAppName = "C:\Program Files (x86)\Adobe\Reader 10.0\Reader\AcroRd32.exe"C:\PDF\" & [ID No] & ".pdf"
    it would be
    Code:
    stAppName ="C:\PDF\" & [ID No] & ".pdf"
    I'm not even sure you need to set variables for this. Very much of my database just has one line of code for opening files or explorer windows.
    If you want to open an excel spreadsheet the code looks like this.
    Code:
    ShellExec "C:\FinalInspection\InspectionSheets\" & PartNumFilter2 & ".xlsx"
    Here is what the module looks like
    Code:
    Option Compare Database
    
    Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
        ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, _
        ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
        
    
    Function ShellExec(ByVal FileName As String)
    
        Const SW_SHOWNORMAL = 1
        
        ShellExecute &H0, vbNullString, FileName, vbNullString, vbNullString, SW_SHOWNORMAL
    
    End Function

    In the end your code will look like this.
    Code:
    Private Sub Command8_Click()
    Dim stAppName
    
    stAppName = "C:\PDF\" & [ID No] & ".pdf"
           ShellExec stAppName
    End Sub

  7. #7
    Join Date
    Jan 2015
    Posts
    10

    PDF Help

    Really appriciate the help in this , still struggling really need some VB training, would be great if one of you geneousous could provide a sample data base, which would help me work it all out. Thanks for the support todate

  8. #8
    Join Date
    May 2009
    Posts
    104
    Sample Database attached.

    Database2.zip

    Put the pdf on your local drive "C:\Users\Public\Documents" Open the databases, open the frmParts form and click the button. The pdf should just open. If you want to add the ShellExec function to your database. Go to the database tools tab, click the Visual Basic button. Click the insert module button, then create a new module. Copy and paste the code from "Module 1" from the attached database. Any time you want to open a file, as long as you have the path correct and Windows had a default program to open that file ( etc. .txt will open with notepad, .xls will open with excel, or whatever your default program is) they should open any time you use the function ShellExec with a file like the example.
    Last edited by dbshaft; 01-15-15 at 10:51.

  9. #9
    Join Date
    Jan 2015
    Posts
    10

    Help

    Thats brilliant really helps. Really appriciate your help in this !

    Stage 1 - played with sample DB - Perfect
    Stage 2 - edit code to point to new location of PDF ( File = "F:\Inspection\SOP PDF\" & Part & ".pdf") re tested all works and opens pdf located in the dir stated - Perfect
    Stage 3 -
    This is what Ive done
    Create a Combo box on the "frmParts" form pointing to the "PartNumber" field in the table and (Control Source = PartNumber) ( I want to select the various parts within the table and then show the relitive pdf)
    Created 2nd button "Command7" on the form
    On the events code for the button I copied your code and edited it to "Command7" button.

    Issue on running I get a Compile error "Label not defined"

    Im sure we are very close and once again appriciate your assistance



    Private Sub Command4_Click()
    On Error GoTo Error_Handle
    Dim Part
    Dim File
    Part = [PartNumber]
    File = "F:\Inspection\SOP PDF\" & Part & ".pdf"

    ShellExec File
    Exit_Command4:
    Exit Sub

    Error_Handle:
    MsgBox Err.Description
    Resume Exit_Command4
    End Sub


    Private Sub Command7_Click()
    On Error GoTo Error_Handle
    Dim Part
    Dim File
    Part = [PartNumber]
    File = "F:\Inspection\SOP PDF\" & Part & ".pdf"

    ShellExec File
    Exit_Command7:
    End Sub

    Error_Handle:
    MsgBox Err.Description
    Resume Exit_Command7
    End Sub

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im not too certain you can have the same label name defined in more than one function/sub.
    Try renaming the label Error_Handle to something unique in the sub_Command7_click function
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jan 2015
    Posts
    10

    Help

    Hmmm struggling i thought i had it but no - i edited command4 to 7 then deleted the command button but no joy when i change the selection in the drop down box it just doesnt do anything.

    Attached DB for reff - ( I didnt include any PDFs, so will need to edit destination and add a few files named inline with the two records in the table)
    Attached Files Attached Files

  12. #12
    Join Date
    May 2009
    Posts
    104
    I think your problem is you aren't referencing the combo box, see example below

    Code:
    Private Sub Command7_Click()
    On Error GoTo Error_Handle
    Dim Part
    Dim File
    Part = [PartNumber] '<-----This should be referencing the combo box if you want to open files coinciding with a displayed part number.
    File = "F:\Inspection\SOP PDF\" & Part & ".pdf"
    
    ShellExec File
    Exit_Command7:
    End Sub
    
    Error_Handle:
    MsgBox Err.Description
    Resume Exit_Command7
    End Sub

    It should look something like this

    Code:
    Private Sub Command7_Click()
    On Error GoTo Error_Handle
    Dim Part
    Dim File
    Part = [Combo7] '<---change the combo7 to whatever your combo box is named.
    File = "F:\Inspection\SOP PDF\" & Part & ".pdf"
    
    ShellExec File
    Exit_Command7:
    End Sub
    
    Error_Handle:
    MsgBox Err.Description
    Resume Exit_Command7
    End Sub

  13. #13
    Join Date
    May 2009
    Posts
    104
    You also have to pay attention to what "Bound Column" you have selected. You had #1 as your bound column, but your data is in column #2. You will need to change that. #1 column is the autonumber "[ID] field and column #2 is the data you want to reference. I made the above changes to your database and it all worked for me.

  14. #14
    Join Date
    Jan 2015
    Posts
    10

    Help



    Thankyou I think we are there, had a quick play put will review tomorrow, needed to reference the combo box and bound column correction

    Ill keep you posted

    Thankyou

  15. #15
    Join Date
    Jan 2015
    Posts
    10

    More :)

    Im now getting carried away Lol

    On hitting the button to fetch pdf i would like to close the form. I added the "DoCmd.Close" command it didnt work as you know

    rivate Sub Command7_Click()
    On Error GoTo Error_Handle
    Dim Part
    Dim File
    Part = [Combo5]
    File = "C:\pdf\EHS\" & Part & ".pdf"

    ShellExec File
    Exit_Command7:
    Exit Sub

    Error_Handle:
    MsgBox Err.Description
    Resume Exit_Command7

    DoCmd.Close
    End Sub

Posting Permissions

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