VBA user account program

  
In this example, you will learn VBA If Else Statement and Database.Execute Method to build a user accounts program. VBA If Else Statement is used to check values in the text boxes  before allowing to create a user account. The values will be checked when you click Create User command button. The Database.Execute Method is used to Save, Update and Delete records in the table by executing an SQL statements. The example also demonstrates Docmd.Maximize method to maximize the form when it first loads. The figure above is UserAccount form that will be created in the example.

- When the form loaded,  it is maximized and all controls are arranged in the middle of the form; all data in text boxes are blank; Update and Delete command buttons are disable, and data are populated in the list box.
- If you click Save without entering the UserName, Password, and Confirm, the messages will appear sequentially as the figure:

 - If you enter number in to the UserName textbox and the Confirm is not the same as Password  and click Create User command button,  the messages will appear sequentially as the figure:

- User Name cannot duplicate. If you enter the existing User Name, the message will appear:

- If you want to to update or delete the records in the table, double click any record in the list box; the record will display in the text boxes and combo box as the figure below:

- After you update or delete the records if you want to add new record/records, click Clear command button.
- The form has three text boxes(User Name, Password, Confirm), one list box(LstData), and five command buttons(Save, Update, Delete, Clear, Close).
-  Drag and drop three text boxes, one list box and five command buttons on the form.
-  Set Name properties of Text Boxes, and List Box:
Name: txtUserName
Name: txtPwd
Name: txtConfirm
Name: LstData
-  Set Name and Caption properties of Command Buttons:
Name: CmdSave
Caption: Save
Name: CmdUpdate
Caption: Update
Name: CmdDelete
Caption: Delete
Name: CmdClear
Caption: Clear
Name: CmdClose
Caption: Close


After you designed the form already, apply the VBA code below:
Option Compare Database
Dim Db As Database


Private Sub Form_Load()

Dim l, t As Integer
' Display form in full screen mode
DoCmd.Maximize
' Display asterisk for the password and confirm
txtPwd.InputMask = "Password"
txtConfirm.InputMask = "Password"

lblTitle.FontSize = 27
Set Db = CurrentDb
' Display three columns in list box
lstViewData.ColumnCount = 3
' Display list box header
lstViewData.ColumnHeads = True
lstViewData.RowSourceType = "Table/Query"
' Populate all data in the list box
lstViewData.RowSource = "select * from TbluserAccount"

DisableCommandButton

ClearData
' Move the controls to the center of form
Dim X As Control
Dim i As Integer
For i = 0 To Controls.Count - 1
Set X = Controls.Item(i)
l = X.Left
t = X.Top
X.Move l + 3500, t + 200
Next


End Sub
'......................................................................
Private Sub CmdCreateUser_Click()

If txtUserName = "" Then
MsgBox "User Name cannot be blank.", vbInformation
txtUserName.SetFocus
Exit Sub

ElseIf IsNumeric(txtUserName) = True Then
MsgBox "User Name must be text.", vbInformation
txtUserName.SetFocus
txtUserName.SelLength = Len(txtUserName)
Exit Sub

ElseIf txtPwd = "" Then
MsgBox " Password cannot be blank.", vbInformation
txtPwd.SetFocus
Exit Sub

ElseIf txtConfirm = "" Then
MsgBox " Comfirm cannot be blank.", vbInformation
txtConfirm.SetFocus
Exit Sub

ElseIf txtPwd <> txtConfirm Then
MsgBox "Password and confirm password must be the same.", vbInformation
txtConfirm.SetFocus
txtConfirm.SelLength = Len(txtConfirm)
Exit Sub
ElseIf ExistingField(txtUserName) = True Then
MsgBox "UserName cannot duplicate.", vbInformation
txtUserName.SetFocus
txtUserName.SelLength = Len(txtUserName)
Exit Sub

End If
Db.Execute "Insert into TblUserAccount values('" & txtUserName & "','" & txtPwd & "','" & txtConfirm & "')"
lstViewData.Requery


End Sub
'.................................................................................
Private Sub CmdUpdate_Click()
Db.Execute "Update TblUserAccount SET Password='" & txtPwd & "', Confirm='" & txtConfirm & "' WHERE UserName='" & txtUserName & "'"
lstViewData.Requery
End Sub

Private Sub CmdDelete_Click()
Db.Execute "Delete from TblUserAccount where Confirm='" & lstViewData.Column(2, lstViewData.ListIndex + 1) & "'"
lstViewData.Requery
End Sub
'...............................................................................
Private Sub cmdClear_Click()
ClearData
txtUserName.Enabled = True
txtUserName.SetFocus
DisableCommandButton
End Sub

Private Sub CmdClose_Click()
DoCmd.Close acForm, "frmUserAccount"
End Sub

Sub ClearData()
txtUserName = ""
txtPwd = ""
txtConfirm = ""
CmdCreateUser.Enabled = True
End Sub
'...................................................................................
Function ExistingField(UserName As String) As Boolean
' Check duplicate user name
Dim Rst As Variant
Rst = DLookup("UserName", "TblUserAccount", "UserName='" & txtUserName & "'")
If Rst <> "" Then
ExistingField = True
End If
End Function
'................................................................................
Private Sub lstViewData_DblClick(Cancel As Integer)

txtUserName = lstViewData.Column(0, lstViewData.ListIndex + 1)
txtPwd = lstViewData.Column(1, lstViewData.ListIndex + 1)
txtConfirm = lstViewData.Column(2, lstViewData.ListIndex + 1)

CmdUpdate.Enabled = True
CmdDelete.Enabled = True
CmdCreateUser.Enabled = False
txtUserName.Enabled = False

End Sub
'..............................................................................
Sub DisableCommandButton()
CmdUpdate.Enabled = False
CmdDelete.Enabled = False
End Sub