Run a delete query based on multiple items selected from a list box

[maupiti]

Access 2003

Execute a delete query based on multiple items selected from a list box.

The first procedure "User_Name_List_AfterUpdate" works correctly. Whenever a user presses the shift-key or the control-key, and then select multiple items, a list is buit up (ex: Public_Source_String = 'John', 'Jay', 'Jack').

The second procedure does not work. This procedure try
to delete 'John', 'Jay', 'Jack' from the table User_Account.

/////////////////////////////////////////////

Private Sub User_Name_List_AfterUpdate()
Dim varItem As Variant
Dim strSQL As String
Dim strSQL_No_Comma_At_End As String

'capture the slected user name
For Each varItem In Me!User_Name_List.ItemsSelected
strSQL = strSQL & "'" & Me!User_Name_List.ItemData
(varItem) & "', "
Next varItem

strSQL_No_Comma_At_End = Left$(strSQL, Len(strSQL) - 2)
Public_Source_String = strSQL_No_Comma_At_End

MsgBox Public_Source_String
End Sub

///////////////////////////////////////////

Private Sub Delete_User_Name_Click()
Dim Dbs As Database
Set Dbs = CurrentDb

Dbs.Execute "DELETE FROM User_Account WHERE Name = " & _
"Source IN (" & Public_Source_String & ")"

Dbs.Close
End Sub

 

[RoyVidar]

If you'd stuffed the sql string into a string, then performed a msgbox or debug.print of it, you'd seen:

DELETE FROM User_Account WHERE Name = Source IN ('John', 'Jay', 'Jack' ...

Now - it is probably either "[Name] In" or "Source In"? (remember [brackets] on name, since it's reserved word, perhaps also include it on [source] too?)

Roy-Vidar

 

[JoeAtWork]

As per Roy, I believe you want the SQL statement to be something like:

DELETE FROM User_Account WHERE [Name] IN ('John', 'Jay', 'Jack')