Delete selected item from list box

[HelpMoses76]

I am trying to write a piece of code that works on button click and deletes a selected row from the Table . Not sure how I can reference the entire row. ID is the primary key in the table . Any ideas ?

 

[theDBguy]

Hi. Is it a multi-select Listbox?

 

[HelpMoses76]

Hi. Is it a multi-select Listbox?

No ...

 

[Isaac]

Which listbox column contains the key value?

 

[HelpMoses76]

Which listbox column contains the key value?

The ID column

 

[Isaac]

Sorry, I meant the column number in the listbox properties.

 

[theDBguy]

No ...

Okay, for a non-multi select Listbox, you might be able to do something like this:

Dim strSQL As String
If MsgBox("Are you sure you want to delete this item?",vbQuestion+vbYesNo,"Confirm!")=vbYes Then
    strSQL = "DELETE FROM TableName WHERE ID=" & Me.ListboxName
    CurrentDb.Execute strSQL, dbFailOnError
End If

Hope that helps...

 

[HelpMoses76]

Sorry, I meant the column number in the listbox properties.

Did you mean the total number of columns in the lisbox ? If yes it is 22

 

[HelpMoses76]

Okay, for a non-multi select Listbox, you might be able to do something like this:

Dim strSQL As String
If MsgBox("Are you sure you want to delete this item?",vbQuestion+vbYesNo,"Confirm!")=vbYes Then
    strSQL = "DELETE FROM TableName WHERE ID=" & Me.ListboxName
    CurrentDb.Execute strSQL, dbFailOnError
End If

Hope that helps...

Thank you so much for taking the time to reply . However it gives me an error on the last statement

CurrentDb.Execute strSQL, dbFailOnError . I wonder if I have to do some kind of variable declaration.

 

[theDBguy]

Thank you so much for taking the time to reply . However it gives me an error on the last statement

CurrentDb.Execute strSQL, dbFailOnError . I wonder if I have to do some kind of variable declaration.

What was the error message?

 

[Gasman]

You did change the table and listbox names to match yours, didn't you

 

[HelpMoses76]

You did change the table and listbox names to match yours, didn't you

Yes I did .

 

[HelpMoses76]

Yes I did .

Thank you once again .

 

[theDBguy]

You did change the table and listbox names to match yours, didn't you

And the Field's Name too please (in case it's called other than just "ID").

 

[HelpMoses76]

What was the error message?

error 3131 - syntax error in from clause
Also attached a screen shot.
Thank you so much for helping

 

[HelpMoses76]

And the Field's Name too please (in case it's called other than just "ID").

it is ID.

 

[theDBguy]

error 3131 - syntax error in from clause
Also attached a screen shot.
Thank you so much for helpingView attachment 85228

Can you please add the following line before the CurrentDb.Execute one and post a screenshot of the message you get? Thanks.

MsgBox strSQL

 

[Gasman]

You need Delete * FROM

https://docs.microsoft.com/en-us/of...ference/delete-statement-microsoft-access-sql

 

[theDBguy]

You need Delete * FROM

https://docs.microsoft.com/en-us/of...ference/delete-statement-microsoft-access-sql

I might say "need" may be a strong word.

 

[Gasman]

I might say "need" is a strong word.

Well I was not sure it was mandatory as you had not used it, but I always have not knowing any better? , so I had to check with that link?

Edit: In fact that link says [table.*] is optional ??