Deleting values from a table which are selected from a list box

[shabbaranks]

Hi Guys,

Im trying to delete values from a table based on the selected values of a listbox. The listbox values have a hidden column which related to the ID on the table to which I am trying to delete from (if that makes sense).

If I use the following code:

    Set ctl = Me.Results_listbox
    For Each varItem In ctl.ItemSelected
    CurrentDb.Execute "Delete * FROM Table1 WHERE Table1.ID = " & ctl.ItemData(varItem)
    Next varItem

I get an error stating Object doesn't support this property or method.

I used another posts code to which it seemed they were doing exactly what I am doing - any ideas why its not working?

Thanks!

 

[pbaldy]

That looks okay offhand. The ID field has a numeric data type, and is in the bound column of the listbox?

 

[shabbaranks]

That looks okay offhand. The ID field has a numeric data type, and is in the bound column of the listbox?

Thanks for the quick response. The bound column of the listbox is the ID field as here:

Dim strSQL As String
Dim strEmployee As String
strEmployee = Me.SelectEmployee_Combo.Column(2)
strSQL = "SELECT  Table1.ID, table1.[Task Date], table1.Hours, table1.Activity, table1.Project, table1.Description" & vbCrLf & _
"FROM table1 " & vbCrLf & _
"WHERE (((table1.[Task Date])>=[Forms]![AmendHrs_frm]![FrmDateSelector_txtbx] And (table1.[Task Date])<=[Forms]![AmendHrs_frm]![ToDateSelector_txtbx]) AND ((table1.sUser)=""" & strEmployee & """) AND ((table1.Approved)=False));"
Me.Results_listbox.RowSourceType = "table/query"
Me.Results_listbox.ColumnCount = "6"
Me.Results_listbox.ColumnWidths = "0cm;2.2cm;1.5cm;6cm;5cm;1cm"
Me.Results_listbox.RowSource = strSQL
Me.Results_listbox.ColumnHeads = True
Me.Results_listbox.BoundColumn = 1
Me.Results_listbox.Requery

As for the datatype for this field within the table is AutoNumber, I cant change it because its a primary key. Is that why its not working?

Thanks again

 

[pbaldy]

No, autonumber is a numeric type (Long Integer). Can you post the db here to play with?

 

[shabbaranks]

No, autonumber is a numeric type (Long Integer). Can you post the db here to play with?

Thanks for the reply, unfortunately I cant post the DB as it holds quite a lot of data and to clear it out to make it blank would take a while.

I'll continue to investigate and see if I can work it out.

 

[shabbaranks]

In these situations would you "generally" use a subform\table to hold the query values and then from that table delete from the main table?

I can achieve that quite easily but the list box idea would be a nicer way of dealing with this.

 

[shabbaranks]

Got it working,

Dim varSelection As Variant
Dim lngID As Long
If Me.Results_listbox.ItemsSelected.Count = 0 Then
Exit Sub
Else
For Each varSelection In Me.Results_listbox.ItemsSelected
lngID = Me.Results_listbox.Column(0, varSelection)
DoCmd.RunSQL "Delete FROM Table1 WHERE Table1.ID = " & lngID & ";"
Debug.Print lngID
Next varSelection
End If
Me.Results_listbox.Requery

 

[pbaldy]

Glad you got it sorted out.