Opening unique documents based on Combo Box Selection (1 Viewer)

Mooish

New member
Local time
Yesterday, 22:32
Joined
Mar 7, 2019
Messages
9
Hello,

I am trying to create a form that will open specific PDF's depending on the selection from the combo box.

As I am very new to Access I have had some trouble in achieving this.

I have created a button that will open the location of the files but It doesn't open the actual file.

I am also unsure on how to change which file opens depending on what has been chosen in the combo box.


Code:
 Private Sub btnBrowse_Click()
    Dim file As FileDialog
    Set file = Application.FileDialog(msoFileDialogFilePicker)
    
    file.AllowMultiSelect = False
    
    If file.Show Then
        Me.txtFileLocation = file.SelectedItems.Item(1)
    End If
    
   Dim strPath As String
   
  'Error check
  If IsNull(Me.cmb_DocDes) Then
    MsgBox "No document selected", vbOKOnly
    
      'Get path
  strPath = DLookup("InfoPDF", "tblQualityProcedures", "ID=" & Me.cmb_DocDes)
  Application.FollowHyperlink "****" & strInput
  
    Exit Sub
  End If
 
End Sub

The **** is where I have inserted the location to the files.

Not sure If I am approaching this the right way or there is a simpler approach.

Apologies If I have posted this in the wrong place.

Thank you.
 

Ranman256

Well-known member
Local time
Today, 01:32
Joined
Apr 9, 2015
Messages
4,339
no need for Dlookup, the field should have the full file path,
c:\folder\myfile.xls

Paste this code into a module, and it will open ANY file in its native application.
In a form put the field and a button to open it.

if the file is myFile.pdf, will open it in acrobat
if the file is myFile.doc, it will open the doc in Word
if its just a file path, it will open in file explorer.
etc..

usage:
OpenNativeApp txtBox

paste this code into a module

Code:
'Attribute VB_Name = "modNativeApp"
'Option Compare Database
Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long

Private Declare Function GetDesktopWindow Lib "user32" () As Long
Const SW_SHOWNORMAL = 1
Const SE_ERR_FNF = 2&
Const SE_ERR_PNF = 3&
Const SE_ERR_ACCESSDENIED = 5&
Const SE_ERR_OOM = 8&
Const SE_ERR_DLLNOTFOUND = 32&
Const SE_ERR_SHARE = 26&
Const SE_ERR_ASSOCINCOMPLETE = 27&
Const SE_ERR_DDETIMEOUT = 28&
Const SE_ERR_DDEFAIL = 29&
Const SE_ERR_DDEBUSY = 30&
Const SE_ERR_NOASSOC = 31&
Const ERROR_BAD_FORMAT = 11&


Public Sub OpenNativeApp(ByVal psDocName As String)
Dim r As Long, msg As String

r = StartDoc(psDocName)
If r <= 32 Then
    'There was an error
    Select Case r
        Case SE_ERR_FNF
            msg = "File not found"
        Case SE_ERR_PNF
            msg = "Path not found"
        Case SE_ERR_ACCESSDENIED
            msg = "Access denied"
        Case SE_ERR_OOM
            msg = "Out of memory"
        Case SE_ERR_DLLNOTFOUND
            msg = "DLL not found"
        Case SE_ERR_SHARE
            msg = "A sharing violation occurred"
        Case SE_ERR_ASSOCINCOMPLETE
            msg = "Incomplete or invalid file association"
        Case SE_ERR_DDETIMEOUT
            msg = "DDE Time out"
        Case SE_ERR_DDEFAIL
            msg = "DDE transaction failed"
        Case SE_ERR_DDEBUSY
            msg = "DDE busy"
        Case SE_ERR_NOASSOC
            msg = "No association for file extension"
        Case ERROR_BAD_FORMAT
            msg = "Invalid EXE file or error in EXE image"
        Case Else
            msg = "Unknown error"
    End Select
'    MsgBox msg
End If
End Sub


Private Function StartDoc(psDocName As String) As Long
Dim Scr_hDC As Long

Scr_hDC = GetDesktopWindow()
StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:\", SW_SHOWNORMAL)
End Function


usage:

Code:
sub btnOpenFile_click()
  OpenNativeApp ME.txtBox
end sub
 

Minty

AWF VIP
Local time
Today, 05:32
Joined
Jul 26, 2013
Messages
10,355
I actually disagree with ranman about storing the whole path. I would store the top level path is a system table, so "\\MyNetworkShare\MyFileStore" would be in a table.

If you ever need to move the files storage path you simply update one record in the table , the rest of your code still works.

Then to your code - You are setting variable strPath but then using strInput as the variable in your application follow code;

Code:
  'Get path
  [COLOR="Red"]strPath[/COLOR] = DLookup("InfoPDF", "tblQualityProcedures", "ID=" & Me.cmb_DocDes)
  Application.FollowHyperlink "****" & [COLOR="DarkOrchid"]strInput[/COLOR]
 

Mark_

Longboard on the internet
Local time
Yesterday, 22:32
Joined
Sep 12, 2017
Messages
2,111
I am trying to create a form that will open specific PDF's depending on the selection from the combo box.

Two steps back.

What information does the combo box provide?
Do you want to open ONE PDF or multiple?

Answering this will drive some other questions, such as "What does the combo box add if you are already planning to have the user manually select a file with a file dialog?"

Please explain what you want your users experience to be when using this form. That will help ensure we get you the proper answer for your situation.
 

Mooish

New member
Local time
Yesterday, 22:32
Joined
Mar 7, 2019
Messages
9
The ComboBox drops down the list of potential documents to choose from.

I.e.

- Document 1
- Document 2
- Document 3

So if the user was to select Document 1 from the ComboBox, then the 'Open Document' button, when clicked, would open Document 1 from our Network.

If Document 2 was selected in the ComboBox then the 'Open Document' button would open Document 2 from our Network, and so on.

So, only One PDF would be opened upon clicking the 'Open Document' button but this document would change depending on the selection from the ComboBox.

Hopefully this helps a bit more. Sorry for the confusion.
 

Minty

AWF VIP
Local time
Today, 05:32
Joined
Jul 26, 2013
Messages
10,355
In addition to Mark's suggestion, if all the documents are in one folder, and there are a lot of them, the combo will get difficult to navigate quite quickly.

I would use a list box and simply open the file on double click of the item in the list box.

The list box view (imho) is a more intuitive interface for things like file selection. It mimics what a user sees in the normal windows environment.
 

Mooish

New member
Local time
Yesterday, 22:32
Joined
Mar 7, 2019
Messages
9
Thanks Minty,

Is there somewhere on this forum that provides some guidance on hyperlinking or rather adding a double click event to open a file within List Boxes?

Thank you
 
Last edited:

Minty

AWF VIP
Local time
Today, 05:32
Joined
Jul 26, 2013
Messages
10,355
If your listbox was called lstFileList then on the build event property of the list box.

Add the code below

Code:
    Dim sFullPath As String
    Dim sTopPath as String

    sTopPath = "\\YourFileLocation\GoesHere\"
    sFullPath  = sTopPath & Me.lstFileList

    Application.FollowHyperlink sFullPath

That should work.

In the future you could use a dlookup to get the top level path from a system table with that info stored in it.
 

Mark_

Longboard on the internet
Local time
Yesterday, 22:32
Joined
Sep 12, 2017
Messages
2,111
More importantly,

Whom ever sets up this list can then either put in the full file path OR just the file name. No need for an end user to have to muck about with a dialog to find the file in the first place.

Using Minty's suggestion, you have one table, for configuration, that holds "Here is where we keep these types of files". In your table they select from you hold ONLY "Here is the name of the file". From the users perspective, one person sets things up and none of the rest need worry about it. For those creating and adding the files to your table of "Documents", they only need enter where the document is, a short name for it that users see, and a somewhat longer description so others maintaining the documents understand what is supposed to be in it, with this last being optional.

In this case Form really will follow function. B-)
 

Mooish

New member
Local time
Yesterday, 22:32
Joined
Mar 7, 2019
Messages
9
Thank you for all the input.

So, because the List Box has multiple documents within it. Will the code provided by @Minty mean that when the different documents are clicked the correct document will appear.

Or will the code only mean that when the List Box is clicked the location for the files is opened but not the specific file itself.
 

Minty

AWF VIP
Local time
Today, 05:32
Joined
Jul 26, 2013
Messages
10,355
No - if the file path string created is like

\\Fileshare\pdfstore\doc1.pdf

Then that file would open.
 

Users who are viewing this thread

Top Bottom