How Do You Do A Multiple Deletion With A CmdBtn On A Form?

[SteverZ]

I am seeking to find if there is a way to do a "multiple deletion" from a Form.

Using the below example, for Project 1234....When I bring up my form, and select 1234 from my Combo Box selection, and then click on the delete command button, all 1234's and their attributes, such as project, craft, unit, qty, etc, should be deleted from my table. (All 1234's and their attributes found in any row in the table should be deleted.)

What I am finding, even with the following code, that only one record can be deleted at a time, instead of all of them. Keep in mind, when I say "all of them" I mean the same type, which in this example, it's 1234. From below, Projects 9999,8888 and 4567, would not be deleted, but all instances of 1234 and their attributes would be.

Project Craft Qty Unit
1234 elec 1 ea
9999 inst 2 roll
1234 inst 3 roll
8888 elec 3 ea
1234 elec 6 roll
4567 inst 5 ea
8888 inst 4 ea
1234 mech 3 ea

For this I have one Form and one Table.

The following code is on a Form that I named "DelByProject" and it is not bound to any table or query. The Table name is "MaterialRecord", the Project number is simply named "Project", the combo box is named "Combo1", and my delete command button is named "CmdDelByProj".

Private Sub CmdDelByProj_Click()
Dim dbs As Database, SQLstr As String, txtProject As String

Set dbs = CurrentDb
txtProject = Me![Combo1] 'The current displayed ID
SQLstr = "DELETE * FROM MaterialRecord WHERE [Project] = " & "'" & txtProject & "'"
dbs.Execute SQLstr
dbs.Close
End Sub
______________
THANKS

 

[rafe]

just did a quick mock-up & it worked fine so you're on the right track. i lifted the query string from a query i created directly ... here's my code...

Private Sub Command2_Click()
DoCmd.RunSQL "DELETE * From Table1 WHERE (((Table1.field1)=" & Me.Combo0 & &quot);"
Me.Combo0.Requery
Me.Combo0 = ""
End Sub

hope this helps