VBA example - Microsoft Access tutorial: Dlookup


VBA Dlookup

In this example, you will learn VBA DLookup Fucntion to find the User Name and Password from User Account  table(TblUserAccount). The syntax of DLookup Function is:

Expression = DLookup(Expr,Domain,[Criteria]

-Expression: A variable that has data type as Variant.

-Expr: A field of a table or query(String data type) that you want to find.

-Domain: A table or query name or it can be an SQL statement that contains the field you want DLookup function to return. The Domain has data type as string.

-Criteria: The argument is optional. It represents the conditional string used to filter information. It is like the WHERE clause of an SQL statement, but in here the word WHERE is not used.



The example illustrates the Log In form that has three Labels(Log In, msg1,msg2), 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  DLookup Log In Form 

-When the form loads, the two labels(msg1,msg2) is disable(See Log In form above) and the cursor is set focus in User Name text.

-If you not enter or enter the wrong user name and click Sign In command button, the message will appear. See the figure below:

VBA example - Microsoft Access VBA DLookup Log In Message1 Form 

-If you do not enter or enter the wrong password and click Sign In command button, the message will appear. See the figure below:

VBA example - Microsoft Access VBA DLookup Log In Message2 Form 

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

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 DLookup Log In Form Design 

-Drag and drop three Labels, 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

Labels:

Name: lblLogIn

Caption: LogIn

Name: lblmsg1

Caption: a

Name: lblmsg2

Caption: a

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()

    txtUserName.SetFocus

    lblmsg2.Visible = False

    lblmsg1.Visible = False

    TimerInterval = 500

    ClearData

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

Private Sub CmdSignIn_Click()

' Check user name and password if not create

    If CheckUserName(txtUserName) = True Then

        lblmsg1.Visible = True

        lblmsg1.ForeColor = vbRed

        lblmsg1.Caption = "Invalid User Name, Try the other."

        txtUserName.SetFocus

        txtUserName.SelLength = Len(txtUserName)

        Exit Sub

    ElseIf CheckPassword(txtPwd) = True Then

        lblmsg1.Visible = False

        lblmsg2.Visible = True

        lblmsg2.ForeColor = vbRed

        lblmsg2.Caption = "Invalid Password, Try the other."

        txtPwd.SetFocus

        txtPwd.SelLength = Len(txtPwd)

        Exit Sub

    Else

    ' Display a message then open a new form

        lblmsg2.Visible = False

        MsgBox "Log in succeed!", vbInformation

        ClearData

        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

 

Sub ClearData()

    txtUserName = ""

    txtPwd = ""

End Sub

 

Private Sub Form_Timer()

' Generate random fore color of label

    lblLogIn.ForeColor = QBColor(15 * Rnd)

End Sub

Note: To create the Taxation and User Account forms visit:

http://www.worldbestlearningcenter.com/index_files/Access-vba-selectcase-statement2.htm

http://www.worldbestlearningcenter.com/index_files/Access-vba-user-account.htm



Comments




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.