Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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("",WIN_NORMAL)
'Open URL:          ?fHandleFile("", 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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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?
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

664 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