Delete query using listbox as criteria

[glgcag]

Hi everyone!

I have a listbox with a rowsource of tVendors. The list of vendors needs to be trimmed sometimes so I need to delete some of the records. I have a delete command button on the form that runs the following code. My question is how I insert the item selected into the code- (!VendorID=?). And I assume that if I have multiple items selected that I could nest this code in a loop?

Private Sub cmdDelete_Click()
On Error GoTo ErrDelete
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tVendors", dbOpenDynaset)
With rst
.Delete
!VendorID = lstVendors.ItemData???
.Update
.Close
End With

ExitDelete:
Exit Sub
ErrDelete:
MsgBox Err.Description
Resume ExitDelete

End Sub

Thanks for the help! These forums are GREAT!

GLGCAG

 

[wonderhands]

You need to loop through the ItemsSelected property which is a hidden collection of all the selected items.

It is collection of variants. so therefore should be accessed like:

dim varitem

for each varitem in me.listbox.itemsSelected

'***Here you should either run a delete query e.g:
'***dbengine(0)(0).execute
"Delete from table where field=" & me.listbox.itemdata(varitem)
Next varitem


'****Or alternatively in this siutation u can pinpoint
'****the records to delete a n the recordset

For each varitem in me.listbox.itemsSelected

rst.findfirst("fieldname=" & me.listbox.itemdata _(varitem))

if not rst.nomatch then
rst.delete
end if

next varitem

'****Also what you can do is Build up a list of values to delete by looping thorugh the itemsselected collection and buliding up a string. then use a bit of string manipulation to end up with an IN clause.

e.G

StrINClause = "3,4,5,6,7,8,9"


Then append then the IN information :

strINClause = "IN(" & strINClause & &quot"

then use it in a delete query or the select of a recordset

"Delete from Tablename where fieldname " & strINClause

Hope this helps

 

[glgcag]

Thank you wonderhands! It worked beautifully! I prefer the delete query but have had difficulty passing criteria from text boxes, combo boxes or list boxes on a form into the query. Particularly, because I am usually doing a multiple parameter query and I have to write the SQL in code to run it on an event in order to include a FOR . . .EACH statement.

Can you recommend a good resource that has good SQL code examples for doing complex queries in SQL in ACCESS? There is such a HUGE difference in the quality of my reporting and searching the more I am able to understand how to get the information properly out of ACCESS.

Again, thanks!

GLGCAG