Trying to Delete a Record Based on Selected Item in a ListBox

[RunTime91]

Greetings...



I am trying to create code that will allow a user to delete a record based on selecting the record in a (Single Select) ListBox

Below is just one variation of what I am trying to get to work.

Dim Dbs As DAO.Database
Set Dbs = CurrentDb

CurrentDb.Execute "DELETE FROM TblTmpTktEmail WHERE TblTmpTktEmail.EmpID = '" & Me.ListEmailAgnts.ItemsSelected & "'"

I have tried setting a variable using ItemData and also using .'Selected' & .'SelectedItems' et al

The error I'm getting is a Compile/Arg Not Optional

Thanks in advance for any help...

[RunTime91]

Update...

Not sure if this matters, but the query is in the ListBox's DoubleClick Event.

Why I want to use the .ItemsSelected method is because one of the reasons a user will use this is when they have accidently added two of the same record

In this case they would need to delete only the duplicate record - I did have code that worked but it deleted both the original and the duplicate

Thanks again...

[davegri]

If you make tblTmpTktEmail.EmpID 'Indexed, duplicates OK = NO', user won't be able to create a duplicate in the first place.

[moke123]

is EmpID a string? or a number?
you dont need the .itemselected.
is EmpID the firstr or 0 column in the listbox?
Is EmpID unique?

For listboxes I usually put a small "Delete" label under the listbox. The user can then select a record click the label and then I use a y/n msgbox to confirm the delete

[RunTime91]

Not sure how to tag these threads as 'Solved' but this one is...

Moke - Great suggestion!

I went with the below Code in conjunction with Dave's instruction to Index the table with Dups = No - and it works lightening fast

Private Sub ListEmailAgnts_DblClick(Cancel As Integer)

'***CLEAR INCORRECT ENTRY FROM TABLE & LISTBOX***

If ListEmailAgnts = "" Then
  Exit Sub
End If

Dim Dbs As DAO.Database
Set Dbs = CurrentDb

CurrentDb.Execute "DELETE FROM TblTmpTktEmail WHERE TblTmpTktEmail.EmpID = '" & Me.ListEmailAgnts.Column(1) & "'"

Me.ListEmailAgnts.RowSource = "SELECT TblTmpTktEmail.EmpName, TblTmpTktEmail.EmpID " & _
                              "FROM TblTmpTktEmail"
End Sub

Thanks Dave & Moke!

[kd2017]

Also, you declared Dbs but didn't use it in the query. And don't forget to release it when you're done.

Dim Dbs As DAO.Database
Set Dbs = CurrentDb

Dbs.Execute "DELETE FROM TblTmpTktEmail WHERE TblTmpTktEmail.EmpID = '" & Me.ListEmailAgnts & "'"
Set Dbs = Nothing

What is the data type of tblTmpTktEmail.EmpID? That is usually a number data type. If it's a number data type then you do not need the single quotes in your sql string

Usually in this situation your listbox would have multiple columns, the first column being the ID field, the other columns for display purposes. When your listbox has multiple columns, and one selection, then Me.Listbox will return the value in the first column of the selected row. If you want to return a value in a different column then you'd use Me.Listbox.Column( ColumnIndex ) where ColumnIndex starts at 0.

Also note that if you're calling this code from outside the OnClick event of the control it would be a good idea to test if the listbox has anything selected at all before executing the sql: If Not IsNull(Me.Listbox) Then 'proceed

[kd2017]

You can probably replace this

Me.ListEmailAgnts.RowSource = "SELECT TblTmpTktEmail.EmpName, TblTmpTktEmail.EmpID " & _
                              "FROM TblTmpTktEmail"

With this:

Me.ListEmailAgnts.Requery

Assuming you the sql or your rowsource never actually changes.

[RunTime91]

kd - Awesome!

In this case the EmpID's do have letters so they are formatted as text... also they appear in column 1 (the 2nd column) of the ListBox

And yeah, I suppose it would be nice if I used my declared variable for Dbs - Geez... jsmh

Requery - Works great!

Thanks kd!

[kd2017]

You're welcome!

[moke123]

Before you go...

with the .execute method I would add a dbfailonerror.

I'm also not sure that a listbox would return an empty string, its probably a null but you can test for
both using nz()

Private Sub ListEmailAgnts_DblClick(Cancel As Integer)

Dim strSql as String

If nz(Me.ListEmailAgnts.Column(1),"") <> "" Then

strSql = "DELETE * FROM TblTmpTktEmail WHERE TblTmpTktEmail.EmpID = '" & Me.ListEmailAgnts.Column(1) & "'"


CurrentDb.Execute strSql,dbFailOnError

Me.ListEmailAgnts.Requery

End If
                          
End Sub

[RunTime91]

Moke ~ Thank you!

Your suggestions - Done!...

Thank you, again!