954,557 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Have something to say? Contribute New Article Reply to this Article

simple problem in vba, please help

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
deadelgabar
Light Poster
31 posts since Apr 2010
Reputation Points: -1
Solved Threads: 0
 

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.

BitBlt
Master Poster
711 posts since Feb 2011
Reputation Points: 367
Solved Threads: 109
 

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

deadelgabar
Light Poster
31 posts since Apr 2010
Reputation Points: -1
Solved Threads: 0
 
If Me.PASSWORD.Value = DLookup("password", "tblEmployees", "[username]=" & Me.Combo0.Value) Then
Jx_Man
Nearly a Senior Poster
3,329 posts since Nov 2007
Reputation Points: 1,372
Solved Threads: 444
 

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

deadelgabar
Light Poster
31 posts since Apr 2010
Reputation Points: -1
Solved Threads: 0
 
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
Jx_Man
Nearly a Senior Poster
3,329 posts since Nov 2007
Reputation Points: 1,372
Solved Threads: 444
 

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
Light Poster
31 posts since Apr 2010
Reputation Points: -1
Solved Threads: 0
 

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

deadelgabar
Light Poster
31 posts since Apr 2010
Reputation Points: -1
Solved Threads: 0
 
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.

Jx_Man
Nearly a Senior Poster
3,329 posts since Nov 2007
Reputation Points: 1,372
Solved Threads: 444
 

This question has already been solved

Post: Markdown Syntax: Formatting Help
You
View similar articles that have also been tagged: