Login to direct user to relevant form

[charlie442]

Hello again gurus,

In a login form I am trying to do a lookup on the user table and direct users to a specific form depending on their GroupID (User Group) in tblUser when they log in. The user ID is a TempVar that is added when a user logs in. Then I am trying to perform a dlookup for Group ID based on the TempVar.UserID. I have attached sample code on what I think it should be, however this is producing errors. As always any assistance hugely appreciated.

Charlie

    If DLookup("GroupID", "tblUser", "UserID = " & TempVars.UserID & "") = 1 Then
    DoCmd.OpenForm "frmMainForm"

 

[Peter D]

If tempvars.userID is a text string, try

If DLookup("GroupID", "tblUser", "UserID = '" & TempVars.UserID & "'") = 1 Then

(note the extra single quotes surrounding the tempvar variable)

 

[charlie442]

Thanks Peter for the tip. However it is still giving me an error message.

The UserID is a number so I am not sure it requires the single quotes.

I am not sure that I have the whole syntax correct in the statement:

"UserID = " & TempVars.UserID & ""

Hmmm??

 

[vbaInet]

It will be useful to know what the error actually is? And what code line does it error when you click Debug?

 

[charlie442]

Hi

Here is the full code. It is creating errors on the dlookups to find the group id on the user ID. This has been commented out for now.

Thanks

Private Sub cmdLogin_Click()
    Dim bValid As Boolean
    bValid = False
    
    
    'Check to make sure that username and password are not blank
    If Nz(Me.txtUserName, "") = "" Then
        MsgBox "User Name cannot be blank."
        Me.txtUserName.SetFocus
    ElseIf Nz(Me.txtPassword, "") = "" Then
        MsgBox "Password cannot be blank."
        Me.txtPassword.SetFocus
    Else
        'If the username is incorrect we'll tell the user that they entered an invalid username or password
        'It's generally considered an insecure practice to inform the user that they have entered a
        'correct or incorrect username.
        If DCount("UserName", "tblUser", "UserName = '" & Replace(Me.txtUserName, "'", "''") & "'") = 0 Then
            MsgBox "Sorry, you entered an invalid username or password."
        Else
            Dim sPswdHash As String
            sPswdHash = Hash(Me.txtPassword)
            If DLookup("PswdHash", "tblUser", "UserName = '" & Replace(Me.txtUserName, "'", "''") & "'") <> sPswdHash Then
                MsgBox "Sorry, you entered an invalid username or password."
            Else
                bValid = True
                'TempVars is only available in Access 2007 and Access 2010
                'Change this to use a global variable if you are using Access 2003 or older
                'Global variables do not survive resets while TempVars do
                'Storing the UserID is what determines if a user is logged in as well as
                'which user is logged in.
                TempVars.Add "UserID", DLookup("UserID", "tblUser", "UserName = '" & Replace(Me.txtUserName, "'", "''") & "'")
            End If
        End If
    End If
    
    If bValid = True Then
    DoCmd.OpenForm "frmMainForm"
    
    'Open the relevant form.
    'If DLookup("GroupID", "tblUser", "UserID = '" & TempVars.UserID & "'") = 1 Then
    'DoCmd.OpenForm "frmMainForm"
        
        'If DLookup("GroupID", "tblUser", "UserID = '" & TempVars.UserID & "'") = 2 Then
        'DoCmd.OpenForm "Manager_Interface_Form"
        
            'If DLookup("GroupID", "tblUser", "UserID = '" & TempVars.UserID & "'") = 3 Then
            'DoCmd.OpenForm "Manager_Interface_Form"
            
                'If DLookup("GroupID", "tblUser", "UserID = '" & TempVars.UserID & "'") = 8 Then
                'DoCmd.OpenForm "User_Interface_Form"
                
                    'If DLookup("GroupID", "tblUser", "UserID = '" & TempVars.UserID & "'") = 9 Then
                    'DoCmd.OpenForm "User_Interface_Form"
                    
                        'If DLookup("GroupID", "tblUser", "UserID = '" & TempVars.UserID & "'") = 10 Then
                        'DoCmd.OpenForm "CAM_Interface_Form"
                        
                            'If DLookup("GroupID", "tblUser", "UserID = '" & TempVars.UserID & "'") = 11 Then
                            'DoCmd.OpenForm "Assurance_Interface_Form"
                        
                                'If DLookup("GroupID", "tblUser", "UserID = '" & TempVars.UserID & "'") = 12 Then
                                'DoCmd.OpenForm "CAM_Interface_Form"
                        
                                    'If DLookup("GroupID", "tblUser", "UserID = '" & TempVars.UserID & "'") = 13 Then
                                    'DoCmd.OpenForm "Assurance_Interface_Form"
                                    
                                    'End If
                                'End If
                            'End If
                        'End If
                    'End If
                'End If
            'End If
        'End If
    'End If
    
        'Close this form
        DoCmd.Close acForm, "frmLogin"
    Else
        Me.txtPassword = Null
        Me.txtUserName.SetFocus
    End If
    
End Sub

 

[vbaInet]

Still no mention of the exact error message.

 

[charlie442]

Hi

Sorry the error message that comes up on the If DLookup statements is Run-time errror 438, Object does not suuport this property or method.

Thanks

 

[vbaInet]

I don't think you've mentioned the right error line. DLookup() doesn't return a "Object does not support this property or method" error. Please recheck your code, run it, click Debug when the error appears and it will take you to the error line highlighting it in yellow.

In the meantime, your DLookup statements are too cumbersome and I don't think it needed to be nested. Can you explain that block of code?

 

[charlie442]

Hi

The If Dlookup statements are what is highlighted and produce the error message I gave above?

Ok so I have user permissioning active in this database. The UserID in the User table is used as a Temporary Variable which is added when a user logs in. There is a field in tblUser called GroupID which determines the access that users have. Essentially I am trying to skip the step of each user having to first select his/her view from a main form, so that when they log in they are taken immediately to the form which is relevant to their group ID.

Cheers

 

[vbaInet]

I missed out the TempVars bit. It's the TempVars that's throwing that error. Hardcode the values instead for testing purposes.

So based on what your logic, do you feel it should be nested or should you be using IF...ELSE IF statements, or better still a SELECT CASE block?

 

[charlie442]

Hey

Thanks for that. I guess I could use If ...Else If Statements or Select Case Statements. However I am unsure what you mean by hardcoding the values. As people are lgging in and out of the database frequently it is the only way I know to keep track of who is doing what in the database.

 

[vbaInet]

You have a development/test environment don't you?

This is what I mean by hardcoding:

    If DLookup("GroupID", "tblUser", "UserID = '" & "SOME USER ID HERE" & "'") = 1 Then
        DoCmd.OpenForm "frmMainForm"

Or test your TempVars.UserID with a msgbox.

 

[charlie442]

Hi

I got there in the end. I was going about it the wrong way. This is the solution.

    If DLookup("GroupID", "tblUser", "UserName = '" & [Forms]![frmLogin]![txtUserName] & "'") = 1 Then
    DoCmd.OpenForm "frmMainForm"
    End If

Thanks for your help!

 

[vbaInet]

Good job charlie!

Happy to help.

NB: If you're calling txtUserName from the same form, you don't need the full reference, just use Me.txtUserName.