VBA example - Microsoft Access: open and close form

VBA open and close form

In this example, you will learn VBA Docmd.OpenForm and Docmd.Close(form) to open a new form and to close the current form. The example illustrates a Log In form that has a Label(Log In), a Line(Line7), two Text Boxes(User Name and Password), three Command Buttons(Sign In, Close and Create New Account) as the figure below:

  VBA example - Microsoft Access VBA  Log In Form 

-When the form loads, the two text boxes are blank and a cursor is set focus in User Name text box(See Log In form above).

-After you enter the user name and password correctly and click the Sign In command button, one message box will display like the figure below:

VBA example - Microsoft Access VBA Long In Message Box 

-Click Ok, then the Taxation form will appear.

-The Log In form does not have the upper right close button because the form border style is set to none. If you want to close this form, click Close command button.

-If you want or need to create a new user account, click Create New Account command button. The User Account form will appear. See the figure:

VBA example - Microsoft Access VBA User Account Form 

To have the Log In form as the figure above, you have to create the form in Form Design. See the figure below:

VBA example - Microsoft Access VBA Log In Form Design 

-Drag and drop a Label, a Line, two Text Boxes, and three Command Button to the form.

-Set Name properties to two Text Boxes:

Text Boxes:

Name: txtUserName

Name: txtPwd

-Set Name and Caption properties to a Label and three Command Buttons


Name: lblLogIn

Caption: LogIn

Command Buttons:

Name: CmdSignIn

Caption: SignIn

Name: CmdClose

Caption: Close

Name: CmdCreateNewAccount

Caption: Create New Account

BackStyle: Transparency


Then apply the VBA Code below:


Option Compare Database

Option Explicit


Private Sub Form_Load()


    TimerInterval = 500


End Sub


Private Sub CmdSignIn_Click()

' Check user name and password if not have

    If CheckUserName(txtUserName) = True Then

        Msgbox”Invalid user name, try the other.”


        txtUserName.SelLength = Len(txtUserName)

        Exit Sub

    ElseIf CheckPassword(txtPwd) = True Then

        Msgbox”Invalid password, try the other”


        txtPwd.SelLength = Len(txtPwd)

        Exit Sub


    ' Display a message and clear all data in the two text boxes then open a new form

        MsgBox "Log in succeed!", vbInformation


        DoCmd.OpenForm "frmTaxation", acNormal

    End If

End Sub


Private Sub CmdClose_Click()

    DoCmd.Close ' Close form

End Sub


Private Sub CmdCreateNewAccount_Click()

' Open form in form view mode

    DoCmd.OpenForm "frmUserAccount", acNormal

End Sub


Function CheckUserName(UserName As String) As Boolean

    Dim Rst As Variant

    Rst = DLookup("UserName", "TblUserAccount", "UserName='" & txtUserName & "'")

    If IsNull(Rst) Then

    CheckUserName = True

    End If

End Function


Function CheckPassword(Password As String) As Boolean

    Dim Rst As Variant

    Rst = DLookup("Password", "TblUserAccount", "Password='" & txtPwd & "'")

    If IsNull(Rst) Then

    CheckPassword = True

    End If

End Function


Sub ClearData()

    txtUserName = ""

    txtPwd = ""

End Sub


Private Sub Form_Timer()

'Generate random forecolor of label

    lblLogIn.ForeColor = QBColor(15 * Rnd)

End Sub

Note: For the User Account form, you visit Create User Account .


Shafiu comment


Hello for the great work done.

I noticed that when a user name is correct, then they can sign in even with another user's password.

Can you look at that defect so that the LookUp will relate the userName to the password.


Karthikeyan K comment

 Karthikeyan K

Awesome collections..Thanks to worldbestlearningcenter.com team.


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.