delete item from table through listbox

[Brian]

Hello:
I have a DB with 2 tables and a form. The form has a list box
representing each table, so my form has lst1 (for Table1) and lst2
(for Table2). When I double click a record in lst1, it appends that
record into Table2. Lst2 is immediatly updated to show the new entry.
My problem is, I want to be able to delete people from Table2 just
like I added them, with a double click. Curently, my only way is to
wipe the whole table and rebuild my list. It's only a temporary list,
so that's no big deal, but I'd rather just delete the one record and
not the entire table. I've tried a delete query, but I can't get it
to recoginize what item I am selecting or double clicking. I've seen
a little info on the .itemsSelected but I can't make sense of it. Any
help would be appreciated.
Thanks.
Brian

 

[Sandra Daigle]

Hi Brian,

Here is some code that I use - basically it loops through the ItemsSelected
collection of the list building a string of the ids (which are in the bound
column of the listbox). The string is used as the Where criteria for the SQL
Delete query. Following the execution of the query, the listbox is
requeried.

FWIW, this example comes from the SelectRecords sample database I put
together on http://www.daiglenet.com/msaccess.htm.

In the following, varItem is a variant which gives you the integer index to
each of the selected rows. ItemData is the property of the listbox that
returns the value in the bound column of the row named by the varItem.


Dim strSQL As String
Dim strwhere As String
Dim db As DAO.Database
Dim varItem As Variant
For Each varItem In Me.lstSelected.ItemsSelected
strwhere = strwhere & "Classid=" & Me.lstSelected.ItemData(varItem) & "
OR "
Next varItem
' Remove the trailing " OR "
strwhere = Left(strwhere, Len(strwhere) - 4)
strSQL = "Delete * from tblPersonClasses where PersonNbr=" & Me.PersonNbr &
" AND (" & strwhere & ");"
Set db = CurrentDb
db.Execute strSQL
Me.lstAvailable.Requery
Me.lstSelected.Requery

Set db = Nothing