Solved

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

Posted on 2006-11-09
13
293 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
Comment Utility
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
Comment Utility
Can it be a relative directory?  Leaving out the c:\ for instance and just writing \temp\ for instance?


0
 

Author Comment

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

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:FocusedEnergy
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanksso much puppydogbuddy, that answers everything specifically.

Cheers

Ethan
0
 
LVL 38

Expert Comment

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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

744 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

17 Experts available now in Live!

Get 1:1 Help Now