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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Bill PrewIT / Software Engineering ConsultantCommented:
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
Bill PrewIT / Software Engineering ConsultantCommented:
Can you paste CheckPath() code


»bp
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

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 PrewIT / Software Engineering ConsultantCommented:
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

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
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 PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
Great, so glad to get you some help, thanks for the feedback.


»bp
0
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
Databases

From novice to tech pro — start learning today.