Delete Selected Records from Form Question

[freezeb]

I have a form that I have create that has a list box set so that I can select drawing numbers from. I want to be able to delete those records from a table. I have the following code that I have placed in an event procedure On Click but it gives me this message when I try and run the code, "Update or CancelUpdate without AddNew or Edit."

This is my code.

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
On Error GoTo ErrorHandler
Set db = CurrentDb()
Set rs = db.OpenRecordset("testpackDetails_tbl", dbOpenDynaset)
'make sure a selection has been made
If Me.List9.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 Drawing"
Exit Sub
End If
'delete selected value(s) from table
Set ctl = Me.List9
For Each varItem In ctl.ItemsSelected
rs.delete
rs!testdetails_id = ctl.ItemData(varItem)
rs.delete
Next varItem
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub

 

[boblarson]

My method would be to do this:

Dim strIn As String
Dim varItm As Variant
Dim strSQL As String
 
For Each varItm in Me.YourListBoxNameHere.ItemsSelected
    strIn = strIn & Me.YourListBoxNameHere!IDFieldNameHere & ","
Next 
 
strIn = Left(strIn, Len(strIn)-1)
strIn = " Where [IDFieldNameHere] In (" & strIn & ")"
 
strSQL = "DELETE * FROM YourTableNameHere" & strIn
 
CurrentDb.Execute strSQL, dbFailOnError

And we're done quickly and painlessly.

 

[freezeb]

Thanks I added the code and changed the respective areas but I am getting and error 13 message with this part of the string.

strIn = strIn & Me.List9!TestDetails_id & ","

I am curious if it may have to do with the fact that there a multiple fields in List9 including TestDetails_id.

 

[boblarson]

Is your ID field a text field? If so you need to use quotes:


strIn = strIn & Chr(34) & Me.List9!TestDetails_id & Chr(34) & ","

 

[freezeb]

No the field is an autonumber. The query that the listbox gets it info from is as follows.
There are 3 tables linked in the query, drawings_tbl, Test_tbl, TestDetails_tbl. The list box works fine and allows me to filter the records for a Test and then select the drawings I want to remove from the test package. The table I want the records deleted from is TestDetails_tbl and the TestDetails_id column is the one that I need to use to deleted the rows not required in the test package.

 

[boblarson]

Your problem is you are trying to refer to a column by name from a listbox. Sorry I didn't notice it earlier. You have:

strIn = strIn & Me.List9!TestDetails_id & ","

but it should be:

strIn = strIn & Me.List9 & ","

where TestDetails_id should be set as the bound column of the listbox but if it isn't you refer to the correct column like this:

strIn = strIn & Me.List9.Column(1) & ","

Where Column(1) is the SECOND column (listboxes are zero based so the first column is 0 the second is 1 the third is 2, etc.