Problem using rst.delete

[P5C768]

I'm trying to create a form to allow users to select specific inventory and delete it. I can't use a delete query because the ID I am using is not numeric and I would like users to be able to delete individual, selected or ranges of inventory.



Here is the coding I have so far:

Private Sub Delete_Inv_btn_Click()
    Dim ditem As Variant
    Dim dbCurrInvent As DAO.Database
    Dim rstInv As DAO.Recordset
    Set dbCurrInvent = CurrentDb
    Set rstInv = dbCurrInvent.OpenRecordset("Main_Inventory_tbl")
    If Forms!Delete_Inv_frm!TestID.ItemsSelected.Count <> 0 Then
        For Each ditem In Forms!Delete_Inv_frm!TestID.ItemsSelected
            rstInv.Delete
        Next ditem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub
    End If
End Sub

As far as I can tell, this isn't doing anything. I use similar coding to add records and that seems to work fine.

[pbaldy]

It looks like you're using a multiselect listbox? In any case, there's no connection between the recordset and the listbox. You'd have to navigate the recordset to the selected record in the listbox and then delete it. What I would do is execute delete SQL within the loop, using the loop variable in the WHERE clause.

[P5C768]

Thanks, I'll give that a try. I thought that was the problem but I wasn't sure how to approach deleting the record other than ret.delete.

[pbaldy]

No problem, post back if you get stuck. You can use the Delete method, but you have to navigate the recordset to the desired record. I find the SQL method simpler.