Creating Directory Folders Based On Query Results

[rustyCrVx]

I have a predefined query that retrieves specific client files with the following returned fields:
FileNo
FullName: [strLastName] & ", " & [strFirstName]

I want to make it so that folders are automatically created for every returned record as

rootClient & FileNo & " " & FullName

with string rootClient already declared "W:\blabla\". I've never dealt with anything like this so please be sure to explain thoroughly.

Thanks in advance.

 

[georgedwilkinson]

How about:

MkDir rootClient & FileNo & " " & FullName

in the appropriate VB event?

 

[rustyCrVx]

I finally got back to work on this and got it to work decently well except that one of the folders trying to be created has an invalid character. Here's my code at the moment:

Public Function fClientDir()
On Error GoTo Err_fClientDir
 
Dim clientRoot As String
Dim clientFolder As String
Dim db As Database
Dim rs As DAO.Recordset
 
Set db = CurrentDb
Set rs = db.OpenRecordset("qryClientDir")
 
rs.MoveFirst
 
clientRoot = "W:\blabla\"
clientFolder = clientRoot & rs![FileNo] & " " & rs![FullName]
 
Do Until rs.EOF
    If Not Dir(clientFolder, vbDirectory) <> "" Then
        MkDir (clientFolder)
    End If
 
    rs.MoveNext
    clientFolder = clientRoot & rs![FileNo] & " " & rs![FullName]
Loop
 
MsgBox "Folders have been created properly.", , "Success"
 
Exit_fClientDir:
    Set rs = Nothing
    Exit Function
 
Err_fClientDir:
    MsgBox Err.Description & vbCrLf & "Error creating:" & clientFolder, , "Error " & Err.Number
    Resume Exit_fClientDir
 
End Function

However I get this error:
Error 76
Path not found
Error creating: W:\blabla\9407107 Dvoretskii (L/C), Pavel P.

The "/" is obviously what's messing it up. How can I check clientFolder's text for invalid characters before attempting creation so I can delete those characters for folder creation but not touch the data?

 

[georgedwilkinson]

There are several methods. Using your code, you could set up an inner loop and trap for error 76. Stay in that loop until the error 76 goes away or the user says to skip the file (using msgbox with cancel option).

Dim SomeVariable As Boolean
    SomeVariable = True
    Do Until rs.EOF
        If Not Dir(clientfolder, vbDirectory) <> "" Then
            Do
                On Error Resume Next
                MkDir (clientfolder)
                If Err = 76 Then
                    SomeVariable = False
                    If MsgBox(clientfolder & " is not valid.  Do you want me to try " & Replace(clientfolder, "/", "-") & "?", vbOKCancel) = vbCancel Then
                        SomeVariable = True
                    Else
                        clientfolder = Replace(clientfolder, "/", "-")
                    End If
                End If
                Err.Clear
                On Error GoTo dowhateveryoudidbefore
            Loop While Not SomeVariable
        End If
        rs.MoveNext
        clientfolder = clientRoot & rs![FileNo] & " " & rs![FullName]
Loop

 

[rustyCrVx]

Ah that replace function is exactly what I needed and the rest of the coding you added was like what I had in mind. Thanks a lot George!

Although, I don't need it for this quick project, as that one record is the only one with an invalid character. Is it possible to create some array of characters to be checked for all at once and replaced using Replace() or something else without having to add multiple checks for each character? No rush on an answer for that though, I'm just curious in case it comes up in the future.

Again, thanks for the quick reply.

 

[rustyCrVx]

One change I had to make:

            Do
                ' I had to add this to actually step out of the loop
                SomeVariable = True
                On Error Resume Next
                MkDir (clientfolder)
                If Err = 76 Then
                    SomeVariable = False
                    If MsgBox(clientfolder & " is not valid.  Do you want me to try " & Replace(clientfolder, "/", "-") & "?", vbOKCancel) = vbCancel Then
                        SomeVariable = True
                    Else
                        clientfolder = Replace(clientfolder, "/", "-")
                    End If
                End If
                Err.Clear
                On Error GoTo dowhateveryoudidbefore
            Loop While Not SomeVariable

 

[georgedwilkinson]

I don't know of anything. I tried a couple of tricks but couldn't get it to work.

You could roll your own or nest Replace() statements.