How to delete multiply selected list box records

[Alika]

Hi.
In an Access database, in a Form user selects a file name to delete it from some directory. user needs to be able to do multiple deletion at a time. I've changed the List Box property to Extended Multi Select, but I'm confused how to recode a Sub, so it will KILL all the selected files.
My code for Single selected row is:

Private Sub cmdDeleteFile_Click()
dim strPath as string
dim strPath="c:\\FilesToBeDeleted"
If Not IsNull(Me.lstFiles) Then
Kill strPath & "\" & Me.lstFiles
Else
MsgBox "No file was selected."
End If
End Sub

Thank you all in advance

 

[mary.h]

Hi,

you need to loop through the selected items and set an object var to the listbox.

The code is like this:

Private Sub cmdDeleteFile_Click()

dim varElem as Variant
dim lstCtl as Control

Const conPath="c:\\FilesToBeDeleted"

Set lstCtl = Me.lstFiles

If lstCtl.ItemsSelected.Count < 1 then MsgBox "No file was selected."

For Each varElem In lstCtl.ItemsSelected

Kill conPath & "\" & lstCtl.ItemData(varElem)

Next varElem

Set lstCtl = Nothing

End Sub


Hope this helps.
Mary.h

 

[Alika]

Hi,Mary.h

Mary, Many thanks! It works perfectly!
Thank you!

I have another question. Along with deleting files from the specified in strPath folder, the corresponding data needs to be deleted from Access table. One deleted file - one row in the table. Some times user needs to delete thousand of files (and therefore records). ‘For Each’ statement doesn't work well in this situation to delete rows from the table, because the MS Access prompts the message "1 row will be deleted from the specified table" during each iteration. It’s inappropriate to have user confirming action thousand times. Is it possible to avoid such MS Access message?
Could anybody advize me?
Thank you.

 

[jfgambit]

A simple suggestion would be to build a delete query based on the parameters you have selected and run the query at the end of the code with a Docmd.OpenQuery command.

HTH

 

[Alika]

A simple suggestion would be to build a delete query based on the parameters you have selected and run the query at the end of the code with a Docmd.OpenQuery command.
HTH

jfgambit, could you be any specific please?
i did it with
Docmd.RunSQL "Delete *
from tblTable where fileName='" & lstCtl.ItemData(varElem) & "'"

How can I assign the multiple selections to the Delete query?
Thank you.
Alika

 

[Alika]

I found an easy solution, may be it'll help somebody else:

Put the statement in the beginning of the Sub, or a Loop, whereever it makes more sence:

DoCmd.SetWarnings False

and then turn the Warnings On when it's proper, in the end of teh Sub or after a Loop, etc.

DoCmd.SetWarnings True

Thank you all very much for help.
Alika