Solved Simplest way to delete a selected item from a listbox

[Vulpeccula]

Hi people,
I have a problem deleting an item from a listbox. I have a project which is a fairly simple Point-of Sale system (screen shot below).

In this system, the server selects from the menu on the left (lstProducts) and that product is added to the list on the right (lstItemsSelected), all pretty straightforward thus far, yes?

Okay, so I need to delete the last item entered but when I hit the "Delete Item" button it deletes all three 'Addleston's Cloudy' items. Aha! you say. Thats because the delete query references the Product_ID (which it does). So here's my question to you guys. How do I do it?

Here is the existing (obvious) code:
Private Sub cmdDelItem_Click()

If IsNull(Me.lstSelectedItems) Then
MsgBox "Please select a record from the list to delete", vbCritical
Else

DoCmd.SetWarnings False
If MsgBox("Are you sure you want to remove '" & Me.lstSelectedItems.Column(2) & "' from the list?", vbYesNo + vbDefaultButton2 + vbCritical, "Warning") = vbYes Then
DoCmd.RunSQL "DELETE * FROM tmpSelectedItems WHERE Product_ID = " & Me.lstSelectedItems.Column(1) & ";"
Me.lstSelectedItems.Requery
End If

DoCmd.SetWarnings True
End If

End Sub

I'm sure I have been here before but a series of strokes have wreaked havoc with my memory (so please excuse any inane drivel). Any help would be very gratefully recieved.

My thanks in advance,

GF

 

[arnelgp]

does tmpSelecteItems table has Autonumber field (you should add one), then use it:

DoCmd.RunSQL "DELETE * FROM tmpSelectedItems WHERE [AutoNumberField] = DMax("[AutonumberField]", "tmpSelectedItems",
"Product_ID = " & Me.lstSelectedItems.Column(1))

 

[arnelgp]

also, consider using the tmSelectedItems table (datasheet or continuous form) in replacement for your listbox.
you then need No code to delete the item.

 

[Vulpeccula]

does tmpSelecteItems table has Autonumber field (you should add one), then use it:

DoCmd.RunSQL "DELETE * FROM tmpSelectedItems WHERE [AutoNumberField] = DMax("[AutonumberField]", "tmpSelectedItems",
"Product_ID = " & Me.lstSelectedItems.Column(1))

Wow!, That was quick man and I thank you.

The answer is no it doesn't. It is a temporary table that only exists until you hit a PAY button when the data from that particular server is appended to the "Reciepts" table.

 

[arnelgp]

you can also use Recordset:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = Currentdb
    Set rs = db.OpenRecordset("tmpSelectedItems")
    With rs
        .MoveLast
        .Delete
        .Close
    End With
    set rs = Nothing
    set db = Nothing

 

[Vulpeccula]

also, consider using the tmSelectedItems table (datasheet or continuous form) in replacement for your listbox.
you then need No code to delete the item.

Please elucidate

 

[Vulpeccula]

you can also use Recordset:

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = Currentdb
    Set rs = db.OpenRecordset("tmpSelectedItems")
    With rs
        .MoveLast
        .Delete
        .Close
    End With
    set rs = Nothing
    set db = Nothing

Yes, I see where you're going with this but I should make a correction to my inital statement. I dsid say "delete the last entry", what I should have said is any selected entry - my apologies for that.

 

[Vulpeccula]

Wow!, That was quick man and I thank you.

The answer is no it doesn't. It is a temporary table that only exists until you hit a PAY button when the data from that particular server is appended to the "Reciepts" table.

I think you have nailed it here my friend. It makes absolute sense to me - I will give it a bash and revert with the results. Thank you so much.

 

[arnelgp]

create a Sub on your form:

Public Sub delItemFromTempTable(Byval Idx As Integer)
    If Idx > -1 Then
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Set db = Currentdb
        Set rs = db.OpenRecordset("tmpSelectedItems")
        With rs
            .Move Idx
            .Delete
            .Close
        End With
        set rs = Nothing
        set db = Nothing
    End If
End Sub

you add a Button on your POS (delete item button) that will delete that item calling the sub above.
but first you need to select the item from the listbox before hitting the button (say cmdDeleteItemBtn).
add code to the Click event:

private sub cmdDeleteItemBtn_Click()
' if nothing is Highlighted on the listbox, ListIndex = -1.
If Me.lstSelectedItems.ListIndex > -1 Then
    If Msgbox("Are you sure to delete " & Me.lstSelectedItems & "?", vbQuestion+vbYesNo)  = vbYes Then
         Call delItemFromTempTable(Me.lstSelectedItems.ListIndex)
   End If
End If
end sub

after you delete from table, you Refresh your list.

 

[Vulpeccula]

create a Sub on your form:

Public Sub delItemFromTempTable(Byval Idx As Integer)
    If Idx > -1 Then
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Set db = Currentdb
        Set rs = db.OpenRecordset("tmpSelectedItems")
        With rs
            .Move Idx
            .Delete
            .Close
        End With
        set rs = Nothing
        set db = Nothing
    End If
End Sub

you add a Button on your POS (delete item button) that will delete that item calling the sub above.
but first you need to select the item from the listbox before hitting the button (say cmdDeleteItemBtn).
add code to the Click event:

private sub cmdDeleteItemBtn_Click()
' if nothing is Highlighted on the listbox, ListIndex = -1.
If Me.lstSelectedItems.ListIndex > -1 Then
    If Msgbox("Are you sure to delete " & Me.lstSelectedItems & "?", vbQuestion+vbYesNo)  = vbYes Then
         Call delItemFromTempTable(Me.lstSelectedItems.ListIndex)
   End If
End If
end sub

after you delete from table, you Refresh your list.

This worked a treat, thank you arnelgp - first class mate!

 

[Vulpeccula]

This worked a treat, thank you arnelgp - first class mate!

Is there a mechanism for registering this issue as solved - I'm not familiar with your protocols?

 

[Mike Krailo]

Is there a mechanism for registering this issue as solved - I'm not familiar with your protocols?

There should be a button at the top of post#1 called Solved. Press that button.

 

[Vulpeccula]

There should be a button at the top of post#1 called Solved. Press that button.

Thank you sir - very impressed with you guys

 

[Pat Hartman]

You seem to have an answer but the whole process would have been easier with two subforms rather than listboxes. Then you could just use queries to move the data around rather than code.

 

[Vulpeccula]

You seem to have an answer but the whole process would have been easier with two subforms rather than listboxes. Then you could just use queries to move the data around rather than code.

You have my attention Pat, please tell me more...

 

[Pat Hartman]

You have an answer so if you want to start a new question rather than confuse this one I will help. You can make subforms look like listboxes but you have all the functionality of a form.

 

[Vulpeccula]

You have an answer so if you want to start a new question rather than confuse this one I will help. You can make subforms look like listboxes but you have all the functionality of a form.

I don't understand, why am I confusing things when I am simply anwering your own post?

 

[Pat Hartman]

This thread was all about your question regarding working with listboxes. I just remarked that there are easier ways to do this. I wasn't saying that you had to change your method, only that you might want to consider using subforms should you need to do this again.

Do you have a specific question?