Access delete table record vba

Question

I have a table named 'Users' in the next format:

Username | Name | Surname | Email              | Role        | Company |
------------------------------------------------------------------------
jsmith   | John | Smith   | john.smith@ABC.com | Local admin | ABC     |
jdoe     | Jane | Doe     | jane.doe@DEF.com   | User        | DEF     |

I have a bound form named 'User Information' where I can cycle users in the next format:

---------------------------------       ---------------------------------
| Username | jsmith             |       | Username | jdoe               |
---------------------------------       ---------------------------------
| Name     | John               |       | Name     | Jane               |
---------------------------------       ---------------------------------
| Surname  | Smith              |       | Surname  | Doe                |
---------------------------------   >   ---------------------------------
| Email    | john.smith@ABC.com |       | Email    | jane.doe@DEF.com   |
---------------------------------       ---------------------------------
| Role     | Local admin        |       | Role     | User               |
---------------------------------       ---------------------------------
| Company  | ABC                |       | Company  | DEF                |
---------------------------------       ---------------------------------

Name of the fields are next:

---

Username_Label | Username

Name_Label | Name

Surname_Label | Surname

Email_Label | Email

Role_Label | Role

Company_Label | Company

---

I have a button for exiting the form, a button for saving data changes, and I wanted to add the button for deleting the user.

Could you give me some advice on how would I go about doing that?

This is the 'User Information' form vba code:

Option Compare Database
Private msaved As Boolean

Private Sub Delete_User_Click()

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If msaved = False Then
        Cancel = True
        Me.Undo
        Cancel = False
    End If

End Sub

Private Sub Exit_Click()

    If MsgBox("Are you sure you want to close?", vbInformation + vbYesNo) = vbYes Then
        DoCmd.Close acForm, "User Information"
    Else
        Cancel = True
    End If

End Sub

Private Sub Save_Data_Click()

    If Me.Dirty Then

        If MsgBox("Are you sure you want to save user data?", vbInformation + vbYesNo) = vbYes Then
            msaved = True
            DoCmd.RunCommand acCmdSaveRecord

            MsgBox "User data saved.", vbInformation + vbOKOnly
        Else
             Exit Sub
        End If

        msaved = False
        DoCmd.Close acForm, "User Information"
    Else
        MsgBox "You have made no changes.", vbInformation + vbOKOnly
    End If

End Sub

Private Sub Form_Current()

    msaved = False
    txtFucus.SetFocus

    For Each ctrl In Me.Controls
        If ctrl.Tag = "CHKLEN" Then 'check the tag of the textbox control for the indicator
            ctrl.Locked = False
        End If
    Next ctrl

End Sub

Answer 1

I managed to delete the user.

Maybe someone will benefit from this. Cheers. :)

Private Sub Delete_User_Click()

    If MsgBox("Are you sure you want to delete user?", vbInformation + vbYesNo) = vbYes Then
    strSQL = "DELETE * FROM [Users]" & "WHERE [Username] = '" & Me.Username & "'"
        CurrentDb.Execute strSQL, dbFailOnError
        MsgBox "User deleted.", vbInformation + vbOKOnly"
    End If

End Sub