using Windows Explorer within MS Access
The Plan:
in a construction company, there are a multitude of documents relating to any particular job. In order to easily retrieve those documents, I've created a shared Jobs drive that theoretically should ease the storage and retrieval of documents.
The Method:
On the "J:" drive I automatically create a sub-folder for each new customer as it is added, using an easily remembered version of the customer name. For example, for the J P Smith Company, I might create a sub-folder labeled "J:/SmithJP"
For each new job, I create a sub-folder within the customer sub-folder. The end result: "J:/SmithJP/11070"
Various applications create sub-folders within the Job folder for various types of documents: invoices, change orders, estimates, etc.
The Problem:
I'm told by the highest authority (the company president) that the job folder name is not specific enough. He wants to add his own descriptive information to the folder name so he can find a particular job more easily without having to do some other lookup of the job number (there are LOTS of jobs and one cannot always remember which is which).
He wants to change "J:/SmithJP/11070" to something like "J:/SmithJP/11070 4th and 5th Floor". He not only wants to do it, he does it.
Therefore, when I use the code I've written to open a window into the folder, it cannot find the folder in question.
____________________________________________________________
Here is the code to create the job folder. It assumes the Customer folder of which this job is a sub-folder has already been created:
Code:
Dim JPath As String, FindIt As Variant
Dim LookFile As String
Dim FileName As String
Dim myPath As String, MyPath2 As String, MyName As String, MyName2 As String
myPath = "J:\" & frmCustName
FileName = myPath & "\" & frmJobID
MyName2 = frmJobID
myPath = FileName
MyPath2 = "J:\" ' Set the path.
Relook:
MyName = Dir(myPath, vbDirectory) ' Retrieve the first entry.
If MyName = MyName2 Then GoTo DoneJob
If MyName = "" Then GoTo MakeDir
GoTo Relook
' create a sub-subdirectory labeled "Timesheets" for the Payroll App.
MakeDir:
MkDir myPath
MyPath2 = myPath & "\" & "Timesheets"
MkDir MyPath2
IsJobDir:
DoneJob:
To Open this folder using an Explorer window, I use the following code:
Code:
Dim RetVal, myPath As String
On Error GoTo BadFolder
myPath = "C:\WINDOWS\EXPLORER.EXE J:\" & CustSort & "\" & JobID
RetVal = Shell(myPath, 1)
GoTo DoneIt
BadFolder:
MsgBox ("Please Create the Folders for this Job")
DoneIt:
Is it possible to retrieve the path of the job folder after the folder name has been enhanced by a user? The first portion of the path remains the same. It just has supplemental descriptive information added.
An additional note: when the folder is not found by the retrieval module, Explorer brings up the user's Documents folder.
