Modify Access VBA to create another range of folders

Hi,

 Attached is a database program which allows scan operator to create a bunch of sequentially numbered subfolders.

 Select User Warren Nelson, Password is Welcome.

 The beginning pathway exists in the table t_User_Scan_Paths, and a TempVars is set to the path for the logged on user.

 While I can get the original range of folders created, if the scanner realizes they need more folders than were originally created, I would like to be able to add that range of folders, too.

 The Form is f_Create_Folder_Structures, and here is the command button named cmd_Create_Folder_Structure On Click Event...

Option Compare Database 
Option Explicit 
Private Sub Form_Load() 
    Me.txt_Main_Scan_Path = TempVars!ScanPath 
End Sub 
Private Sub cmd_Create_Folder_Structure_Click() 
    Dim strBaseFolder As String 
    Dim strFolder     As String 
    Dim i             As Integer 
    On Error GoTo ProcErr 
    strBaseFolder = TempVars!ScanPath 
    strBaseFolder = _ 
    strBaseFolder & _ 
    "\" & Format(Date, "yyyy\-mm\-dd") ' This is an example of how other levels could be added
    If CheckPath(strBaseFolder) = False Then 
        MsgBox "The base path could not be created." & vbNewLine & _ 
        "Be sure the path does not end with a backslash." 
        Exit Sub 
    End If 
    For i = Val(Me.[txt_Start_Folder_Number]) To Val(Me.[txt_End_Folder_Number]) 
        strFolder = strBaseFolder & "\" & i 
        If FolderExists(strFolder) Then 
            MsgBox strFolder & " already exists." 
        Else 
            MkDir strFolder 
        End If 
    Next i 
EndProc: 
    Exit Sub 
ProcErr: 
    MsgBox "Error " & Err.Number & "(" & Err.Description & ") in cmd_Create_Folder_Structure_Click" 
    Resume EndProc 
End Sub 

Open in new window


I am very new to VBA and could really use some help !

Thank You very much !

Bill
ScanPaths-v-5.zip
Bill NelsonITAsked:
Who is Participating?
 
Bill PrewCommented:
Try using this version of that routine.  As it was coded, it would only return True if it had to make some directories.  I added a check to see if the folder passed in already exists, and if so return True.  That should allow the higher level logic to behave as we need it to.

Public Function CheckPath (ByVal PathToCheck) As Boolean
  
  Dim strPath() As String   ' Array of each level in the path
  Dim strTest   As String   ' The path assembled one level at a time for testing
  Dim i         As Integer
  
  On Error GoTo CheckPath_Error

  If FolderExists(PathToCheck) = True Then
    CheckPath = True
    Exit Function
  End If

  strPath = Split(PathToCheck, "\")
   
  For i = 0 To UBound(strPath)
    strTest = strTest & strPath(i) & "\"
    If FolderExists(strTest) = False Then
        MkDir strTest
        If i = UBound(strPath) Then
            CheckPath = True
        End If
    End If
  Next i

ProcExit:
  Exit Function

CheckPath_Error:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & "in CheckPath, modADDLogonDetails"
  Resume ProcExit
  
End Function

Open in new window


»bp
0
 
Bill PrewCommented:
If they can get back that form and use it again, can't they just specify the new from and to range and let them be created?  So if they created 1 to 100 on initial use, this time they enter 101 to 200 for example.  It looks like the code would work.


»bp
0
 
Bill NelsonITAuthor Commented:
I thought that is what would happen but it triggers the error msg box "The base path could not be created. Be sure the path does not end with a backslash"
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Bill PrewCommented:
Can you paste CheckPath() code


»bp
0
 
Bill NelsonITAuthor Commented:
Sure...

Public Function CheckPath _
    (ByVal PathToCheck) _
  As Boolean
  
  Dim strPath() As String   ' Array of each level in the path
  Dim strTest   As String   ' The path assembled one level at a time for testing
  Dim i         As Integer
  
On Error GoTo CheckPath_Error

  strPath = _
    Split(PathToCheck, "\")
   
  For i = 0 To UBound(strPath)
    strTest = _
      strTest & strPath(i) & "\"
    If FolderExists(strTest) = False Then
        MkDir strTest
        If i = UBound(strPath) Then
            CheckPath = True
        End If
    End If
  Next i

ProcExit:
  Exit Function

CheckPath_Error:
  MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
         "in CheckPath, modADDLogonDetails"
  Resume ProcExit
  
End Function

Open in new window

0
 
Bill NelsonITAuthor Commented:
Hi Bill,

I am getting the following error when trying to debug:

Compile Error:
ByRef argument type mismatch

On the line 9:

If FolderExists(PathToCheck) = True Then

(PathToCheck) is highlighted...

Doyou have any ideas why I am getting this debug error?
0
 
Bill PrewCommented:
Try adding the data type to the parm on the Function definition, so:

Public Function CheckPath (ByVal PathToCheck As String) As Boolean


»bp
0
 
Bill NelsonITAuthor Commented:
That did the trick Sir !!! Thank you so much for getting this working for me I cannot thank you enough !!!!!!

Have a great evening and many many thanks for your appreciated help !!!

Bill
0
 
Bill PrewCommented:
Great, so glad to get you some help, thanks for the feedback.


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.