Trying to delete record from listbox and it doesn't work...

[SimonSezz]

I have a form that shows all user accounts available in the program. The users are listed in a listbox control named "lstUsers", which has it's record source value set to gather records from tblUsers

There is a button on this form named "cmdDeleteUser" and when it is clicked, it should delete the user that is currently selected in "lstUsers". It doesn't work though, the record still remains in the table.

Here is the code for the click button action:

Private Sub cmdDeleteUser_Click()
    Dim MsgAnswer As Integer
    If IsNull(lstUsers.Value) Then
        MsgBox "No user selected.", vbCritical, "Error"
    Else
        MsgAnswer = MsgBox("Are you sure you want to delete this user?", vbOKCancel, "Delete User")
        If MsgAnswer = 1 Then
            DeleteUser
        End If
    End If
End Sub

Here is the code for the function "DeleteUser()":

Function DeleteUser()
    Dim dbsATEVRS As DAO.Database
    Dim rstUsers As DAO.Recordset
    Dim strSQL As String
    Dim strName As String
 
    Set dbsATEVRS = CurrentDb
    strSQL = "SELECT * FROM tblUsers"
    Set rstUsers = dbsATEVRS.OpenRecordset(strSQL, dbOpenDynaset)
 
    Do Until rstUsers.EOF
        If rstUsers![UserID] = lstUsers.Value Then
            rstUsers.Delete
        End If
        rstUsers.MoveNext
    Loop
 
    MsgBox "User Deleted", vbCritical
End Function

 

[boblarson]

You don't need all of that code to do that. Just use a delete query.

Private Sub cmdDeleteUser_Click()
Dim strSQL As String

    If IsNull(lstUsers) Then
        MsgBox "No user selected.", vbCritical, "Error"
    Else
        If  MsgBox("Are you sure you want to delete this user?", vbOKCancel, "Delete User")= vbOK Then
              strSQL = "DELETE * FROM  tblUsers WHERE UserID =" & Me.lstUsers
              CurrentDb.Execute strSQL, dbFailOnError
              Me.lstUsers.Requery
        End If
    End If

End Sub

 

[SimonSezz]

Thank you for the help. Your code performs perfectly. I have just been referring to the Access help so I try to gather as much information from there as I can. I didn't know you can get the listbox's current value without using the value property. I have a lot more to learn.