Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Oct 2006
    Location
    St. George, Utah, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open OLE in Original Application (Access 2003)

    I'm looking for any help I can find on opening record specific PDF files in Acrobat Reader through a macro (i.e. double click a record from a list and open a related pdf). Thanks.

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

    Re: Open OLE in Original Application (Access 2003)

    Welcome to Woody's Lounge!

    Can you provide some additional information?
    - Are all PDF files stored in a single folder?
    - Does the record contain the filename of the PDF file, or can the filename be derived from fields in the record? If so, how?

  3. #3
    New Lounger
    Join Date
    Oct 2006
    Location
    St. George, Utah, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open OLE in Original Application (Access 2003)

    Thanks for the quick response.

    The PDF files are in subfolders by category under one folder, but can be placed in one folder if it makes it easier. The PDF file is linked through and OLE object in the record.

    To be even more specific, I have our entire product line in a list box. I want to give access to the products' specifications (which are pdf files) through double clicking on a product in the list box. The PDF files are record specific and need to match the product selected in the list box. Currently the pdf files live in the same record as the products they belong to.

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

    Re: Open OLE in Original Application (Access 2003)

    I would use a text field instead of an OLE field, and store the path and filename of the PDF file in the text field.
    Add the text field as an extra column to the row source of the list box. By setting the corresponding column width to 0, you can hide the extra column.
    You can then use Application.FollowHyperlink or ShellExecute to open the PDF file. See <post:=201,988>post 201,988</post:> for an example of the code. You'd replace the literal path and filename used there with a reference to the hidden column of the list box.

  5. #5
    New Lounger
    Join Date
    Oct 2006
    Location
    St. George, Utah, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open OLE in Original Application (Access 2003)

    Thank you, Hans. I appreciate your help. I'll give that a try. - I gave that a try but have to admit I don't have much experience with VBA. In the following code: IngResult = ShellExecute(hWndAccessApp,"open", "Location" ,0&,0&,....) how do I stick the text from the hidden column in the list box into the "Location" section? And how do I make sure it is opening the selected records link? My list box is named "Combo14" and the info is in the 9th column.

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

    Re: Open OLE in Original Application (Access 2003)

    Try the following (I assume that you have already copied the code for ShellExecute into your database).
    - Open your form in design view.
    - Select Combo14 (strange name for a list box, BTW)
    - Activate the Event tab of the Properties window.
    - Click in the On Dbl Click box.
    - Select [Event Procedure] from the dropdown list.
    - Click the builder button (the ... to the right of the dropdown arrow).
    - Make the code look like this:

    Private Sub Combo14_DblClick(Cancel As Integer)
    Dim lngResult As Long

    lngResult = ShellExecute(hWndAccessApp, "Open", _
    Me.Combo14.Column(8), 0&, 0&, SW_SHOWMAXIMIZED)
    If lngResult <= 32 Then
    MsgBox "Couldn

  7. #7
    New Lounger
    Join Date
    Oct 2006
    Location
    St. George, Utah, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open OLE in Original Application (Access 2003)

    After entering the code below the database seems to lose Combo14. I have a query on the form that references the list box and the query pretends like Combo14 no longer exists. I get the message "Enter parameter value for Forms!Frm_Assembly.Combo14". The entire code I entered is as follows:

    Private Sub Combo14_DblClick(Cancel As Integer)

    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal IpOperation As String, ByValIpFile As String, ByValIpDirectory As String, ByVal nShowCmd As Long) As Long

    Private Const SW_SHOWMAXIMIZED As Long = 3

    Private Sub cboProducts_Click()
    Dim IngResult As Long

    Ing Result = ShellExecute(hWndAccessApp, "Open", Me.Combo14.Column(8), 0&, 0&, SW_SHOWMAXIMIZED)
    If IngResult <= 32 Then MsgBox "Couldn't open the PDF document.", vbExlamation
    End If
    End Sub

  8. #8
    New Lounger
    Join Date
    Oct 2006
    Location
    St. George, Utah, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open OLE in Original Application (Access 2003)

    Thank you for all your help, Hans. I still didn't have any luck with that last thread. Attached is a very slimmed down version. I appreciate what you've got me so far.

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

    Re: Open OLE in Original Application (Access 2003)

    Sorry, you can't do it like that. In the first place, the declarations should be at the top of the module, before all Subs:

    Private Const SW_SHOWMAXIMIZED As Long = 3

    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal IpOperation As String, ByValIpFile As String, ByValIpDirectory As String, ByVal nShowCmd As Long) As Long

    The name cboProducts_Click was just the name of the example code in the post I referred to, you shouldn't use this in your database. Instead, use the code from my previous reply.

    If you still have problems (which is quite understandable if you're just venturing into VBA), could you post a stripped down copy of your database? That would make it easier for us to help you. See <post#=401925>post 401925</post#> for instructions.

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

    Re: Open OLE in Original Application (Access 2003)

    You hadn't added the column with the path/filename to the row source of the list box (nor adjusted the column count and column widths).
    You had set the On Dbl Click property to the name of a (non-existing) macro instead of to [Event Procedure]
    The code for ShellExecute wasn't present.

    BTW I think your form should be unbound (i.e. record source blank).

    See the attached version (in Access 2000 format), and look at the code.

  11. #11
    New Lounger
    Join Date
    Oct 2006
    Location
    St. George, Utah, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open OLE in Original Application (Access 2003)

    It Worked! Thanks again, Hans. I actually leave the form bound so that I can use the refresh command. The table is actually not used for anything; but without it being bound and refreshing after update, the other queried lists in the form don't refresh with a newly selected model.

Posting Permissions

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