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

Posted on 2006-11-09
Medium Priority
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.


************ 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
        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 **********

Question by:FocusedEnergy
  • 6
  • 5
  • 2
LVL 38

Expert Comment

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()

.........your other code
End Sub

The above will open the designated directory and let you pick.  you can designate any directory you want.

Author Comment

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


Author Comment

ID: 17909840
Also, how can I use this with a combo box?
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 65

Assisted Solution

rockiroads earned 1000 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"

LVL 38

Expert Comment

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:


Author Comment

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.

Author Comment

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

Expert Comment

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

Expert Comment

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.

Author Comment

ID: 17917598

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:

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.

LVL 38

Accepted Solution

puppydogbuddy earned 1000 total points
ID: 17919609
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.


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.


Author Comment

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


LVL 38

Expert Comment

ID: 17919927
Glad we could help.  Thanks for the points and grade.

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

627 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