Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-17-2018, 12:35 AM   #1
ebbsamsung
Newly Registered User
 
Join Date: May 2014
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
ebbsamsung is on a distinguished road
Button in subform with two routine

Dear Experts,

Good day!

I actually had a problem in my db. My boss want me to make a button in a subform detail to open specific pdf file based on path listed in table named "tblPDF" and concatenate a field DocumentNo in subform. I've done this part using the code i found in a forum. But now, my boss want as well if the pdf file not there, it will open the window explorer setfocus to the folder name identical to the DocumentNo.

Is is possible to please modify the code i found to fit the need of my boss?
I am very sorry, as a novice like i need your help for this matter.

I attached herein the screenshots of the table, form with subform and a directory folder.

Here is the code i used so far to open pdf file

Thank you in advance experts!



Code:
Private Sub cmdOpenPDF_Click()

Dim colFiles As New Collection
Dim direc As String
Dim filetosearch As String
filetosearch = Me.No & "_" & Me.DocumentNo & "_" & Me.RevisionNo & ".pdf"


direc = Nz(DLookup("PDFsFolder", "tblPDF", "PdfId = 14"), "")
RecursiveDir colFiles, direc, filetosearch, True
Dim vFile As Variant
For Each vFile In colFiles
OpenAnyFile (vFile)
Next vFile

End Sub


Function OpenAnyFile(strPath As String)
If FileThere(strPath) Then
MsgBox ("Open PDF file for " & No & "_" & DocumentNo & "_" & RevisionNo & "?")
FollowHyperlink strPath
Else
MsgBox ("File not found")
End If
End Function
Function FileThere(fileName As String) As Boolean
If (Dir(fileName) = "") Then
FileThere = False
Else
FileThere = True
End If
End Function
Attached Images
File Type: jpg PopupfolderbasedonDocNo.jpg (100.7 KB, 7 views)
File Type: jpg SubfolderOpen0.jpg (98.7 KB, 6 views)
File Type: jpg SubfolderOpen1.JPG (63.6 KB, 6 views)

ebbsamsung is offline   Reply With Quote
Old 04-17-2018, 01:20 AM   #2
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,669
Thanks: 52
Thanked 1,849 Times in 1,766 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Button in subform with two routine

modify this part to:

For Each vFile In colFiles
OpenAnyFile (vFile, direc)
Next vFile
...
...
...



Function OpenAnyFile(strPath As String , direc as string)
If FileThere(strPath) Then
MsgBox ("Open PDF file for " & No & "_" & DocumentNo & "_" & RevisionNo & "?")
FollowHyperlink strPath
Else
MsgBox ("File not found")
FollowHyperlink direc
End If
End Function
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-17-2018, 01:29 AM   #3
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,448
Thanks: 71
Thanked 1,089 Times in 1,017 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Button in subform with two routine

Cross posted with answers at http://www.accessforums.net/showthread.php?t=71629

If you do cross post, please state you have done so & provide a link at each forum

I expect arnel's solution will work, but if not, here's an alternative for you to adapt:

Button code for your form:
Code:
Private Sub cmdOpenFileOrFolder_Click()

On Error GoTo Err_Handler

    Dim strFileName As String, strFolderName As String, strFilePath As String

   
    'enter details of your file name & folder name e.g. using DLookup
    'concatenate to get the full older path 
    strFilepath = strFolderName & "\" & strFileName
    
    'Check file exists
    If Dir(strFilename) <> "" Then 
	Statusbar ("Opening the file, please wait. . .") 'show message while file opens
    	Call fHandleFile(strFilePath, WIN_NORMAL)
    	Statusbar 'clear message
    Else 'file missing . . . show error message
       Shell "C:\WINDOWS\explorer.exe """ & strFolderName & "", vbNormalFocus 
    End If
    
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.number & " in cmdOpenFileOrFolder_Click procedure :             " & _
        Err.description, vbCritical, "Program error"
    Resume Exit_Handler
End Sub
NOTE: fHandleFile is another generic function to open any file using the default file handler.
Place this code in a module:

Code:
Option Compare Database
Option Explicit

Dim fso As Object

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

Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 2            'Open Maximized
Public Const WIN_MIN = 3            'Open Minimized

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&

'---------------------------------------------------

Function fHandleFile(stFile As String, lShowHow As Long)

On Error GoTo Err_Handler

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:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                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)
                
Exit_Handler:
    Exit Function
    
Err_Handler:
    MsgBox "Error " & Err.number & " in fHandleFile procedure : " & Err.description, vbOKOnly + vbCritical
    Resume Exit_Handler

End Function

'Now all you need to do is call the application with the path of the file and let Windows do the rest.
'This code can be used to start any registered applications, including another instance of Access.
'If it doesn't know what application to open the file with, it just pops up the standard "Open With.." dialog.
'It can even handle URL's and mailto:

'Open a folder:
'  fHandleFile("C:\TEMP\",WIN_NORMAL)

'Call Email app:
'  fHandleFile("mailto:bpo@yahoo.com",WIN_NORMAL)

'Open URL:
' fHandleFile("http://uk.yahoo.com";, WIN_NORMAL)

'Handle Unknown extensions:
' fHandleFile("C:\TEMP\TestThis",Win_Normal)

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old 04-17-2018, 02:57 AM   #4
ebbsamsung
Newly Registered User
 
Join Date: May 2014
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
ebbsamsung is on a distinguished road
Re: Button in subform with two routine

Sir Ridders,

I adapted the code you've posted and this is what ive got so far.
I was able to open the window explorer directing to the path mentioned in tblFolder.
The original path or location of the folder is like this:

S:\Final Document for Project\Part 2. Procurement Manual\Section 1_Mechanical\Subsection 1-1_GT, GTG & ST, STG_Siemens AG\

But what i did in the tblFolder was, I shorten the path like this:
S:\Final Document for Project\Part 2. Procurement Manual\Section 1_Mechanical\

The purpose of this is when the user click the button, the code runs same as recursive maybe, I dont know whats the exact word to say but something like that looking the exact folder in subfolder and open in window explorer

This is the code you've given and i tried but the window explorer is opening the My Documents

Sorry I am a novice of this Access and VBA,

Code:
Private Sub cmdOpenFolder_Click()
On Error GoTo Err_Handler

    Dim strFileName As String, strFolderName As String, strFilePath As String
    strFileName = Me.No & "_" & Me.DocumentNo & "_" & Me.RevisionNo
    strFolderName = Nz(DLookup("PDFsFolder", "tblFolder", "PdfId = 1"), "")
   
    'enter details of your file name & folder name e.g. using DLookup
    'concatenate to get the full older path
    strFilePath = strFolderName & "\" & strFileName
    
    'Check file exists
    If Dir(strFileName) <> "" Then
        'StatusBar ("Opening the file, please wait. . .") 'show message while file opens
        Call fHandleFile(strFilePath, WIN_NORMAL)
        'StatusBar 'clear message
    Else 'file missing . . . show error message
       Shell "C:\WINDOWS\explorer.exe """ & strFolderName & strFileName & "", vbNormalFocus
    End If
    
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in cmdOpenFolder_Click procedure :             " & _
        Err.Description, vbCritical, "Program error"
    Resume Exit_Handler
End Sub
ebbsamsung is offline   Reply With Quote
Old 04-17-2018, 03:56 AM   #5
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,448
Thanks: 71
Thanked 1,089 Times in 1,017 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Button in subform with two routine

Quote:
the code runs same as recursive maybe
Sorry - no idea what that means

You've altered my code but not done so correctly:
Part of the problem is that you may have 2 backslashes

Add the 3 debug lines shown in BLUE so you can see what you are using
1. Do you have .pdf at the end of strFileName? If not add it as shown in RED
2. If you have a backslash at the end of strFolderName then either remove it there or remove it in the strFilePath concatenation code (the part in RED)
3. The Shell line should not include the strfilename part (again in RED). Remove that - what I wrote before was correct

Code:
Private Sub cmdOpenFolder_Click()
On Error GoTo Err_Handler

    Dim strFileName As String, strFolderName As String, strFilePath As String
    strFileName = Me.No & "_" & Me.DocumentNo & "_" & Me.RevisionNo & ".pdf"
    strFolderName = Nz(DLookup("PDFsFolder", "tblFolder", "PdfId = 1"), "")

    Debug.Print strFolderName
    Debug.Print strFileName
   
    'enter details of your file name & folder name e.g. using DLookup
    'concatenate to get the full older path
    strFilePath = strFolderName & "\" &  strFileName

    Debug.Print strFilePath
    
    'Check file exists
    If Dir(strFileName) <> "" Then
       Call fHandleFile(strFilePath, WIN_NORMAL)
    Else 'file missing . . . show folder
       Shell "C:\WINDOWS\explorer.exe """ & strFolderName  & strFileName & "", vbNormalFocus
    End If
    
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in cmdOpenFolder_Click procedure :             " & _
        Err.Description, vbCritical, "Program error"
    Resume Exit_Handler
End Sub
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old Yesterday, 03:03 AM   #6
ebbsamsung
Newly Registered User
 
Join Date: May 2014
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
ebbsamsung is on a distinguished road
Re: Button in subform with two routine

Sir Ridders,

Sorry for the late reply, I actually follow what you've said and the code working fine to pop the window explorer but the pdf file not. Maybe I miss something or what, could you please check sir.

' This is a concatenation of fields in a subform with an addition of particular file extension to find

strFileName = Me.No & "_" & Me.DocumentNo & "_" & Me.RevisionNo & ".pdf"

'In this part the path is not fully listed in the table and it goes like this only:
(S:\Final Document for Project\Part 2. Procurement Manual\Section 1_Mechanical)
The full path is:
(S:\Final Document for Project\Part 2. Procurement Manual\Section 1_Mechanical\Subsection 1-1_GT, GTG & ST, STG_Siemens AG)
for one file in the subform list only. This is the reason why I purposely listed the path in a table not in full because not all file pointed to one directory or folder.

strFolderName = Nz(DLookup("PDFsFolder", "tblFolder", "PdfId = 1"), "")

Is it possible the code routine runs like this:

Find first using the strFoldername & strFileName and pop up if there is a pdf file and if not open the subfolder named the same as strFileName without the addition of ".pdf" extention


Thank you in advance

Code:
Private Sub cmdOpenFolder_Click()
On Error GoTo Err_Handler

    Dim strFileName As String, strFolderName As String, strFilePath As String
    strFileName = Me.No & "_" & Me.DocumentNo & "_" & Me.RevisionNo & ".pdf"
    strFolderName = Nz(DLookup("PDFsFolder", "tblFolder", "PdfId = 1"), "")

    Debug.Print strFolderName
    Debug.Print strFileName
   
    'enter details of your file name & folder name e.g. using DLookup
    'concatenate to get the full older path
    strFilePath = strFolderName & "\" &  strFileName

    Debug.Print strFilePath
    
    'Check file exists
    If Dir(strFileName) <> "" Then
     ' This part I want to open the pdf file if available
       Call fHandleFile(strFilePath, WIN_NORMAL)
    Else 'file missing . . . show folder
      'This part if no pdf  file then pop up the window explorer pointing to the foldername the same  as pdf file. This is because the file name of the pdf file is same also  with the foldername
       Shell "C:\WINDOWS\explorer.exe """ & strFolderName  & "", vbNormalFocus
    End If
    
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in cmdOpenFolder_Click procedure :             " & _
        Err.Description, vbCritical, "Program error"
    Resume Exit_Handler
End Sub
ebbsamsung is offline   Reply With Quote
Old Yesterday, 04:54 AM   #7
ebbsamsung
Newly Registered User
 
Join Date: May 2014
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
ebbsamsung is on a distinguished road
Re: Button in subform with two routine

Sir Ridders,

First, I modified the path and the If statement by adding "*" in it and its working fine to pop up the pdf file and if nothing there, the last part of the code wont pop up the window explorer pointing to the folder.

Second, if i retain as is your code, it will not pop up the pdf file even if its there instead it pop up the window explorer pointing to the folder.


Dim strFileName As String, strFolderName As String, strFilePath As String
strFileName = Me.No & "_" & Me.DocumentNo & "_" & Me.RevisionNo & ".pdf"
strFolderName = Application.HyperlinkPart(FileLocation)
'enter details of your file name & folder name e.g. using DLookup
'concatenate to get the full older path
strFilePath = strFolderName & "" & strFileName



'Check file exists
If Dir(strFileName) <> "*" Then
Call fHandleFile(strFilePath, WIN_NORMAL)
'StatusBar 'clear message
Else 'file missing . . . show error message
Shell "C:\WINDOWS\explorer.exe """ & strFolderName & "", vbNormalFocus
End If


Exit_Handler:
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " in cmdOpenFolder_Click procedure : " & _
Err.Description, vbCritical, "Program error"
Resume Exit_Handler
End Sub

ebbsamsung is offline   Reply With Quote
Old Yesterday, 06:23 AM   #8
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,448
Thanks: 71
Thanked 1,089 Times in 1,017 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Button in subform with two routine

OK - you've still made mistakes in adapting my code
However I also made a mistake - apologies which is why you used the "*"
The Dir line should read Dir(strFilePath) <> "" - please amend

Corrected version below (changes in RED)
I've fixed the Dir line & also added code to handle path depending on whether folder name has a trailing "" or not
If you use this in its entirety it should work

Having said that I've never used HyperlinkPart myself....

Code:
Private Sub cmdOpenFolder_Click()

On Error GoTo Err_Handler

    Dim strFileName As String, strFolderName As String, strFilePath As String
         
   strFileName = Me.No & "_" & Me.DocumentNo & "_" & Me.RevisionNo & ".pdf"
   strFolderName = Application.HyperlinkPart(FileLocation)

    'concatenate to get the full folder path 
    'check for trailing backslash

   If Right(strFilePath, 1) = "\" Then
          strFilePath = strFolderName & strFileName
    Else 'add missing backslash
          strFilePath = strFolderName & "\" & strFileName
    End If
    
    Debug.Print strFilePath

    'Check file exists
    If Dir(strFilePath) <> "" Then
          Call fHandleFile(strFilePath, WIN_NORMAL)
    Else 'file missing . . . show folder
          Shell "C:\WINDOWS\explorer.exe """ & strFolderName & "", vbNormalFocus
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.number & " in cmdOpenFolder_Click procedure :             " & _
        Err.description, vbCritical, "Program error"
    Resume Exit_Handler
    
End Sub
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Old Yesterday, 12:59 PM   #9
ebbsamsung
Newly Registered User
 
Join Date: May 2014
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
ebbsamsung is on a distinguished road
Smile Re: Button in subform with two routine

Sir Ridders,

First and foremost, allow me to re-post the successful guidance of Sir Ridders.Thank you so much for your patience and sharing your expertise to get the exact code for my db.

Anyway, the code was running perfectly, so for those who encounter like this problem, this is what i did with the aide of Sir Ridders.

Maybe this will help you.

1st Part, I made a table that holds the record

Fields Name:
No
DocumentNo
RevisionNo

FileLocation------this hold the path of the file or folder

2nd Part, a continuous form with a record source of the table in 1st part
3rd Part, a button (cmdOpenFolder) in a subform detail section
4th Part, in a button copy the code below and put in "On Click" event

Code:
Private Sub cmdOpenFolder_Click()

    Dim strFileName As String, strFolderName As String, strFilePath As String
    
    'strFileName holds the concatenation of fieds in a subform
    strFileName = Me.No & "_" & Me.DocumentNo & "_" & Me.RevisionNo & ".pdf"
   'strFolderName holds the the path (FileLocation) 
   strFolderName = Application.HyperlinkPart(FileLocation)
    
    'concatenate to get the full folder path
    'check for trailing backslash
    
    If Right(strFilePath, 1) = "\" Then
        strFilePath = strFolderName & strFileName
        
    Else ' add missing backslash
        strFilePath = strFolderName & "\" & strFileName
    
    End If
    
    Debug.Print strFilePath
    
    'Check if file exists
    If Dir(strFilePath) <> "" Then
        Call fHandleFile(strFilePath, WIN_NORMAL)
        'StatusBar 'clear message
    Else
       'file missing . . . show error message
       Shell "C:\WINDOWS\explorer.exe """ & strFolderName & "", vbNormalFocus
    End If
   
    
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in cmdOpenFolder_Click procedure : " & _
        Err.Description, vbCritical, "Program error"
    Resume Exit_Handler
End Sub
Copy this code to a module:

Code:
Option Compare Database
Option Explicit

Dim fso As Object

 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

Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 2            'Open Maximized
Public Const WIN_MIN = 3            'Open Minimized

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&

'---------------------------------------------------

Function fHandleFile(stFile As String, lShowHow As Long)

On Error GoTo Err_Handler

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:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                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)
                
Exit_Handler:
    Exit Function
ebbsamsung is offline   Reply With Quote
Old Yesterday, 01:19 PM   #10
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 4,448
Thanks: 71
Thanked 1,089 Times in 1,017 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Button in subform with two routine

Think you're overdoing the Sir bit ....
Glad its finally working for you....

However you've omitted the following line needed for error handling
Code:
On Error GoTo Err_Handler
Place it as the 2nd line - above the Dim line

You can make this MUCH more useful by placing the first procedure in a module and changing it to a Public Function so it can be called from anywhere

Code:
Public Function OpenFileFolder(strFolderName as String, strFileName As String)

On Error GoTo Err_Handler

    Dim strFilePath As String

     'concatenate to get the full folder path
    'check for trailing backslash
    
    If Right(strFilePath, 1) = "\" Then
        strFilePath = strFolderName & strFileName        
    Else ' add missing backslash
        strFilePath = strFolderName & "\" & strFileName    
    End If
    
   ' Debug.Print strFilePath
    
    'Check whether file exists
    If Dir(strFilePath) <> "" Then
        'file exists so open it
        Call fHandleFile(strFilePath, WIN_NORMAL)
    Else
       'file missing . . . open folder instead
       Shell "C:\WINDOWS\explorer.exe """ & strFolderName & "", vbNormalFocus
    End If
   
Exit_Handler:
    Exit Function

Err_Handler:
    MsgBox "Error " & Err.Number & " in OpenFileFolder procedure : " & _
        Err.Description, vbCritical, "Program error"
    Resume Exit_Handler
End Function
Now you can use this repeatedly wherever you like in your database using a single line of code like this:
Code:
OpenFileFolder "C:\MyFiles\PDF_Files", "ExampleFile.pdf"

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ridders is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
going from sub routine to original routine errors occure Robert M Modules & VBA 2 05-05-2010 01:50 PM
Get a sub routine to run another sub routine - Both on 2 different sub forms. gbfielding Modules & VBA 2 12-09-2009 05:12 PM
Save-Button routine csdrex87 Modules & VBA 5 07-08-2009 08:47 AM
activate button('s sub routine) from other form John Nelson Modules & VBA 2 01-28-2005 07:53 AM
Exitting a Command Button's Sub Routine lewando_bria Modules & VBA 4 08-12-2002 07:17 AM




All times are GMT -8. The time now is 05:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World