Opening up a PDF or Word Doc from a combo box?

Good Day,

I have records that might have a series of documents associated with them that I would like to have accessible to the user.  If a user were to select the document from a combo box and then click it, the ideal would be that the default Windows application would launch and select the proper record.  Several records might reference the same document, hence me wanting to use something like a combo box with a keywordID or something.  Also, I don't want to specify a hard link to the documents, since the documents could be moved (stored on a DVD to be mobile or switched computers etc), but they would always be in the same directory as the DB albeit in a subfolder.  Is this possible?

I saw this code but I don't understand it (specifically where to put it etc) nor if it would work with a combo box and/or would allow me to have a relative link to the files in question.  Any help would be appreciated.

Ethan

************ Code Start **********
'This code was originally written by Dev Ashish.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish

Private Declare Function apiShellExecute 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

'***App Window Constants***
Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 2            'Open Maximized
Public Const WIN_MIN = 3            'Open Minimized

'***Error Codes***
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&




'***************Usage Examples***********************
'Open a folder:     ?fHandleFile("C:\TEMP\",WIN_NORMAL)
'Call Email app:    ?fHandleFile("mailto:dash10@hotmail.com",WIN_NORMAL)
'Open URL:          ?fHandleFile("http://home.att.net/~dashish", WIN_NORMAL)
'Handle Unknown extensions (call Open With Dialog):
'                   ?fHandleFile("C:\TEMP\TestThis",Win_Normal)
'Start Access instance:
'                   ?fHandleFile("I:\mdbs\CodeNStuff.mdb", Win_NORMAL)
'****************************************************

Function fHandleFile(stFile As String, Optional lShowHow As Long = WIN_NORMAL)
Dim lRet As Long, varTaskID As Variant
Dim stRet As String
    'First try ShellExecute
    lRet = apiShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
           
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'No associated application. Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, lShowHow)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function
'************ Code End **********





FocusedEnergyAsked:
Who is Participating?
 
puppydogbuddyConnect With a Mentor Commented:
Ethan,
Rocki's followHyperlink suggestion should work very well.  I would have suggested it myself if I hadn't been so focused on addressing your questions about the fHandleFile....The follow hyperlink method has the advantage over the fHandleFile in that it will work >>>inside<<< a combo box, so you will be able to use the combo to select a building record and have  the selected  item keep focus until you click the hyperlink to go to the documents.  See the link below to read how it works.  

 As I implied above, if you use the followhyperlink  method, you won't need fHandleFile function. This link to a previous EE post should answer most of your questions...be sure and read the referenced links included in the post, especially the one on using the followhyperlink inside a combo box.

       http://www.experts-exchange.com/Databases/MS_Access/Q_21559505.html?query=follow+hyperlink+in+combo+box&topics=39

Regarding the second table issue, it depends on whether you want the hyperlink to open a directory or open a document.  If only a directory.....you don't need it.  If you want your hyperlink to open the document, you will not only need a second table, but will also need a second combo box on your main form that will display the list of documents for the building selected in the first combo box.  This second combo, not the first combo,  would have the hyperlink.

Regarding the structure of the second table: in addition to the fields you mentioned you would have an autonumber primary key to ensure that each row (record) is unique.....and this will allow you to have different records that reference the same docID and/or buildingrecordID

Hope this clears things up for you.

PDB
0
 
puppydogbuddyCommented:
Place the code in a   standard module and compile.

If you are going to use it to open files, place this code behind a button on your form:

Private Sub YourButton_Click()

fHandleFile("C:\TEMP\",WIN_NORMAL)
.........your other code
End Sub


The above will open the designated directory and let you pick.  you can designate any directory you want.
0
 
FocusedEnergyAuthor Commented:
Can it be a relative directory?  Leaving out the c:\ for instance and just writing \temp\ for instance?


0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
FocusedEnergyAuthor Commented:
Also, how can I use this with a combo box?
0
 
rockiroadsConnect With a Mentor Commented:
ShellExecute is good but now Access have FollowHyperlink, that seems to work well with pdf's and doc's


Syntax is

FollowHyperlink "fullpathoffile"

0
 
puppydogbuddyCommented:
I believe it  can be a relative directory, but would have to follow UNC syntax:  \\xxxxxxx\

How do you envsion using the combo box???? as a list of documents and their locations??

In the after update event of your combo, you could substitute the value of one or more columns in the combo box as your document locator as follows:

   fHandleFile(yourCombo.Column(2),WIN_NORMAL)
 
0
 
FocusedEnergyAuthor Commented:
I am thinking of having the combo box link to a keyword table I guess, so if the form where the combo box exists is referencing one table, the combo box would need to link this with a documents table I guess and the combo box would retrieve the info from that table and then when I select the proper file name it would launch.  If this is possible, I would not want the id number displayed but the document name.
0
 
FocusedEnergyAuthor Commented:
rockiroads can the FollowHyperlink be relative if using UNC syntax?  Also, can this be combined in a combo box?
0
 
puppydogbuddyCommented:
you can set the row source and bound column of the combo just about any way you want.
0
 
rockiroadsCommented:
Im not sure about unc, I must admit, Ive never tried that.  I know it works with hyperlinks and docs

with regards to relative paths, u can build a full path using the current directory

I suggested FollowHyperlink only cos its quick one liner. Prior to discovering this, I always used to suggest ShellExecute
The link example I used to give is this http://www.vbaccelerator.com/codelib/shell/shellex.htm
Its a great func because it basically emulates what u do in explorer, i.e., open, edit, print etc, whatever right click functions are available

But Im off to bed now, so nite all.
0
 
FocusedEnergyAuthor Commented:
puppydogbuddy,

can expand on your explanation, I'm quite a newbie at this.

I think rockiroads' idea of using followhyperlink will work well, now the key is the combo box.  I would need the followhyperlink command behind the combo box, the combox should reference another table?

So I will need two tables now?

I have presently: tblbuildings (with the original record that currently has a memo field listing a series of PDF docs)

now, I think I need to take that memo field and instead construct another table, with something like:

docID
recordID (from tblbuildings)
document file path

I have a bit of confusion regarding the new table.  Because some records from tblbuildings will source the same documents docID can't be unique or can it?  if record 1 references doc 1 2 and 3, and record 2 references doc 2 3 and 4, how would the second table be constructed, and then what code would be behind the combo box?
 
Where would I put: fHandleFile(yourCombo.Column(2),WIN_NORMAL)

Thanks again, I'm very new to SQL and Access, sorry for any frustration.

Ethan
0
 
FocusedEnergyAuthor Commented:
Thanksso much puppydogbuddy, that answers everything specifically.

Cheers

Ethan
0
 
puppydogbuddyCommented:
Ethan,
Glad we could help.  Thanks for the points and grade.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.