simple problem in vba, please help

[deadelgabar]

hi, all
im working in access 2007 vba, i write a code in the login screen, to check if the user name and password match with the corresponding username and password in the database.
the problem im facing is if a user enters a usernam correct and a password which matches another user name the system will login.
example:
username:saad,,, password:saad, accounttype: admin.
username:jhone,, password:jhon, accounttpe: manager.

if jhone entered his username and the password of saad the system will log him as admin.

Public Sub Command4_Click()


  If IsNull([Combo0]) = True Then 'Check UserName
        MsgBox "Username is required", vbOKOnly, "Required Data"
        
    ElseIf IsNull([PASSWORD]) = True Then 'Check Password
        MsgBox "Password is required", vbOKOnly, "Required Data"
    Else
    If Me.PASSWORD.Value = DLookup("USERNAME", "SYS_USER", "[password]='" & Me.PASSWORD.Value & "'") Then
            strUser = Me.Combo0.Value 'Set the value of strUser declared as Global Variable
            strAccountType = DLookup("USER_TYPE", "SYS_USER", "[password]='" & Me.PASSWORD.Value & "'") 'set the value of strRole declared as Global Variable
            DoCmd.Close
            MsgBox "Welcome Back, " & strAccountType, vbOKOnly, "Welcome"
            DoCmd.OpenForm "First Screen"
            
        Else
            MsgBox "Invalid Password. Please try again.", vbOKOnly, "Invalid Password"
            PASSWORD.SetFocus
    
        End If
    
    End If

End Sub

[BitBlt]

Your selection criteria in your DLookups are incomplete. You should probably use something like this (Assuming that the value in [Combo0] is the user name):

If Me.PASSWORD.Value = DLookup("USERNAME", "SYS_USER", "[username]='" & me.[Combo0] & "' and [password]='" & Me.PASSWORD.Value & "'")

That way you restrict the row checked against the combination of selected user and supplied password.

Please note that this method will have to be used everywhere you are using "[password]=" as your selection criterion.

Also, from the MSAccess VBA Help File:Tips

Although you can use the DLookup function to display a value from a field in a foreign table, it may be more efficient to create a query that contains the fields that you need from both tables and then to base your form or report on that query.
Hope this helps.

[deadelgabar]

thanks very much for reply,
but unfortunatly i tried and it didnt work,
it always returns that invalid password.

[Jx_Man]

If Me.PASSWORD.Value = DLookup("password", "tblEmployees", "[username]=" & Me.Combo0.Value) Then

[deadelgabar]

data type mismatch in criteria expression,, this is the error.
i tried many times yesterday, if any one please can help me here..
thanks in advance.. :)

[Jx_Man]

data type mismatch in criteria expression,, this is the error. i tried many times yesterday, if any one please can help me here.. thanks in advance..

Are you copied all the code without change it? i was wrong in type table name "tblEmployees", it must be "SYS_USER". Sorry, my bad.
So, it should like this follwoing line:

If Me.PASSWORD.Value = DLookup("password", "SYS_USER", "[username]=" & Me.Combo0.Value) Then

[deadelgabar]

thanks you very mcuh for your reply, i really thank u so much for trying helping me,
but i didnot copy the code as it is, i made the table SYS_USER and it didnt work, it told me criteria missmatch, i cant even understand it..
sorry for bieng annouying

[deadelgabar]

ok the problem is finally solved,
thanks for every one cheered me up.
i only had a small problem in the criteria seection but it is solved now.
thanks alot

[Jx_Man]

ok the problem is finally solved, thanks for every one cheered me up. i only had a small problem in the criteria seection but it is solved now. thanks alot

It's great to know you already solved it.
But would you like to share with us how you solved it?
So when another user search a same thread like yours they will get the answer too..

Thank you.