Someone Must KNow How To Do This: How To Do A Multiple Deletion?

[SteverZ]

SOMEBODY MUST KNOW HOW TO DO THIS: How Do You Do A Multiple Deletion With A Command Button On A Form?

For this I have one Form that has one combo box and one command button, and there is one Table, in which the Form is not bound.

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. Additionally, this code doesn't even save the one deleted record, so the deleted record reappears.


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

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

 

[edge2000]

It looks Ok but the delete statement should be

MySQL = "Delete * FROM tbl_StockLocations WHERE LinkJobID =" & glJobID & ";"

You appear to have an extra & in place.

Have you run with a breakpoint on the delete line and checked the value of txtProject.
After break is made goto the Immediate window and type
?sqlstr
Copy the sql line and paste it into a query by form and check it runs.

Have you checked a relationship is not causing the problem, althogh an error should occur.

If you know all this then good luck!!

Dave

Dave
dab@completebs.com

http://www.completebs.com

See website for more info but we can develop most things for most people.

 

[SteverZ]

Thanks. Actually, to answer your "if you know all of this", actually, I don't. I am terrible at Access. What do you mean "'run a breakpoint"'? How should I make a relationship between this code and the table? Thank You.

 

[Rohdem]

Try this:

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

Set dbs = CurrentDb
txtProject = Me![Combo1]
Set rec = dbs.OpenRecordset("MaterialRecord", dbOpenDynaset)
Do While Not rec.EOF

If rec!Project = txtProject Then
rec.Delete
End If

rec.MoveNext
Loop

rec.Close
Set rec = Nothing
End Sub


Mike Rohde
rohdem@marshallengines.com

 

[edge2000]

If you change the module to the delete statement as suggested
SQLstr = "DELETE * FROM MaterialRecord WHERE [Project] = '" & txtProject & "' ;"

Try It - It may work.

If not in the module the there is a grey bar down the left hand side, if you click this at the line the delete statement is on a brown circl should appear, now run the code again and it should stop execution on the line and the statement will appear in yellow, hold you mouse ove the txtProject and check the value that appears is correct, now load the immediate window, icon on toolbar and type ?MySQL
Copy this and create a new query click sql and paste this in and try to ruin it, this should give you a clue as to what is happening.


Dave
dab@completebs.com

http://www.completebs.com

See website for more info but we can develop most things for most people.

 

[Durkin]

Just a couple of thoughts.
Is your 'Project' field a number? If it is you don't need quotes around it.
If it's a text field, is there any chance there are spaces in any of fields which would make them unequal ie. could your '1234' actually be '1234 '. To get around this use the Trim function like this:

SQLstr = "DELETE FROM MaterialRecord WHERE TRIM([Project]) = '" & Trim(txtProject) & "' ;"

One more thing I've just thought of. Check your combo box to see if the bound column is actually the column you want to retrieve the value from.

Durkin
alandurkin@bigpond.com