Removing an item from a listbox

Hello to every Access brain around. I've been trying to do something really simple but I have not been able to find the solution. I have two listbox on a form about trainings the first gives employee's name to choose and the second displays them (tblNamesChosen) with a code On Form Current linking both table (form and 2nd listbox) by the trainingID. The problem is : I would like to be able to delete one name from the txtNamesChosen listbox with a button like you see all the time "< "....so here's the code I've been trying:

Private Sub Command48_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim VarItem As Variant
Dim strSQL As String
Dim intID As Integer
intID = 0 & TrainingID
Set db = CurrentDb()
strSQL = "SELECT tblNamesChosen.* FROM tblNamesChosen;"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

For Each VarItem In Me!txtNamesChosen.ItemsSelected()
rs.MoveFirst
If rs!Names.Value = Me!txtNamesChosen.Column(0, VarItem) Then
With rs
rs.Edit
rs.Delete
rs.MoveNext
End With
End If
Next VarItem

Set db = Nothing
Set rs = Nothing
txtNamesChosen.Requery
Me.Refresh
End Sub

It does delete a name, but whatever I try it's always the first one or the last one never the one selected (if not in that position). Any help would be much much appreciated...I look all over the web to seek a solution. This is my last hope. Thanks!!!

I came across this article on msdn and thought this might help you in some way (maybe not at all)

http://msdn.microsoft.com/archive/d...osoftAccess.asp

and find the article Creating Combo Boxes and List Boxes in Microsoft Access on the left site.

Maybe you can work out from that example how to delete from the box.

Sorry I can't help more than this

I came across this article when searching for something else. This seems to be what you are looking for. Sorry for wasting your time if this is irrelevant.

http://www.tek-tips.com/faqs.cfm?fid=4246

Hello BBjo, I really appreciate all the trouble you're going throught for me. The problem is that these list box are alone in a form and they just moving the items from one listbox to another, my listbox is link to a table so I have to go in the table, make sure that the item is the one link to the right trainingID of the form and then delete it. But be sure that I looked to everyting you send me and try to ignite a spark in my brain to solve this. Right now I'm still at . Thanks!!

I'm almost there :

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim VarItem As Variant
Dim strSQL As String
Dim strNames As String
Dim intID As Integer
Set db = CurrentDb()
intID = TrainingID
Me.txtNamesChosen.SetFocus
strNames = Me.txtNamesChosen.ItemData(Me.txtNamesChosen.ListI ndex)
strSQL = "SELECT tblNamesChosen.* FROM tblNamesChosen WHERE tblNamesChosen.Names = " & strNames & " AND tblNamesChosen.TrainingID = " & intID & ";"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
' Delete record
Do
rs.Delete
rs.MoveNext
Loop Until rs.EOF

Set db = Nothing
Set rs = Nothing
Me.Refresh

but I get a Syntax error (missing operator) in query expression and the line in red is highlighted...Any ideas
If I only put the WHERE tblNamesChosen.TrainingID = " & intID & ";" it's working great...it deletes all the record with the same training ID, but I really want only the name selected to be deleted. Thanks!!

The ' has to surround text variable'

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim VarItem As Variant
Dim strSQL As String
Dim strNames As String
Dim intID As Integer
Set db = CurrentDb()
intID = TrainingID
Me.txtNamesChosen.SetFocus
strNames = Me.txtNamesChosen.ItemData(Me.txtNamesChosen.ListI ndex)
strSQL = "SELECT * FROM tblNamesChosen WHERE tblNamesChosen.Names= '" & strNames & "' AND tblNamesChosen.TrainingID = " & intID & ";"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
' Delete record
Do
rs.Delete
rs.MoveNext
Loop Until rs.EOF

Set db = Nothing
Set rs = Nothing
Me.Refresh

ALLELUIA!!!!