Lookup Value in a Table and Open Form

[66tigger40]

Hi

I have a simple User Information Table that based on the login I want to open one of three forms.

The AccessLevel can be either Full, Part or Activity. I have the first part working fine to check the userid against the userinformation tabel to can't get it to open the form based on the AccessLevel - missing something from the code and going round in circles!

Private Sub cmdLogin_Click()


Dim UserAccess As Variant


    If IsNull(Me.UserLogin) Or Me.UserLogin = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.UserLogin.SetFocus
        Exit Sub
    End If



    If IsNull(Me.UserPassword) Or Me.UserPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.UserPassword.SetFocus
        Exit Sub
    End If

    If Me.UserPassword.Value = DLookup("UserPassword", "tbl_UserInformation", _
            "[UserID]=" & Me.UserLogin.Value) Then

        UserID = Me.UserLogin.Value
        UserAccess = DLookup("[AccessLevel]", "Tbl_UserInformation", "[UserID] =" & Me.UserLogin.Value)
    Else
      

       MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.UserPassword.SetFocus
    Exit Sub
    

    End If
        
    If UserAcess = "Full" Then
    DoCmd.Close acForm, "Frm_Login", acSaveNo
    DoCmd.OpenForm "Frm_MainMenu"
    End If
    
    
    
    If UserAccess = "Part" Then
    DoCmd.Close acForm, "Frm_Login", acSaveNo
    DoCmd.OpenForm "Frm_MainMenuPart"
    
    End If
    
    
    If UserAccess = "Activity" Then
    DoCmd.Close acForm, "Frm_Login", acSaveNo
    DoCmd.OpenForm "Frm_MainMenuActivity"
   
    End If
    



    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

Thanks for any pointers!

 

[boblarson]

Try setting a breakpoint and stepping through the code to see what is happening at any one point.

Bob Larson
Access MVP
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[Pampers]

Hi there,
I see a typo in:

If UserAcess = "Full" Then
If UserA[r]c[/r]cess = "Full" Then

Just a suggestion. Instead of:
If IsNull(Me.UserLogin) Or Me.UserLogin = "" Then
You can use: If Trim(Me.UserLogin) & "" = ""

And Bob, thanx a lot for the code for distributing front ends on your site. Works great.





Pampers
Keeping it simple can be complicated

 

[Pampers]

Eh, I mean
I see a typo in:

If UserAcess = "Full" Then

Shoud be:

If UserAccess = "Full" Then

Just a suggestion. Instead of:
If IsNull(Me.UserLogin) Or Me.UserLogin = "" Then
You can use: If Trim(Me.UserLogin) & "" = ""

Other suggestion:
Use Option Explicit (just below Option Compare Database)

Pampers
Keeping it simple can be complicated

 

[JBinQLD]

G'day mate,

Look at the bottom three if statements:

Eg.

If UserAcess = "Full" Then
    DoCmd.Close acForm, "Frm_Login", acSaveNo
    DoCmd.OpenForm "Frm_MainMenu"
End If

I'd recommend opening your target form [bold]BEFORE[/bold] closing the one that's executing code

If UserAcess = "Full" Then
    [red]DoCmd.OpenForm "Frm_MainMenu"
    DoCmd.Close acForm, "Frm_Login", acSaveNo[/red]
End If

Good luck!

JB