delete a listbox entry 1

[Duane8889]

Hello
I want to delete a selection from a listbox. My wizard created this delete routine for me but I think it pertains to a textbox choice for deletion.

Private Sub cmdDeleteEmployee_Click()
On Error GoTo Err_cmdDeleteEmployee_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_cmdDeleteEmployee_Click:
    Exit Sub

Err_cmdDeleteEmployee_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteEmployee_Click
    
End Sub

How can I modify it to delete a choice from a listbox box if I highlight the choice in the listbox to delete?

thanks for any help!
Duane

 

[Remou]

Hi
Where is the data in the listbox coming from? Is it based on a table or query or a value list ... ? Just to make sure, do you wish to remove the item from the list or remove an item from a table based on the listbox selection?

 

[Duane8889]

thanks Remou
The listbox is populated from a table. Yes, I want to remove an item from a table based on the listbox selection.

This is the RowSource...
SELECT [tblEmployees].[employeesID], [tblEmployees].[employees] FROM tblEmployees ORDER BY [tblEmployees].[employees];

The ControlSource is Employees, a field from a table.

Duane

 

[Remou]

Hi
There are a few ways to do this and you may well find a better way in the FAQs ...

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordSet("Select * from [tblEmployees]")
rs.FindFirst "[EmployeesID] =" & Me!List0
rs.Delete
Me.List0.Requery

Or

SQL = "Delete * From [tblEmployees] Where [EmployeesID] =" & Me!List0
DoCmd.RunSQL SQL
Me.List0.Requery

Both of these assume that EmployeesID is numeric.

 

[Duane8889]

Remou
Your code worked but now when I add someone after I delete someone from the listbox I get some strange things such as a 10 digit negative number for my records. This is the number you see at the bottom of the form with the navigation buttons. The code for the delete is...

Private Sub cmdDeleteEmployee_Click()
On Error GoTo Err_cmdDeleteEmployee_Click
    SQL = "Delete * From [tblEmployees] Where [EmployeesID] =" & Me!lstEmployees
    DoCmd.RunSQL SQL
    Me.lstEmployees.Requery
    txtCount = lstEmployees.ListCount & " employees"

Exit_cmdDeleteEmployee_Click:
    Exit Sub

Err_cmdDeleteEmployee_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteEmployee_Click
    
End Sub

for the add routine...

Private Sub cmdAddEmployee_Click()
On Error GoTo Err_cmdAddEmployee_Click

    txtAddEmployee.SetFocus
    DoCmd.GoToRecord , , acNewRec
    
Exit_cmdAddEmployee_Click:
    Exit Sub

Err_cmdAddEmployee_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEmployee_Click
    
End Sub

There is also several blank entries in the listbox. They have record numbers associated with them but no employee names. So I use the delete routine to delete them.

Also when adding after deleting it adds two entries in the listbox for every one I make, the name I added and another with a number.

Duane

 

[Remou]

Hi Duane
It seems that your list box shows employees on a form for the same employees, which changes things a little. To go back to your original post:

'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.RunCommand acCmdSelectRecord
'DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
DoCmd.RunCommand acCmdDelete
Me.lstEmployees.Requery

Once you have deleted an employee from the form, Me.lstEmployees.Requery will remove the name from the list box. The wizard uses code that is a little old fashioned, so I have included the code that Microsoft recommended in Access 2000. I do not think it has changed since, but you can check.

You say

I get some strange things such as a 10 digit negative number for my records. This is the number you see at the bottom of the form with the navigation buttons.

I have tried to reproduce this and cannot. I thought at first you were referring to an Autonumber, but if it is at the bottom with the navigation buttons, that cannot be it.

There is also several blank entries in the list box. They have record numbers associated with them but no employee names. So I use the delete routine to delete them.

Could this be a problem when adding employees?

txtAddEmployee.SetFocus
DoCmd.GoToRecord , , acNewRec

I notice you set focus on txtAddEmployees, if this is not the same as, or related to, tblEmployees.Employee, perhaps the name is not being filled in? Once again, I set up a form and table based on the information you have supplied and I cannot reproduce this problem.

Also when adding after deleting it adds two entries in the list box for every one I make, the name I added and another with a number.

This does seem a little odd. Is there any other code that could be causing this? I notice that you are not requerying the list box after an addition. You can put code in so:

Private Sub Form_AfterInsert()
Me.lstEmployees.Requery
txtCount = lstEmployees.ListCount & " employees"
End Sub

This might help show up this problem

 

[Duane8889]

thanks Remou for trying
these problems are intermittent. I get differing problems doing the same actions. It seems adding is not a problem until I try deleting then adding.

I can delete an employee and be asked twice the popup box "You are about to delete one row are you sure?"

Sometimes when I delete the name goes away but the ID appears in the listbox. Other times I delete an employee and the #Deleted appears in the column and the table.

Sometimes I delete an employee and look at the table and see the ID stays in the table but the employee field is blank. And when I return to the form the employee corresponding to the ID is in the table as a blank.

pertinent code...

Private Sub cmdDeleteEmployee_Click()
On Error GoTo Err_cmdDeleteEmployee_Click
    SQL = "Delete * From [tblEmployees] Where [EmployeesID] =" & Me!lstEmployees
    DoCmd.RunSQL SQL
    Me.lstEmployees.Requery
    txtCount = lstEmployees.ListCount & " employees"

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDelete

Exit_cmdDeleteEmployee_Click:
    Exit Sub

Err_cmdDeleteEmployee_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteEmployee_Click
    
End Sub

'   show number of entries in listbox
Private Sub Form_AfterInsert()
    Me.lstEmployees.Requery
    txtCount = lstEmployees.ListCount & " employees"
End Sub

'   add record button routine
Private Sub cmdAddEmployee_Click()
On Error GoTo Err_cmdAddEmployee_Click

    txtAddEmployee.SetFocus
    DoCmd.GoToRecord , , acNewRec
'    Dim ctlst As Control
'    Set ctlst = lstEmployees
'    ctlst.Requery
    
Exit_cmdAddEmployee_Click:
    Exit Sub

Err_cmdAddEmployee_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddEmployee_Click
    
End Sub

Private Sub lstEmployees_AfterUpdate()
    txtCount = lstEmployees.ListCount & " employees"
End Sub

Private Sub txtAddEmployee_AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord
    DoEvents
    lstEmployees.Requery      ' refresh lstEmployees
    txtCount = lstEmployees.ListCount & " employees"
End Sub

Private Sub Form_Load()
    Dim myString As String
    txtCount = lstEmployees.ListCount & " employees"
End Sub

 

[Remou]

Hi
I think there are a few bits that can be left out, for example:

SQL = "Delete * From [tblEmployees] Where [EmployeesID] =" & Me!lstEmployees
DoCmd.RunSQL SQL
Me.lstEmployees.Requery

The above deletes a record.

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDelete

This also deletes a record, which may be the same record that has already been deleted. This will cause problems. When I suggested the first bit of code above, I did not realise you had a listbox referencing the same recordset as the form. I think it would be best to change the whole sub to this:

Private Sub cmdDeleteEmployee_Click()
On Error GoTo Err_cmdDeleteEmployee_Click
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdDelete

    Me.lstEmployees.Requery
    'See my comment below regarding txtCount
    'txtCount = lstEmployees.ListCount & " employees"

Exit_cmdDeleteEmployee_Click:
    Exit Sub

Err_cmdDeleteEmployee_Click:
    MsgBox Err.Description
    Resume Exit_cmdDeleteEmployee_Click
    
End Sub

Also, it may be best to simply set the Control Source of txtCount to:
[tt]= lstEmployees.ListCount & " employees"[/tt]
If you do this, you could then leave out a lot of lines. You could try commenting them out at first and see if you are still getting the results you want. By the way, you cannot set the Control Source and use this:
[tt]txtCount = lstEmployees.ListCount & " employees"[/tt]
at the same time.
I think you will find that you can leave out:

Private Sub lstEmployees_AfterUpdate()
    txtCount = lstEmployees.ListCount & " employees"
End Sub

Private Sub txtAddEmployee_AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord
    DoEvents
    lstEmployees.Requery      ' refresh lstEmployees
    txtCount = lstEmployees.ListCount & " employees"
End Sub

Private Sub Form_Load()
    Dim myString As String
    txtCount = lstEmployees.ListCount & " employees"
End Sub