Problem with coding User login form in ACCESS 2010

[chriss_ko]

I have used Combo box. security_level field is the one that describes their levels and there are only two levels 1 and 2... My design is like this …. Below…
I have a table called User (user_ID, User_Name, Password, Security_Level)
On form there is a combo box for user name and text box for password.
Two command button where one is for EXIT (Closing the application) and the second button is to run the code.

if the password in table User matches value chosen in combo box or user name and password are correct
Then it should check if Security_Level of the user is equal to 1 to displays a form called Admin
and when the Security_Level of the user is equal to 2 to display a form called user1.

NOTE: All that I want is to have a login that has two user and each user when login opens his/her own form which is different from the other user.

 

[mikejaytlabustro]

Private Sub YourSecondButton_Click()

Dim PasswordLookup As String
Dim SecurityLevelLookup As Integer

PasswordLookup = Nz(DLookup("Password", "User", "User_ID=" & Nz(MyUserID, 0)), 0)
SecurityLevelLookup = Nz(DLookup("Security_Level", "User", "User_ID=" & Nz(MyUserID, 0)), 0)

If PasswordLookup = MyPassword And SecurityLevelLookup = 1 Then
DoCmd.OpenForm "Admin", acNormal
Else

If PasswordLookup = MyPassword And SecurityLevelLookup = 2 Then
DoCmd.OpenForm "User1", acNormal
Else

End If
End If

End Sub

 

[mikejaytlabustro]

Set your ComboBox Name to: MyUserID
Set your Password Textbox Name to: MyPassword

 

[chriss_ko]

Thanks very much for your support,
but it brings this error.
"run time error 3075
syntax error (missing operator) in query operation 'user_id='.

 

[pr2-eugin]

I have also answered your question in the other thread..

Syntax error mostly signifies that you are comparing a String to a Number.. Show the code you have written,along with the field types of your table..

 

[chriss_ko]

Thank you very much it has work perfectly, thanks a lot again.
Asante (Swahili).

Is it possible to display a msg when a user didn't write the user name to display a msg saying ENTER USER NAME and when he forgets to write password to say write password and when its wrong password to say incorrect password or user name.???

 

[chriss_ko]

Thanks very much, it has worked correctly, thanks again.
Asante sana (Swahili)

Is it possible to display a msg when a user didn't write the user name to display a msg saying ENTER USER NAME and when he forgets to write password to say write password and when its wrong password to say incorrect password or user name.???

 

[pr2-eugin]

Try this If condition to see if either of the two is empty..

Private Sub Command9_Click()
    Dim PasswordLookup As String
    Dim SecurityLevelLookup As Integer
    
    [COLOR=Red][B]If Len(MyUserID & vbNullString) <> 0 And Len(MyPassword & vbNullString) <> 0 Then[/B][/COLOR]
        PasswordLookup = Nz(DLookup("Password", "User", "user_id=" & Nz(MyUserID, 0)), 0)
        SecurityLevelLookup = Nz(DLookup("Security_Level", "User", "User_ID=" & Nz(MyUserID, 0)), 0)

        If PasswordLookup = MyPassword And SecurityLevelLookup = 1 Then
            DoCmd.OpenForm "Admin", acNormal
        ElseIf PasswordLookup = MyPassword And SecurityLevelLookup = 2 Then
            DoCmd.OpenForm "User1", acNormal
        End If
    [COLOR=Red][B]Else
        MsgBox "UserName/Password is missing, please enter all required information", vbCritical, "Missing info."
    End If[/B][/COLOR]
End Sub

 

[chriss_ko]

Thanks a lot, it has worked perfectly.

But i want it to clear the password after it has login/ open the required form because the password still remains there and if another user clicks login he can enter through you account. therefore i want it to clear the password in the text box just after log in. here are my codes:

Private Sub Command11_Click()

Dim PasswordLookup As String
Dim SecurityLevelLookup As Integer

PasswordLookup = Nz(DLookup("Password", "Users", "User_ID=" & Nz(MyUserID, 0)), 0)
SecurityLevelLookup = Nz(DLookup("Security_Level", "Users", "User_ID=" & Nz(MyUserID, 0)), 0)

If PasswordLookup = MyPassword And SecurityLevelLookup = 1 Then
DoCmd.OpenForm "MAIN SWITCHBOARD", acNormal
End If
'Check to see if data is entered into the combo box
If IsNull(Me.MyUserID) Or Me.MyUserID = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.MyUserID.SetFocus
End If
'Check to see if data is entered into the password box
If IsNull(Me.MyPassword) Or Me.MyPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.MyPassword.SetFocus
Exit Sub
End If
If PasswordLookup = MyPassword And SecurityLevelLookup = 1 Then
DoCmd.OpenForm "Admin", acNormal
Else
MsgBox "invalid username or password", vbCritical, "missing information,"
End If
'If User Enters incorrect password 3 times
'database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts < 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub

 

[pr2-eugin]

Well.. There are some things that needs to change in your code..

1. You have used intLogonAttempts, but have not declared it.. Thus it might not be stable when calculating.. So Dim it.. As a matter of fact any variable you wish to use it, use Dim and a type.. To enforce this, in the VBA window under Options, select 'Require Variable Declaration'..

2. Checking for Null values should take place before using them.. So bring the code to check for Null values before using them in DLookUp..

3. You have not used Exit Sub in the UserID check..

4. If you do not need the Login Form after successful login, then you either can close it or hide it..

5. The condition to check would be > 3, if it is less than 3, then even at the first try the Application will Quit..

6. The condition you have in the authentication looks disturbing.. they are the same conditions??

7. This has nothing to do with your code, but the usage of the forum, when presenting your code wrap them in CODE tags to preserve any formatting.. This will make the code easier to read, and pleasant to look at.

Private Sub Command11_Click()
    Dim PasswordLookup As String, [COLOR=Red][B]intLogonAttempts As Integer[/B][/COLOR]
    Dim SecurityLevelLookup As Integer

    [COLOR=Green]'Check to see if data is entered into the combo box[/COLOR]
[COLOR=Blue]    If IsNull(Me.MyUserID) Or Me.MyUserID = "" Then
        MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.MyUserID.SetFocus
        [COLOR=DarkOrange][B]Exit Sub[/B][/COLOR]
    End If[/COLOR]

    [COLOR=Green]'Check to see if data is entered into the password box[/COLOR]
[COLOR=Blue]    If IsNull(Me.MyPassword) Or Me.MyPassword = "" Then
        MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.MyPassword.SetFocus
        Exit Sub
    End If[/COLOR]
    
    PasswordLookup = Nz(DLookup("Password", "Users", "User_ID=" & Nz(MyUserID, 0)), 0)
    SecurityLevelLookup = Nz(DLookup("Security_Level", "Users", "User_ID=" & Nz(MyUserID, 0)), 0)

    If PasswordLookup = MyPassword And SecurityLevelLookup[COLOR=DarkOliveGreen][B] = 1[/B][/COLOR] Then
        DoCmd.OpenForm "MAIN SWITCHBOARD", acNormal
        [B][COLOR=DarkOrchid]DoCmd.Close acForm, Me.Name[/COLOR][/B]
        Exit Sub
    ElseIf PasswordLookup = MyPassword And SecurityLevelLookup [COLOR=DarkOliveGreen][B]= 1[/B][/COLOR] Then
        DoCmd.OpenForm "Admin", acNormal
        [B][COLOR=DarkOrchid]DoCmd.Close [/COLOR][/B][B][COLOR=DarkOrchid]acForm,[/COLOR][/B][B][COLOR=DarkOrchid] Me.Name[/COLOR][/B]
        Exit Sub
    Else
        MsgBox "Invalid UserName or Password", vbCritical, "Wrong information,"
    End If

   [COLOR=Green] 'If User Enters incorrect password 3 times
    'database will shutdown[/COLOR]
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts[B][COLOR=Magenta] >[/COLOR][/B] 3 Then
        MsgBox "You do not have access to this database.Please contact admin.", _
        vbCritical, "Restricted Access!"
        Application.Quit
    End If
End Sub

 

[chriss_ko]

It brings me this error,
"Compile error
Method or data member not found"
then it high lights ."closeform"

 

[pr2-eugin]

Sorry that was my bad.. try the edited code..

 

[chriss_ko]

Sorry,
What is that? is it a program? how do i get it?
if i remove this two line it work but it does not remove the password nor closing the form. also it does not close the application after the user enters wrong password more than 3 times. help me how to correct these codes.
"
DoCmd.Close acForm, Me.Name
DoCmd.Close acForm, Me.Name

 

[pr2-eugin]

What do you mean? I do not understand.. I changed the code after your reply.. Copy and past the code in Post # 10.. It should be fine..

 

[chriss_ko]

Thank you very much, it has worked.
Asante Sana (Swahili).

 

[pr2-eugin]

Glad to hear.. Good Luck !!