Solved

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

Posted on 2006-11-09
13
301 Views
Last Modified: 2012-06-27
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 **********





0
Comment
Question by:FocusedEnergy
  • 6
  • 5
  • 2
13 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17909810
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
 

Author Comment

by:FocusedEnergy
ID: 17909834
Can it be a relative directory?  Leaving out the c:\ for instance and just writing \temp\ for instance?


0
 

Author Comment

by:FocusedEnergy
ID: 17909840
Also, how can I use this with a combo box?
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
ID: 17910098
ShellExecute is good but now Access have FollowHyperlink, that seems to work well with pdf's and doc's


Syntax is

FollowHyperlink "fullpathoffile"

0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17910100
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
 

Author Comment

by:FocusedEnergy
ID: 17910228
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:FocusedEnergy
ID: 17910237
rockiroads can the FollowHyperlink be relative if using UNC syntax?  Also, can this be combined in a combo box?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17910257
you can set the row source and bound column of the combo just about any way you want.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17910284
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
 

Author Comment

by:FocusedEnergy
ID: 17917598
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
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 250 total points
ID: 17919609
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
 

Author Comment

by:FocusedEnergy
ID: 17919735
Thanksso much puppydogbuddy, that answers everything specifically.

Cheers

Ethan
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17919927
Ethan,
Glad we could help.  Thanks for the points and grade.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now