Solved Error making directory

[Morphies]

Morning all,

I'm trying to debug this code which worked perfectly with just the [Job Number] field as the folder name, I'm now trying to add in the customer name to the path but when the directory does not exist, the make directory function fails with a path not found error:

If I manually create the folder, it will open it just fine

I've added in the msg box to validate the path and it all looks OK?

Private Sub Command616_Click()
    If IsNull([Job Number].Value) Then
        MsgBox "Client Job Number cannot be blank", vbOKOnly
        Exit Sub
    Else
        Dim stAppName As String
        Dim CreateFolder As Integer
        Dim jFolder1 As String
        Dim jFolder2 As String
        Dim jFolder As String
        Dim ForbiddenChars As Variant
        Dim i As Integer

        'define array of forbidden characters to replace
        ForbiddenChars = Array("/", "\", ":", "*", "?", """", "<", ">", "|")

        'clean up Customer Name and Job Number
        jFolder1 = Me![Customer Name]
        jFolder2 = Me![Job Number]

        'replace forbidden characters in jFolder1 and jFolder2
        For i = LBound(ForbiddenChars) To UBound(ForbiddenChars)
            jFolder1 = Replace(jFolder1, ForbiddenChars(i), "-")
            jFolder2 = Replace(jFolder2, ForbiddenChars(i), "-")
        Next i

        'concatenate jFolder1 and jFolder2 to create final path
        jFolder = jFolder1 & "\" & jFolder2

        'check if folder exists
        If Dir("L:\Client_Images\" & jFolder, vbDirectory) <> "" Then
            stAppName = "C:\windows\explorer.exe L:\Client_Images\" & jFolder
            Call Shell(stAppName, 1)
        Else
            ' ask user if we are to create the folder
            CreateFolder = MsgBox("Folder does not exist! Create it?", vbYesNo)
            If CreateFolder = vbNo Then
                Exit Sub
            ElseIf CreateFolder = vbYes Then
                'create the folder and open in windows explorer
                MsgBox "L:\Client_Images\" & jFolder, vbOKOnly
                On Error GoTo Error_Handler
                MkDir "L:\Client_Images\" & jFolder & "\"
                stAppName = "C:\windows\explorer.exe L:\Client_Images\" & jFolder
                Call Shell(stAppName, 1)
            End If
        End If
    End If
    Exit Sub

Error_Handler:
    MsgBox "Error creating folder: " & Err.Description, vbCritical, "Folder Creation Error"
    Exit Sub
End Sub

 

[cheekybuddha]

You can only create one directory at a time.

Here's a function (untested) that should help you:

Function CreateDir(FullPath As String) As Boolean

  Const BS As String = "\"
  Dim folders As Variant, i As Integer, path As String
 
  If Len(FullPath) = 0 Then Exit Function
  folders = Split(FullPath, BS)
  For i = 0 To UBound(folders)
    path = path & IIf(i > 0, BS, vbNullString) & folders(i)
    If Len(Dir(path, vbDirectory)) = 0 Then
      MkDir path
    End If
  Next i
  CreateDir = (Err = 0)
 
End Function

Then adapt your code:

' ...
            ElseIf CreateFolder = vbYes Then
                'create the folder and open in windows explorer
                MsgBox "L:\Client_Images\" & jFolder, vbOKOnly
                On Error GoTo Error_Handler
                Call CreateDir("L:\Client_Images\" & jFolder & "\")
' ...

 

[Morphies]

Well every day's a school day.

Worked perfect, thanks.

 

[cheekybuddha]

Well every day's a school day.

Worked perfect, thanks.

Great! I wrote that code off the top of my head so there may be edge cases where it doesn't work.

It might be an idea to add error handling, though you do have it covered in this instance with the error handler in your calling sub.

 

[arnelgp]

oh the code is same as mine:
ForceMKDir()
Save Email to Directory | Access World Forums (access-programmers.co.uk)

Private Sub ForceMKDir(ByVal thePath As String)
    Const prefix As String = "\\"
    Dim var As Variant, s As String
    Dim i As Integer
   
    If Left$(thePath, 2) = prefix Then
        thePath = Mid$(thePath, 3)
        s = prefix
   End If
   
    var = Split(thePath, "\")
   
    On Error Resume Next
   
    For i = 0 To UBound(var)
   
        s = s & var(i)
        VBA.MkDir s
        s = s & "\"
       
    Next i
   
End Sub

 

[cheekybuddha]

@arnelgp Does a server path fail with MkDir()? I never tested.