Search for a directory

I want to determine if a particular directory exists while in an Access form (2003).

I am using the below code that works fine but I want to only search for one level of directory not all the sub directories in the folder.  The Main path is P:\Rental Jobs and under that directory is a directory for each job number in our access table  (like P:\Rental jobs\20456.  ) But I dd not want to look for any subfolders beyond the Job Number directory. I am not sure of the different settings available in the code.  Any suggestions ?

Public Function FindMyFolder(startfolder As String, searchname As String) As String

Static fso As scripting.FileSystemObject
If fso Is Nothing Then Set fso = New scripting.FileSystemObject

Dim folder As scripting.folder
Dim result As String

Set folder = fso.GetFolder(startfolder)

Dim subfolder As scripting.folder
For Each subfolder In folder.SubFolders
  If subfolder.Name = searchname Then
    result = subfolder.path
    GoTo done
    result = FindMyFolder(subfolder.path, searchname)
    Me.txtSearch = subfolder.path
    MsgBox ("Search Directory: " & txtSearch), vbInformation
    If result <> Empty Then GoTo done
  End If

FindMyFolder = result

End Function
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try using the following function:
Public Function FolderExists(strFolder As String) As Boolean
On Error GoTo ProcExit
  If (GetAttr(strFolder) And vbDirectory) = vbDirectory Then
    FolderExists = True
  End If
End Function

Open in new window

Simply pass it the full path to the folder you want to check and it will return True or False.

Best wishes,
Graham Mandeno [Access MVP 1996-2013]

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ChandlerBoundAuthor Commented:
Worked perfectly and quickly as  my method was way too slow!  Thank you for the quick response.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.