MS Access throwing error 2001 when running VBA script to make a directory in a Macro or as sub scrip-CodePudding

I have the following VBA script in MS Access to check for a directory and make it if it does not exist:

Public Function MakeReportDirct()

Dim fDate As String
Dim sFolderPath As String
Dim oFSO As Object


fDate = Format(Now(), "YYYY-MM")
sFolderPath = "\\satco-file01\COMPANYSHAREDFOLDERS\Reporting\Nuvo\Daily Reports\" & fDate

Set oFSO = CreateObject("Scripting.FileSystemObject")

If oFSO.FolderExists(sFolderPath) Then
End

End If

MkDir sFolderPath

End Function

When I run the code alone in the VBA window, it works perfectly. However, when I try to run the code from a Macro or when I try to call from another VBA script:

Public Function ChartwellVendor()

Call MakeReportDirct
Call ChartwellVendorExport
Call ChartwellVendorEmail


End Function

It gives the "Macro Single Step" error:2001

I have enabled every location I can think of (the database location, the export location, the location the directory will be created) as a "Trusted Location".

Any help would be greatly appreciated!

CodePudding user response：

The End statement "Terminates execution immediately".

When you run MakeReportDirct alone, that accomplishes what you want: MkDir is not executed when the folder already exists.

But when you run it from within ChartwellVendor, you want other procedures to run after MakeReportDirct. However, the End statement terminates all further processing, so those other procedures don't run.

You could avoid that problem by replacing End with Exit Function. Then MakeReportDirct could terminate but still allow processing to continue with the next line in ChartwellVendor

Another approach would be to flip the logic of your If block condition. If the folder does not exist, run MkDir inside that code block.

If Not oFSO.FolderExists(sFolderPath) Then
    MkDir sFolderPath
End If