Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: How do I delete multiple rows from an listbox?

    How do I delete multiple rows from an access listbox which has its row source set to a table or query?

    I can add multiple items from a listbox to a table, but how do I delete multiple items from a table?

    Thankyou

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    A delete query would be one way:

    DELETE *
    FROM TableName
    WHERE Whatever
    Paul

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    That will just delete everything, I need to be able to choose certain rows from the listbox.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, it won't delete everything, it will delete anything that meets the criteria you specify. That could be a single record, as selected from a listbox.
    Paul

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    But that is just an sql statement and I want to be able to delete multiple records not just single records.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The ones selected in the listbox? You loop the selected items like you would to add, but delete instead.
    Paul

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    This is what I have to add records -
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ctl As Control
    Dim varItem As Variant
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblLinkUserVisits", dbOpenDynaset, dbAppendOnly)
    'make sure a selection has been made
    If Forms![visits_frm]!List71.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 user!"
    Exit Sub
    End If

    Set ctl = Forms![visits_frm]!List71
    For Each varItem In ctl.ItemsSelected
    rs.Delete

    rs!userID = ctl.ItemData(varItem)
    rs!visitID = Forms![visits_frm]![visitID]
    rs.Update
    Next varItem
    I tried rs.delete, but I get a runtime error 3021 no current record.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I didn't suggest a recordset, I suggested SQL. Deleting can be done with a recordset, but SQL is easier in my view. You've opened a recordset on a table (using dbAppendOnly I'll point out, which will make it problematic to delete a record), but how have you put the recordset onto the record selected in the listbox? You haven't, and you have to before the delete. I'm suggesting this type of thing:

    CurrentDb.Execute "DELETE * FROM tblLinkUserVisits WHERE userID = " & ctl.ItemData(varItem)
    Paul

  9. #9
    Join Date
    Aug 2004
    Posts
    364
    I know you suggested SQL, I was just showing you what I had tried. I only just got my head into multiselect listboxes, so Im not too sure what Im doing!

  10. #10
    Join Date
    May 2010
    Posts
    601
    For this, I find using a sub form is a lot easy and less coding the a Multi-Select Listbox.

    Just my 2 cents ...
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  11. #11
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by HiTechCoach View Post
    For this, I find using a sub form is a lot easy and less coding the a Multi-Select Listbox.

    Just my 2 cents ...
    Thats a valid point, I guess a datagrid would do? But I just would like to know how to do it with a listbox for my own satisfaction, I've been googleing it and getting no where

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by moss2076 View Post
    I just would like to know how to do it with a listbox for my own satisfaction
    Have you tried the code I posted?
    Paul

  13. #13
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by moss2076 View Post
    Thats a valid point, I guess a datagrid would do? But I just would like to know how to do it with a listbox for my own satisfaction, I've been googleing it and getting no where
    I was suggesting a sub form control, not a datagrid control.


    Have you tried something like this:

    Code:
    
    Dim strSQL As String
    Dim ctl As Control
    Dim varItem As Variant
    
    'make sure a selection has been made
    If Forms![visits_frm]!List71.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 user!"
    Exit Sub
    End If
    
    Set ctl = Forms![visits_frm]!List71
    For Each varItem In ctl.ItemsSelected
    
    CurrentDb.Execute "DELETE * FROM tblLinkUserVisits WHERE userID = " & ctl.ItemData(varItem) & " and visitID  = " &  Forms![visits_frm]![visitID] 
    
    Next varItem
    *** Air code *** Untested ***
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  14. #14
    Join Date
    Aug 2004
    Posts
    364
    Im getting a runtime error 3061 "too few parameters - expected 1".

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What are the data types of the 2 fields? Are all the table, field, form and control names correct? Is the form open?
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •