Delete event triggers for moving files

[olxx]

I have a bit of struggle with delete events. I have table that consist filepaths and filenames. I need to delete those corresponding files (actually moved, but not relevant) if that record is deleted. if i use before delete event, user might cancel the operation but files are getting deleted. if i use Form_AfterDelConfirm the records are gone before event triggers and i have no info anymore which files to delete. Is there any way of getting set of records that are about to get deleted? I'm pretty sure i'm not the first guy trying to accomplish something like that.

 

[ebs17]

Information is lost when deleted.

It will therefore be practical to log information such as the record ID or the file path or into a buffer (variable, separate table) immediately before deleting records. This means that the files can then be deleted or moved in the second step.

 

[olxx]

Information is lost when deleted.

It will therefore be practical to log information such as the record ID or the file path or into a buffer (variable, separate table) immediately before deleting records. This means that the files can then be deleted or moved in the second step.

Well that was the question, how do i get the selected records that are about to be deleted? if it is one record then you can catch that currently selected record in OnDelete event before actually deleting. If there are more than one, how to get that recordset?

 

[jdraw]

How exactly do you identify what records should be deleted? Once you know what the selections/deletion criteria are, then you can set up the logic to do the delete/or move/or flag the desired records. I agree with ebs17 that physical delete would not be the first step. People often flag a record as "IsDeleted" or similar, in order not to lose history or data that may have other purpose(s).

 

[ebs17]

How do you delete such records? Using a filtered delete query?
With the same filter you can create a selection query beforehand and store the paths in a comma-separated string or an array, of course the same with a recordset loop.
You can create an array from the comma-separated string using a split. You can then use the array to act directly on the files.

//edit
In the same sense, @jdraw's proposal expands the possibilities.

 

[AHeyne]

How about that:
It assumes that you have a control named 'uxFilePath' in your form.

The module collection variable 'filePathsToDelete' will hold the file paths to be deleted and is filled by the 'Delete' event.

Then later the 'AfterDelConfirm' event procedure checks if the user commited the deletion and will delete each file path.
If the user didn't commit the collection of files will be resetted.

Private filePathsToDelete As Collection

Private Sub Form_Open(Cancel As Integer)
    ResetFilePathsToDelete
End Sub

Private Sub Form_Delete(Cancel As Integer)
    filePathsToDelete.Add Me.uxFilePath.Value
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
    If Status = 0 Then
        Dim filePath As Variant
        For Each filePath In filePathsToDelete
            Debug.Print "Delete '" & filePath & "' now..."
        Next filePath
    End If

    ResetFilePathsToDelete
End Sub

Private Sub ResetFilePathsToDelete()
    Set filePathsToDelete = New Collection
End Sub

 

[olxx]

Thanks for replies. I try to explain it a bit more. User form is a split form, that shows multiple records. One of the fields is filepath that contains file name and location. User can delete records from that form using standard built-in Access interface (lets say drag mouse over records and press delete key). Access will then prompt to confirm delete. If user presses yes, the records get deleted and corresponding files deleted/moved. At the moment, it works fine if user deletes just one record at the time.
Logic at the moment is:

1. Form_Delete event stores filepath of the current record to variable
2. Form_BeforeDelConfirm event checks if the file to be deleted is not open, cancels delete if file is open
3. Form_AfterDelConfirm checks if user pressed yes on prompt, and deletes file.

The problem is at first step. If i could get the recordset user selected for delete, i could solve this.
Yes, i could solve this by cloning all records to tempTable and compare them after deleting but this seems ridiculous.

 

[AHeyne]

What is wrong with my suggestion? Did you try it? It also handles the deletion of multiple records in the form.

 

[ebs17]

checks if the file to be deleted is not open, cancels delete if file is open

First you have to think about processes. What is the plan?
If the tenth file is open, is the cancellation for this one record aborted, or should the entire deletion process be reversed across all records and all files?

 

[arnelgp]

here is a demo.
open Form1 and enter the correct path+filename of file you have.
now when you delete it, it will be moved to C:\Moved folder.

 

[olxx]

Thanks for replies.
@ebs17 The whole delete process is canceled if any of the files is open.
@arnelgp Thanks for your demo. But i have no idea how to integrate that demo to my own code. Those scripting.dictionary objects and timers are way beyond my comprehension and vba skill. I´ll keep it as last resort if i can't make up easier solution.
@AHeyne Thanks for the code. Your approach is probably closest thing what i wanted so far. But i still couldn't get it to work because i forgot to mention one detail, filepath and filename are stored separately. Is there any way that i can save them both in collection?

 

[Gasman]

But i still couldn't get it to work because i forgot to mention one detail, filepath and filename are stored separately. Is there any way that i can save them both in collection?

Concatenate them?

 

[ebs17]

Form events usually only affect one record. Other measures will be taken for mass data processing.

lets say drag mouse over records

Are you using a selection via the record selector? You can use SelTop and SelHeight as properties of the form to evaluate the selection.
The following example transfers the selection to an additional bound checkbox.

' http://www.office-archive.com/3-ms-access/13b3380dd2222309.htm
Private Sub Form_Click()
On Error GoTo FormClickerr
   
    'MsgBox "SelHeight: " & Me.SelHeight & vbTab & "SelTop: " & Me.SelTop
    'MsgBox Me.CurrentRecord
   
    If Me.chkSelected.Visible = True Then    'Allows recordselectors to pick resource records for updates
        Dim i As Integer, SelectDirection As String, SelectNumber As Integer
        SelectNumber = Me.SelHeight         'the number of records chkSelected
        SelectDirection = "Up"             'default selection to start from bottom
        If Me.CurrentRecord = Me.SelTop And Me.SelHeight = 1 Then
            SelectDirection = "One"              'only 1 record chkSelected
        ElseIf Me.CurrentRecord = Me.SelTop And Me.SelHeight > 1 Then
            SelectDirection = "Down"              'selection was started from top
        ElseIf Me.NewRecord = True Then     'user included blank new record in selection(shame on them :-)
            DoCmd.GoToRecord acActiveDataObject, , acPrevious   'so lets go back one record
            SelectNumber = SelectNumber - 1    'minus 1 from the no. chkSelected & stick to default up direction
        End If
        'MsgBox SelectDirection
   
        Do While i < SelectNumber
            If IsNull(Me.ID) Then Exit Do
            If Me.chkSelected = True Then                '
                Me.chkSelected = False
            Else
                Me.chkSelected = True
            End If
            If (Me.CurrentRecord = 1 And Me.NewRecord = -1) Or Me.NewRecord = -1 Or i = SelectNumber - 1 Then
                Me.Refresh
                Exit Do        'we are out of the record range (all done)
            End If
            If SelectDirection = "Down" Then      'started from top move forward
                DoCmd.GoToRecord acActiveDataObject, , acNext
            ElseIf SelectDirection = "Up" Then     'started from bottom move Backward
                DoCmd.GoToRecord acActiveDataObject, , acPrevious
            End If
            Me.Refresh                    'refresh changes
            i = i + 1                     'increment I for loop
        Loop
    End If
FormClickerr:
    If Val(Err) = 3101 Or Val(Err) = 2105 Then    'user caught trying to create a inadvertant
        Me.Undo                                  'new record because a new record was in the selection
        Resume Next
    ElseIf Val(Err) <> 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If
End Sub

In the second step, it is then easy to go through the selection using a recordset loop, checking for open files and then deleting or moving the files and delete records.

 

[xavier.batlle]

@AHeyne Thanks for the code. Your approach is probably closest thing what i wanted so far. But i still couldn't get it to work because i forgot to mention one detail, filepath and filename are stored separately. Is there any way that i can save them both in collection?

Yes, you can store instances of classes with several attributes.

 

[olxx]

Concatenate them?

I tried filePathsToDelete.Add Me.uxFilePath.Value & Me.uxFileName.Value, gives error: object variable or with block variable not set.

 

[olxx]

OK, it seems that i managed to get it to work, thanks for pointing me the right direction @ebs17 and @AHeyne
If it has any use to anybody, here is the code:

Public filePathsToDelete As Collection

Private Sub Form_AfterDelConfirm(Status As Integer)
DoCmd.SetWarnings True
    If Status = acDeleteOK Then
        ' Verify that the filePathsToDelete collection is not empty
        If filePathsToDelete.Count > 0 Then
            
            Dim filePath As Variant
            For Each filePath In filePathsToDelete
                
                ' Call the moveFile function to move each file
                ....your function here
            Next filePath
        Else
            
        End If
    End If
    ' Reset the filePathsToDelete collection
    ResetFilePathsToDelete
End Sub

Private Sub PopulateFilePathsToDelete()
    Dim rsForm As DAO.Recordset
    Dim i As Long
    Dim fullPath As String
        ' Check if the form is open
    If Not CurrentProject.AllForms("yourform").IsLoaded Then
        MsgBox "Form is not open.", vbExclamation
        Exit Sub
    End If
    
    ' Check if the form has a record source
'    If Forms("yourform").RecordSource = "" Then
'        MsgBox "Form does not have a record source.", vbExclamation
'        Exit Sub
'    End If
    
    ' Create a new collection to store selected record values
    Set filePathsToDelete = New Collection
    
    ' Get the form's recordset
    Set rsForm = Forms("yourform").RecordsetClone
     If rsForm.EOF Then
        'MsgBox "Recordset is empty or disconnected.", vbExclamation
        Exit Sub
    End If
    ' Move to the first record in the recordset

    rsForm.MoveFirst
    rsForm.Move Me.SelTop - 1
    ' Loop through selected records
    For i = Me.SelTop To Me.SelTop + Me.SelHeight - 1
        ' Check if the recordset is at the end
        If Not rsForm.EOF Then
            
            ' Concatenate file path and filename to create full path
            fullPath = ...whatever your logic...
            filePathsToDelete.Add fullPath
            ' Move to the next record
            rsForm.MoveNext
        End If
    Next i
    
    ' Close the form's recordset
    rsForm.Close
    Set rsForm = Nothing
End Sub

Private Sub Form_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
PopulateFilePathsToDelete
End Sub

Private Sub ResetFilePathsToDelete()
    Set filePathsToDelete = New Collection
End Sub

Private Sub Form_Load()
ResetFilePathsToDelete
End Sub

 

[The_Doc_Man]

Thank you for posting the code that resulted from this. Others who have a similar problem will benefit.