Delete created folders when deleting record

[psyc0tic1]

I am asking for help on another part of the attachments thread located here:
https://www.access-programmers.co.uk/forums/showthread.php?t=247009

I didn't want to add it to the bottom as it is already 3 pages long.

Ok... I have an attachments form opened via command button on a form that creates a directory based on the PONumber field on that form... and then creates another directory under that called Lab... and then creates another directory under that using the value from the PartNumber field on the original form... and uploads an attachment into that directory as well as creates the link in the table (tblLinks)

Option Compare Database
Option Explicit
Dim RecordID As Integer

Private Sub cmdBrowseToFile_Click()
    Dim fDialog As Object
    Dim varFile As Variant
    Dim savePath As String
    
 
   ' Set up the File Dialog.
    Set fDialog = Application.FileDialog(1)
 
    With fDialog
      'Set the title of the dialog box. '
        .Title = "Select the File..."
 
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
        If .Show = True Then
            For Each varFile In .SelectedItems
                savePath = GetDirectory() & "\" & GetFilenameFromPath(varFile)
                lstFiles.AddItem 0 & ";" & RecordID & ";" & varFile & ";" & savePath & ";" & GetFilenameFromPath(varFile)
            Next
        Else
            MsgBox "You clicked Cancel in the file dialog box.", vbOKOnly, "Select a File"
            Exit Sub
        End If
   End With
End Sub

Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'
 
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

Function GetDirectory() As String
    Dim strPath As String
    Dim partNum As String
    strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_home.Lab_Test_Input_Form.Form.PONumber

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

        strPath = strPath & "\" & "Lab"

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

        partNum = DLookup("PartNumber", "tbl_parts", "ID = " & Forms!frm_home.Lab_Test_Input_Form.Form.PartNumber)

        strPath = strPath & "\" & partNum

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If
    
    GetDirectory = strPath
End Function

Private Sub cmdSubmit_Click()
    Dim dbs As DAO.Database
    Dim strPath As String
    Dim varFile As Variant
    Dim i As Integer
    
    Set dbs = CurrentDb
    For i = 0 To lstFiles.ListCount - 1
        If lstFiles.Column(0, i) = 0 Then
            FileCopy lstFiles.Column(2, i), lstFiles.Column(3, i)
            dbs.Execute "INSERT INTO tblLinks (IRecordID, IPath, IDescription, Iissue, ICat) VALUES (" & lstFiles.Column(1, i) & ", '" & lstFiles.Column(3, i) & "', '" & lstFiles.Column(4, i) & "', '" & txtIssue.Value & "', '" & txtCat.Value & "');", dbFailOnError
        End If
    Next i
    
    txtIssue.Value = ""
    
    While Not lstFiles.ListCount = 0
        lstFiles.RemoveItem 0
    Wend
    
End Sub

Private Sub lstFiles_DblClick(Cancel As Integer)
    lstFiles.RemoveItem lstFiles.ItemsSelected(0)
End Sub

Currently if I delete a record in my main table (tbl_auditdata)that has a corresponding record in the child table (tblLinks) it will delete the record from both tables as expected.

However... this will not delete the folder it created using the PONumber value along with all sub folders.

I am wanting to make a form for my admin panel that will allow someone to look up a PO number and be able to delete that record from both tables and the directory(s) associated with it.

Currently I have this form which will let me search for a PO Number and it will display all records associated with it.

Option Compare Database
Option Explicit

Private Sub cmdRecordSearch_Click()
    Dim strsearch As String
    Dim Task As String

    If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
        MsgBox "Please type in your search keyword.", vbOKOnly, "Keyword Needed"
        Me.txtSearch.SetFocus
    Else
        strsearch = Me.txtSearch.Value
        Task = "SELECT * FROM tbl_auditdata WHERE ((PONumber Like ""*" & strsearch & "*""))"
        Me.RecordSource = Task
    End If
End Sub

Private Sub Form_Load()
    Dim Task As String

        Task = "SELECT * FROM tbl_auditdata WHERE (status)is null"
        Me.RecordSource = Task
        Me.txtSearch.SetFocus
End Sub

Starting with the code at the top... I need help on code to recognize all of the created folders and delete them when deleting a record

and then how to make the last form I showed change to be able to look up records by the PONumber and be able to choose a single record from the list and delete it (from both tables and the directories)

Does that make sense?

 

[The_Doc_Man]

First and foremost - it makes sense in one way, but my question is more along the lines of this: If the file names are unique and you want to delete them when done, why bother to make a new folder? Just keep one folder for staging attachments and empty it by selectively erasing the files once you have added them as attachments. Which I would presume that you know you should do if you are deleting the records. As long as the names are unique and predictable, one-folder-fits-all is not a terrible strategy. Now if you named things the same and use folders for differentiation, that would be another story. But you suggested that the names DO differ, so save yourself some work.

You can use the KILL verb (which you can look up) to kill a file given the fully qualified name to include device and path. There are also things you can do with the file system object to identify & verify & delete a file. Look up FileSystemObject (as a run-together term). If you search this forum, you will find a literal TON of stuff on FSO functions, and the MSDN documentation site has a lot of good stuff, too.

 

[psyc0tic1]

Now if you named things the same and use folders for differentiation, that would be another story.

Indeed... the users will name the attachments the same all the time i.e. IMG 1.jpg, IMG 2.jpg etc. which is the reason for the folder creations the way they are. I also do not want to delete any attachments ever unless a record needs to be deleted for whatever reason. I have reports that access and allow viewing of the attachments when needed to address issues with parts.

All that being said... I can delete records but do not want to leave orphaned attachments in the storage location.

The storage location being a directory named "Attachments". from there they are categorized by information pulled from the form like I explained above.

I will do some reading on the FSO