Opening Folders from Acccess and If / Then

[Frankie_The_Flyer]

I'm still very new to this game and have been searching the Internet for bits and peices that I then build into something that generally works!
I'm now attempting to use a button on a form to open a sub-folder that is held in My Documents folder, "Customers".
The Sub-folder name is that of the customer as displayed on the form in the cell named "Entity"
Unfortunately as the name of the customer in the Entity cell may differ from that of the folder (could have the Pty or Ltd missing off the folder name).
I want the process to either open the folder if it's correctly named, or open the main folder if it isnt, so the user can search for the folder.
So far I have the following which will do the task but has a couple of issues.
1. it always brings up the warning "Path or File name is not found" if the folder is incorrectly named
2. If the folder is correctly named it not only opens the folder for the customer named in Entity, but goes on to open the "Customer" folder too.

Questions:
What code do I need to make the process open the folder for the Entity if the name in Entity and the name on the folder match; Or open the folder "Customer" if the name of the Entity and the name on the folder don't match?

Is there any way I can turn off the "Path or File Name" warning? (DoCmd doesn't seem to work)

Code I have is as follows

rivate Sub Command217_Click()

sPath = [Entity]

DoCmd.SetWarnings False

Shell "Explorer.exe " & "C:\Mydocuments\Customers\" & sPath, vbNormalFocus

If NotFound Then
Shell "Explorer.exe " & ""C:\Mydocuments\Customers\", vbNormalFocus

Else

Shell "Explorer.exe " & "C:\Mydocuments\Customers\" & sPath, vbNormalFocus

End If

DoCmd.SetWarnings True

End Sub

 

[Frankie_The_Flyer]

Help please Anybody ??

 

[TJPoorman]

Please use code tags when posting code (http://www.access-programmers.co.uk/forums/showthread.php?t=240420)

The reason your program is opening the folder twice is because you have the shell command outside your if statement as well as within.

I would recommend error trapping to check for the error code then change the path there. Something like the following:

Private Sub Command217_Click()
 
On Error GoTo errHandler
 
sPath = [Entity]
 
Shell "Explorer.exe " & "C:\Mydocuments\Customers\" & sPath, vbNormalFocus
 
Exit Sub
 
errHandler:
    If err.number = 1234 Then  [COLOR=red]'Change this to the error number[/COLOR]
[COLOR=#ff0000]       [COLOR=#000000]Shell "Explorer.exe " & ""C:\Mydocuments\Customers\", vbNormalFocus[/COLOR]
[/COLOR]   Else
        msgbox "There has been an error!"
    End If
End Sub

 

[Frankie_The_Flyer]

Thanks for the input TJ.
If the folder is not found, windows throws up a Warning "The Path C:\Mydocuments\Customers\Fred Blogs Pty does not exist or is not a Directory". I've searched the Internet, but can't find a suitable code for the warning.
Is there such a thing as Warning Handler rather than errHandler? And / or is there a code for the warning that I'm getting?

 

[MarkK]

You should check if the folder exists before you pass it to the Shell. Then you won't have to handle errors, and you can give your user very specific information about what is going on.

Check out the VBA.FileSystem.Dir() function or you can use the FolderExists() function of the FileSystemObject, but then you need to create the object first.

Here's an example of using FileSystemObject . . .

   dim tmp as string
[COLOR="Green"]   'check which folders exist [/COLOR]
   With CreateObject("FileSystemObject")
      If .FolderExists("C:\" & sPath) Then
[COLOR="Green"]         'try for the most detailed path[/COLOR]
         tmp = "C:\" & sPath
      Else
[COLOR="Green"]         'but if that fails, use one we know will work[/COLOR]
         tmp = "C:\"
      End If
   End With

[COLOR="Green"]   'here we only ever call the Shell once[/COLOR]
   Shell "Explorer.exe " & chr(34) & tmp & chr(34)

hth

 

[Frankie_The_Flyer]

Thanks lagbolt I'll give that a try. A customer folder will exist as it's put on file prior to enterint the data into this tracking database. The issue is that the name of the customer in the Entity cell may differ from that of the folder (could have the Pty or Ltd missing off the folder name) because the folders were set up by someone else some time ago.
Looks like your response may resolve the issue though. Watch this space............!!

UPDATE - Just run the above but it highlights "With CreateObject("FileSystemObject")". Am I missing something here?

 

[MarkK]

My mistake. The With block line should read . . .

With CreateObject("Scripting.FileSystemObject")

 

[Frankie_The_Flyer]

That works a treat Thanks lagbolt!

 

[MarkK]

You bet Frankie.