How can i delete data from a listbox (vba)?

J

Jero

Hi,

I have a list called lstResult with 4 columns. The first column contain the
ID's.
I want to delete the selected row by using sql, but when i try to do that,
then I received this error:

Error '3464'
Data type mismatch in criteria expression.

//This is the code:

Private Sub btn_Delete_Click()
Dim Id
Id = CVar(Me.lstResult.Column(0, Me.lstResult.ListIndex))

If IsNull(Me.lstResult) Then
MsgBox "No selection."
End If

DoCmd.RunSQL ("Delete * FROM Employee WHERE User_ID = '" & Id & "' ")
Me.lstResult.Requery
End Sub
 
T

Tim Ferguson

Data type mismatch in criteria expression.


DoCmd.RunSQL ("Delete * FROM Employee WHERE User_ID = '" & Id & "' ")
Click to expand...

If User_ID is a numeric expression, you should get rid of the quote
marks:

"DELETE FROM Employee WHERE User_ID = " & CStr(Id)


By the way, the code flow is wrong. If there is no selection, you still
pass the Null to the DELETE command, which may not be what you want. Try
this:

If lstResult.ListIndex < 0 Then
MsgBox "etc"

Else
jetSQL = "DELETE etc" & lstResult.Column(0, lstResult.ListIndex)
db.Execute jetSQL

End If

Hope that helps


Tim F
 
Top