VBA example - Microsoft Access: user account program


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 below is UserAccount form that will be created in the example:

 VBA example - Microsoft Access VBA If Esle Statement and Database.Execute Method UserAccount Form  

- 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:

VBA example - Microsoft Access VBA If Esle Statement And Database.Execute Method Blank

 - 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:

VBA example - Microsoft Access VBA If Esle Statement And Database.Execute Method User Name and Confirm 

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

VBA example - Microsoft Access VBA If Esle Statement And Database.Execute Method Duplicate User Name 

- 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:

VBA example - Microsoft Access VBA If Esle Statement and Database.Execute Method UserAccount View Form 

- 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 



Comments

amar comment

 amar

bonjour


2017-02-05
hi comment

 hi

a


2017-01-24
Darren comment

 Darren

Brilliant Tutorials and examples.

Thanks!


2016-11-08



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.