delete table records with listbox

[ilse20]

Hello,

i'm kind of new to working with VBA Access and i am experiencing some problem.

i have a table where the user can save labels. then i have a listbox where the user can see which labels are saved with his username.

i also have the option for the user to deleted records that he doesn't want to print.

now i have a code to delete the records. It deletes the selected items from the table. but it doesn't quite work ok. When i have deleted the selected items. the items that are still in the listbox aren't correct. it delete a record which i haven't selected.

naamloos.jpg is a screenshot before i delete the selected items.

after.jpg is a screenshot after i have deleted the selected items.

as you can see it didn't deleted the items i selected. if you look at naamloos.jpg you see that i didn't selected one hpl-40 and one hpl-131.
so those 2 should still be in the listbox after i press delete selected.

but if you look at after.jpg you can see that there are two hpl-131.

this is the code i use.

   Dim Response As Integer
        Response = MsgBox(prompt:="Are you sure you want to delete the selected labels?", Buttons:=vbYesNo)
        
    For i = 0 To Me.list_labeloverview.ItemsSelected.Count - 1
        Dim LabelName As String
        Dim h_mySQL As String
        Dim ChosenName As String
        Dim h_Queryname As String
        Dim h_qdf As DAO.QueryDef
        Dim h_rs As DAO.Recordset
        
        LabelName = Me.list_labeloverview.ItemData(Me.list_labeloverview.ItemsSelected.item(i))
            
            Set h_rs = CurrentDb.OpenRecordset("Generate_Labels")
            With h_rs
                If Response = vbYes Then
                    .Delete
                Else
                    .Close
                End If
            End With
        Next
            UpdateGetLabels

can somebody please help me with this?

ilse

 

[DCrake]

i is the number of items that have been selected in the list. Your loop is just enumerating through the items until it reaches the count.

For x = 0 To .Listcount - 1
   If .Selected(x) Then
     Delete
   end if
Next

For brevity only

 

[ilse20]

if tried you code some ways but it doesn't seem to work.

 

[DCrake]

Function GetServiceCount() As Integer
Dim Ctl As Control
Dim vItem As Variant
Dim iCnt As Integer
Set Ctl = Me.LstServices
iCnt = 0
For Each vItem In Ctl.ItemsSelected
    iCnt = iCnt + 1
Next vItem
Set Ctl = Nothing
GetServiceCount = iCnt
End Function

the above code loops through a listbox and counts the number of selected items

Dim vItem As Variant
Dim Ctl As Control
Dim rs As DAO.Recordset
    Set Ctl = Me.LstServices
    For Each vItem In Ctl.ItemsSelected
        Me.TxtComments = Me.TxtComments & vbCrLf & Ctl.Column(0, vItem) & " Removed on " & Date
        Set rs = CurrentDb.OpenRecordset("Select * From tblmstStaffServices Where fldPinID='" & szPinID & "' And fldServiceId=" & Ctl.Column(3, vItem))
        If Not rs.EOF And Not rs.BOF Then
            rs.Delete
        End If
    Next vItem
    rs.Close
    Set Ctl = Nothing
    Set rs = Nothing
    Me.LstServices.Requery

The above code is similar but removes items from tables based on selected items.

 

[ilse20]

it still doesn't quite work. when i select multiple item i get the error: no current record.

and when i select one i get the error: object variable or with block variable not set

and then when i have pressed end it has deletes the top record

 

[DCrake]

Have you tried the second suggestion I gave you?

Dim vItem As Variant
Dim Ctl As Control
Dim rs As DAO.Recordset
    Set Ctl = Me.LstServices
    For Each vItem In Ctl.ItemsSelected

        Set rs = CurrentDb.OpenRecordset("Select * From tblmstStaffServices Where fldPinID='" & szPinID & "' And fldServiceId=" & Ctl.Column(3, vItem))
        If Not rs.EOF And Not rs.BOF Then
            rs.Delete
        End If
    Next vItem
    rs.Close
    Set Ctl = Nothing
    Set rs = Nothing
    Me.LstServices.Requery

Change the table,field & control names to match yours

Notice how the code is constructed do you understand what is happening at at what point.

 

[ilse20]

i have tried that. but it still doesn't work

 Dim Response As Integer
    Dim h_rs As DAO.Recordset
    Dim vItem As Variant
    Dim Ctl As Control
    Dim rs As DAO.Recordset
    
    Set Ctl = Me.list_labeloverview
    For Each vItem In Ctl.ItemsSelected

        Set h_rs = CurrentDb.OpenRecordset("SELECT pronum, finishing, colour, ref_num_buyer, date, logo FROM Generate_labels WHERE ChangeUserName = '" & ChosenName & "';")
        If Not h_rs.EOF And Not h_rs.BOF Then
            h_rs.Delete
        End If
    Next vItem
        h_rs.Close
        Set Ctl = Nothing
        Set h_rs = Nothing
        UpdateGetLabels
    Me.list_labeloverview.Requery

 

[ilse20]

with this code it doesn't do anything. the records i selected stay in the listbox he doesn't delete them

 

[DCrake]

But are the correct records being deleted from the table? Lets worry about that first. You initially stated that thats what was not happening. The only reason the list box is not refreshing correctly is because we have not told it to explicitly, sometimes .Requery does not always work as the underlying datasource need refreshing itself.

To force the refresh you need to reset the Rowsource again using SQL, such as

Me.Listbox.RowSource = "Select * From ...."

 

[ilse20]

the records aren't being deleted from the table. when i select multiple records and press delete the records stay in the listbox and in the table and one with a single records it doesn't do that

 

[DCrake]

Repost the sql you are using and indicate where you are calling it from.

 

[ilse20]

when i reply the sql with the table i want to delete it from is doesn't work. the i have the same problem as in the beginning

    Dim Response As Integer
    Dim h_rs As DAO.Recordset
    Dim vItem As Variant
    Dim Ctl As Control
    Dim rs As DAO.Recordset
    
    Set Ctl = Me.list_labeloverview
    For Each vItem In Ctl.ItemsSelected

        Set h_rs = CurrentDb.OpenRecordset("Generate_Labels")
        If Not h_rs.EOF And Not h_rs.BOF Then
            h_rs.Delete
        End If
    Next vItem
        h_rs.Close
        Set Ctl = Nothing
        Set h_rs = Nothing
        UpdateGetLabels
    Me.list_labeloverview.Requery

 

[DCrake]

Put a breakpoint on the code and use F8 to step through it to see what is (not) happening.

 

[ilse20]

when i do that it skips h_rs.delete.
For Each vItem In Ctl.ItemsSelected

Set h_rs = CurrentDb.OpenRecordset("SELECT pronum, finishing, colour, ref_num_buyer, date, logo FROM Generate_labels WHERE ChangeUserName = '" & ChosenName & "';")
If Not h_rs.EOF And Not h_rs.BOF Then
h_rs.Delete
End If
Next vItem
h_rs.Close
Set Ctl = Nothing
Set h_rs = Nothing
UpdateGetLabels
Me.list_labeloverview.Requery

this code i've breakpointed and the it goes from If Not h_rs.EOF And Not h_rs.BOF Then to end if

 

[DCrake]

        Set rs = CurrentDb.OpenRecordset("Select * From Generate_labels Where ChangeUserName =" & [B]Ctl.Column(0, vItem))[/[/B]CODE]

What you have not done is to apply a filter correctly to the recordset that matches the currently selected item in the list box.

 

[ilse20]

when i do ik like that i get this error:

error 3061 too few parameters expected 1

 

[ilse20]

put then when i put 1 there it doesn't work either

 

[ilse20]

i have been looking at my code a bit. i have changed some things and now i get a different error.

 Set Ctl = Me.list_labeloverview
    Response = MsgBox(prompt:="Are you sure you want to delete the selected labels?", Buttons:=vbYesNo)
    For Each vItem In Ctl.ItemsSelected

        Set h_rs = CurrentDb.OpenRecordset("SELECT pronum, Finishing, Colour, Ref_num_buyer, Date, Logo FROM Generate_labels WHERE ChangeUserName = '" & ChosenName & "' & Ctl.Column(8, vItem)")
        'If Not h_rs.EOF And Not h_rs.BOF Then
        If Response = vbYes Then
            h_rs.Delete
        Else
            h_rs.Close
        End If
    Next vItem
        h_rs.Close
        Set Ctl = Nothing
        Set h_rs = Nothing
        UpdateGetLabels
    Me.list_labeloverview.Requery

now i get the error:

erroe 3085 undefined function ctl.column in expression.

 

[ilse20]

i solved it.

i changed from a listbox to a continous form. there it works. i put the table in the record source and then textboxen on the form that retrieve the tablefields

now my code is

    Dim h_rs As DAO.Recordset
    Dim h_SQL As String
    
    Set h_rs = CurrentDb.OpenRecordset("Generate_Labels")
    h_SQL = "Delete * FROM Generate_labels WHERE tb_id = Generate_labels.label_ID"
    
    With DoCmd
        .SetWarnings False
        .RunSQL h_SQL
        .SetWarnings True
    End With
    Me.Requery