Solved Create directory if not exist and save report

M

Momma

Member
Local time
Tomorrow, 01:28
Joined
Jan 22, 2022
Messages
130
Hi All

I'm creating a report for each dog which I need to save in each dog's folder eg. C:\Dogs\DogID\Documents.
My code does not create the directory and I, therefore, get a run-time error '5' Invalid procedure call or argument on the following line
DoCmd.OutputTo acOutputReport, "rptCouncilNotification", acFormatPDF, NewFilePath & FileName

My other problem is the SQL Update which does not update the checkbox.
strSQL = "UPDATE tblDogsChecklist SET CouncilNotified = True" WHERE DogID = " & DogID & ";"
Where do I go wrong?

Code: 
Private Sub CmdReports_Click()

    Dim strSQL As String
    Dim i As Long
    Dim DocumentTypeID As Long
    Dim DogID As Long
    Dim NewFilePath As String
    Dim FileName As String
    Dim fso As Object
    
    If Forms!frmCouncilgrouplistbox!ContactList.ItemsSelected.Count < 1 Then
        Exit Sub
    End If
    
    For i = 0 To Forms!frmCouncilgrouplistbox!ContactList.ListCount - 1
        If Forms!frmCouncilgrouplistbox!ContactList.Selected(i) Then
        
            Call FolderExistsCreate("C:\Dogs\" & DogID & "\Documents", True)
        
            DogID = Forms!frmCouncilgrouplistbox!ContactList.Column(0, i)
            NewFilePath = "C:\Dogs\" & DogID & "\Documents"
            FileName = "CouncilNotification.pdf"
            
             'Create the Reports
            DoCmd.OpenReport "rptCouncilNotification", acViewPreview, , "DogID=" & Forms!frmCouncilgrouplistbox!ContactList.Column(0, i), acWindowNormal
            DoCmd.OutputTo acOutputReport, "rptCouncilNotification", acFormatPDF, NewFilePath & FileName

            DoCmd.SetWarnings False
            DocumentTypeID = 9
            DoCmd.RunSQL "INSERT INTO tblDocument (DogID, Filename, NewFilePath, DocumentTypeID) " & _
                "VALUES (" & DogID & ", """ & FileName & """,""" & NewFilePath & """," & DocumentTypeID & ")"
            DoCmd.SetWarnings True
            'Set fso = CreateObject("Scripting.FileSystemObject")
            'CopyFile FilePath, NewFilePath
            'If Not FileExists(NewFilePath) Then
            '    MsgBox "ERROR: File Copy Failed. " & NewFilePath
            '    Exit Sub
            'End If
            'Set fso = Nothing
        End If
        
        DoCmd.SetWarnings False
        DocumentTypeID = 9
        strSQL = "INSERT INTO tblDocument (DogID, Filename, NewFilePath, DocumentTypeID) " & _
            "VALUES (" & DogID & ", """ & FileName & """," & FilePath & """," & DocumentTypeID & ");"
        Debug.Print strSQL
        strSQL = "UPDATE tblDogsChecklist SET CouncilNotified = True WHERE DogID = " & DogID & ";"
        Debug.Print strSQL
        DoCmd.SetWarnings True
        
    Next
End Sub


Function FolderExistsCreate(DirectoryPath As String, CreateIfNot As Boolean) As Boolean
    Dim Exists As Boolean
    On Error GoTo DoesNotExist
    Exists = ((GetAttr(DirectoryPath) And vbDirectory) = vbDirectory)

    If Exists Then
        FolderExistsCreate = True
    Else
        ' Doesn't Exist Determine If user Wants to create
        If CreateIfNot Then
            MkDir DirectoryPath
            FolderExistsCreate = True
        Else
            FolderExistsCreate = False
        End If
    End If
    Exit Function
DoesNotExist:
    FolderExistsCreate = False
End Function
 
I see you are using the MkDir command, which is a valid approach. If you can set a breakpoint on that line, you can examine what is in DirectoryPath. You can also put a breakpoint on the DoCmd.OutputTo so that you can examine the values for each variable to be sure you are passing in the right values AND that they actually point somewhere.
 
The_Doc_Man said:
I see you are using the MkDir command, which is a valid approach. If you can set a breakpoint on that line, you can examine what is in DirectoryPath. You can also put a breakpoint on the DoCmd.OutputTo so that you can examine the values for each variable to be sure you are passing in the right values AND that they actually point somewhere.
Click to expand...
I appreciate your reply. I've set the breakpoints and it had a value of "" for NewFilePath.
I then added two more lines DogPath and DogIDPath and they all show the correct paths.
The Docmd.OutputTo also shows the correct path

I'm thinking the problem is somewhere in the Function FolderExistsCreate.
Would you have any idea why?

Code: 
            DogID = Forms!frmCouncilgrouplistbox!ContactList.Column(0, i)
            FilePath = "C:\Reports"
            Dogpath = "C:\dogs"
            DogIDPath = Dogpath & "\" & DogID
            NewFilePath = DogIDPath & "\Documents"
            FileName = "CouncilNotification.pdf"
            Call FolderExistsCreate(NewFilePath, True)
 
add backslash (\) to NewFilePath:

NewFilePath = "C:\Dogs\" & DogID & "\Documents\"

on your Update query, you only have it but not Actually Run the SQL, should be:

...
...
strSQL = "UPDATE tblDogsChecklist SET CouncilNotified = True WHERE DogID = " & DogID & ";"
Debug.Print strSQL
With DoCmd
.SetWarnings False
.RunSQL strSQL
.SetWarnings True
End With
 
The trick is to put a breakpoint in that code and single-step it to see what is not being set correctly. Looking at the code in post #3 I can tell you that DogID depends on the value of i so that had better be right. Then, DogIDPath depends on DogID so that also depends on i being right. Finally, NewFilePath, because it depends on DogIDPath, also depends on i.

I'm thinking that if you have nulls anywhere in that sequence, everything else becomes at least questionable. However, you say that the DogPath and DogIDPath look OK so that is a bit of a buzzkill. In the code in your first post, you have a a name being formed by concatenating two strings with a LONG. There, DogID is NOT a variant.

I see our friend arnelgp suggests closing the path definition. He is quite often right so I would give that a try. However, long-term, you should watch out for concatenation of LONG and STRING types together. They might work OK, but it is technically bad practice to do that kind of mixed-mode operation.

I also see that you are employing the "SetWarnings" operation in code that you are trying to debug. It is appropriate to use that when your code has been tested and proven, but for code under development, you WANT warnings. They are your friends. They might warn you of another problem that is caught in the middle of a no-warn code segment.
 
When I single-step through the code my paths are all correct. I replaced all the concatenating strings with the variables and they all work.
I also closed the path definition.
I also changed the code to check if the directory exists and it returns a False which means the directory should be created.
I now get a run-time error 76 Path not found on this line
MkDir NewFilePath


Code: 
Private Sub CmdReports_Click()

    Dim strSQL As String
    Dim i As Long
    Dim DocumentTypeID As Long
    Dim DogID As Long
    Dim FilePath As String
    Dim Dogpath As String
    Dim DogIDPath As String
    Dim NewFilePath As String
    Dim Filename As String
    Dim fso As Object
    
    If Forms!frmCouncilgrouplistbox!ContactList.ItemsSelected.Count < 1 Then
        Exit Sub
    End If
    
    For i = 0 To Forms!frmCouncilgrouplistbox!ContactList.ListCount - 1
        If Forms!frmCouncilgrouplistbox!ContactList.Selected(i) Then
                
            DogID = Forms!frmCouncilgrouplistbox!ContactList.Column(0, i)
            FilePath = "C:\Reports"
            Dogpath = "C:\dogs"
            DogIDPath = Dogpath & "\" & DogID
            NewFilePath = DogIDPath & "\Documents\"
            Filename = "CouncilNotification.pdf"
            
            If FolderExists(NewFilePath) = False Then
                MkDir NewFilePath
            End If
            
            'Create the Reports
            DoCmd.OpenReport "rptCouncilNotification", acViewPreview, , "DogID=" & Forms!frmCouncilgrouplistbox!ContactList.Column(0, i), acWindowNormal
            DoCmd.OutputTo acOutputReport, "rptCouncilNotification", acFormatPDF, NewFilePath & Filename
            
            DocumentTypeID = 9
            DoCmd.RunSQL "INSERT INTO tblDocument (DogID, Filename, NewFilePath, DocumentTypeID) " & _
                "VALUES (" & DogID & ", """ & Filename & """,""" & NewFilePath & """," & DocumentTypeID & ")"
        End If
        
        DocumentTypeID = 9
        strSQL = "INSERT INTO tblDocument (DogID, Filename, NewFilePath, DocumentTypeID) " & _
            "VALUES (" & DogID & ", """ & Filename & """," & FilePath & """," & DocumentTypeID & ");"
        Debug.Print strSQL
        strSQL = "UPDATE tblDogsChecklist SET CouncilNotified = True WHERE DogID = " & DogID & ";"
        Debug.Print strSQL
        With DoCmd
            .SetWarnings False
            .RunSQL strSQL
            .SetWarnings True
        End With
        
    Next
End Sub

Function FolderExists(strPath As String) As Boolean
On Error Resume Next
FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function
 
can you use a Custom MKDir?
see this change:
Code: 
Private Sub CmdReports_Click()

    Dim strSQL As String
    Dim i As Long
    Dim DocumentTypeID As Long
    Dim DogID As Long
    Dim FilePath As String
    Dim Dogpath As String
    Dim DogIDPath As String
    Dim NewFilePath As String
    Dim Filename As String
    Dim fso As Object
    
    If Forms!frmCouncilgrouplistbox!ContactList.ItemsSelected.Count < 1 Then
        Exit Sub
    End If
    
    For i = 0 To Forms!frmCouncilgrouplistbox!ContactList.ListCount - 1
        If Forms!frmCouncilgrouplistbox!ContactList.Selected(i) Then
                
            DogID = Forms!frmCouncilgrouplistbox!ContactList.Column(0, i)
            FilePath = "C:\Reports"
            Dogpath = "C:\dogs"
            DogIDPath = Dogpath & "\" & DogID
            NewFilePath = DogIDPath & "\Documents\"
            Filename = "CouncilNotification.pdf"
            
            'If FolderExists(NewFilePath) = False Then
            '    MkDir NewFilePath
            'End If
            
            'arnelgp
            Call forceMKDir(NewFilePath)
            
            'Create the Reports
            DoCmd.OpenReport "rptCouncilNotification", acViewPreview, , "DogID=" & Forms!frmCouncilgrouplistbox!ContactList.Column(0, i), acWindowNormal
            DoCmd.OutputTo acOutputReport, "rptCouncilNotification", acFormatPDF, NewFilePath & Filename
            
            DocumentTypeID = 9
            DoCmd.RunSQL "INSERT INTO tblDocument (DogID, Filename, NewFilePath, DocumentTypeID) " & _
                "VALUES (" & DogID & ", """ & Filename & """,""" & NewFilePath & """," & DocumentTypeID & ")"
        End If
        
        DocumentTypeID = 9
        strSQL = "INSERT INTO tblDocument (DogID, Filename, NewFilePath, DocumentTypeID) " & _
            "VALUES (" & DogID & ", """ & Filename & """," & FilePath & """," & DocumentTypeID & ");"
        Debug.Print strSQL
        strSQL = "UPDATE tblDogsChecklist SET CouncilNotified = True WHERE DogID = " & DogID & ";"
        Debug.Print strSQL
        With DoCmd
            .SetWarnings False
            .RunSQL strSQL
            .SetWarnings True
        End With
        
    Next
End Sub

Function FolderExists(strPath As String) As Boolean
On Error Resume Next
FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function


'arnelgp
'put this Function in separate module
Public Function forceMKDir(ByVal sPath As String)
    Dim v As Variant
    Dim s As String
    Dim i As Integer
    v = Split(sPath, "\")
    On Error Resume Next
    For i = 0 To UBound(v)
        s = s & v(i)
        VBA.MkDir s
        s = s & "\"
    Next
End Function
 
arnelgp said:
can you use a Custom MKDir?
see this change:
Code: 
Private Sub CmdReports_Click()

    Dim strSQL As String
    Dim i As Long
    Dim DocumentTypeID As Long
    Dim DogID As Long
    Dim FilePath As String
    Dim Dogpath As String
    Dim DogIDPath As String
    Dim NewFilePath As String
    Dim Filename As String
    Dim fso As Object
   
    If Forms!frmCouncilgrouplistbox!ContactList.ItemsSelected.Count < 1 Then
        Exit Sub
    End If
   
    For i = 0 To Forms!frmCouncilgrouplistbox!ContactList.ListCount - 1
        If Forms!frmCouncilgrouplistbox!ContactList.Selected(i) Then
               
            DogID = Forms!frmCouncilgrouplistbox!ContactList.Column(0, i)
            FilePath = "C:\Reports"
            Dogpath = "C:\dogs"
            DogIDPath = Dogpath & "\" & DogID
            NewFilePath = DogIDPath & "\Documents\"
            Filename = "CouncilNotification.pdf"
           
            'If FolderExists(NewFilePath) = False Then
            '    MkDir NewFilePath
            'End If
           
            'arnelgp
            Call forceMKDir(NewFilePath)
           
            'Create the Reports
            DoCmd.OpenReport "rptCouncilNotification", acViewPreview, , "DogID=" & Forms!frmCouncilgrouplistbox!ContactList.Column(0, i), acWindowNormal
            DoCmd.OutputTo acOutputReport, "rptCouncilNotification", acFormatPDF, NewFilePath & Filename
           
            DocumentTypeID = 9
            DoCmd.RunSQL "INSERT INTO tblDocument (DogID, Filename, NewFilePath, DocumentTypeID) " & _
                "VALUES (" & DogID & ", """ & Filename & """,""" & NewFilePath & """," & DocumentTypeID & ")"
        End If
       
        DocumentTypeID = 9
        strSQL = "INSERT INTO tblDocument (DogID, Filename, NewFilePath, DocumentTypeID) " & _
            "VALUES (" & DogID & ", """ & Filename & """," & FilePath & """," & DocumentTypeID & ");"
        Debug.Print strSQL
        strSQL = "UPDATE tblDogsChecklist SET CouncilNotified = True WHERE DogID = " & DogID & ";"
        Debug.Print strSQL
        With DoCmd
            .SetWarnings False
            .RunSQL strSQL
            .SetWarnings True
        End With
       
    Next
End Sub

Function FolderExists(strPath As String) As Boolean
On Error Resume Next
FolderExists = ((GetAttr(strPath) And vbDirectory) = vbDirectory)
End Function


'arnelgp
'put this Function in separate module
Public Function forceMKDir(ByVal sPath As String)
    Dim v As Variant
    Dim s As String
    Dim i As Integer
    v = Split(sPath, "\")
    On Error Resume Next
    For i = 0 To UBound(v)
        s = s & v(i)
        VBA.MkDir s
        s = s & "\"
    Next
End Function
Click to expand...
Hi Arnel, I've just created a button to create a directory. It creates a parent directory and if I extend it with a subfolder I get the Path not found error.
I also get an error on your Public Function
Compile error:
expected variable or procedure, not module
I've done a compile and got the same error.
 
first of all check if you have Right to create folder in Root directory (C:\).
also add a Breakpoint to your sub (in vba, goto the sub and press F9).
run your form and click the button.
press F8 to continue with your code.
see the value of each variable specially:

DogID = Forms!frmCouncilgrouplistbox!ContactList.Column(0, i)
 
arnelgp said:
first of all check if you have Right to create folder in Root directory (C:\).
also add a Breakpoint to your sub (in vba, goto the sub and press F9).
run your form and click the button.
press F8 to continue with your code.
see the value of each variable specially:

DogID = Forms!frmCouncilgrouplistbox!ContactList.Column(0, i)
Click to expand...
I've done all of that, Arnel. I have full rights and the values of all the variables are what they should be.
I did, however, found a database that I got when I did my training some time ago.

There are the two modules and I got it working with this line only
CheckFolder NewFilePath

It's just too hard for me to understand why it wouldn't work previously.
I really appreciate your help. Thank you!

Public Function CheckFolder(FolderName) As Boolean

Dim FN As String

FN = FolderName
CheckFolder = True
If Not FolderExists(FN) Then
MkDir FolderName
If Not FolderExists(FN) Then
MsgBox "ERROR! Could not create " & FN
CheckFolder = False
End If
End If

End Function


Code: 
Public Function FolderExists(Folder As String) As Boolean

    Dim s As String
    
    s = Dir(Folder, vbDirectory)
    If s = "" Then
        FolderExists = False
    Else
        FolderExists = True
    End If

End Function
 
Momma said:
My code does not create the directory and I, therefore, get a run-time error '5' Invalid procedure call or argument on the following line
Click to expand...
Code: 
   For i = 0 To Forms!frmCouncilgrouplistbox!ContactList.ListCount - 1
     If Forms!frmCouncilgrouplistbox!ContactList.Selected(i) Then
     ''using a variable DogID
        Call FolderExistsCreate("C:\Dogs\" & DogID & "\Documents", True)
     ''  the variable gets a value - the order of the lines of code is incorrect
        DogID = Forms!frmCouncilgrouplistbox!ContactList.Column(0, i)
 
Momma said:
I've done all of that, Arnel. I have full rights and the values of all the variables are what they should be.
I did, however, found a database that I got when I did my training some time ago.

There are the two modules and I got it working with this line only
CheckFolder NewFilePath

It's just too hard for me to understand why it wouldn't work previously.
I really appreciate your help. Thank you!

Public Function CheckFolder(FolderName) As Boolean

Dim FN As String

FN = FolderName
CheckFolder = True
If Not FolderExists(FN) Then
MkDir FolderName
If Not FolderExists(FN) Then
MsgBox "ERROR! Could not create " & FN
CheckFolder = False
End If
End If

End Function


Code: 
Public Function FolderExists(Folder As String) As Boolean

    Dim s As String
   
    s = Dir(Folder, vbDirectory)
    If s = "" Then
        FolderExists = False
    Else
        FolderExists = True
    End If

End Function
Click to expand...
You are never going to get that MSGBOX notification, as you create the folder if it does not exist?
Just add the MSGBOX line to the first If statement.
 
You must log in or register to reply here.

Similar threads

M
Solved Save filepath to table
Replies
6
Views
784
Momma
M
H
How to insert some data from Excel spreadsheet and the other data from Access combo boxes
Replies
6
Views
279
Pat Hartman
P
mloucel
Solved SubForm [Continuous Form] not updating data.
Replies
18
Views
916
mloucel
mloucel
N
Solved Record set too slow with SQL server Cloud & Parameters not working
Replies
15
Views
699
cheekybuddha
cheekybuddha
B
Overlapping dates with some null data
Replies
7
Views
682
bilakos93
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom