Access Login Form problems

[stumo]

Hi

I'm fairly new to access and as such my experience of VBA is somewhat limited. I have a login form which is linked to an "employees table" which asks for users to enter their name and password. The current VBA performs all the relevant checks around user name & password etc with no problems. However based on the users access level (which is set in the field "strAccess" in the Enployees table) when I click the "Login" button I'd like to open 1 of 2 different forms, i.e "Admin" access level to open "Frontsheet" and "User" access level to open "frmAdvisorSummary", the trouble is I keep getting a "runtime error 2467" which points me to the following line of code "strAccessLevel = DLookup("[strAccess]", "tblEmployees", "[strEmpName]=" & Me.cboEmployee.Value)"

The whole code is as follows, any help/advice would be greatfully recieved as I'm at a complete loss.
Hope this makes sense

Many Thanks
Stuart


Full Code is

Private Sub cmdLogin_Click()

'Check to see if data is entered into the UserName combo box

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

'Check to see if data is entered into the password box

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

'Check value of password in tblEmployees to see if this matches value chosen in combo box

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value
End If
'Close logon form and open FrontSheet

DoCmd.Close acForm, "frmLogon", acSaveNo

'Open correct form
Dim strAccessLevel As String

strAccessLevel = DLookup("[strAccess]", "tblEmployees", "[strEmpName]=" & Me.cboEmployee.Value)

If strAccessLevel = "Admin" Then
DoCmd.OpenForm "Frontsheet"
Else
If strAccessLevel = "User" Then
DoCmd.OpenForm "frmAdvisorSummary"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
Exit Sub
End If
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 your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub

[Scott Price]

Private Sub cmdLogin_Click()
 
'Check to see if data is entered into the UserName combo box
 
If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployee.SetFocus
Exit Sub
End If
 
'Check to see if data is entered into the password box
 
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
 
'Check value of password in tblEmployees to see if this matches value chosen in combo box
 
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
 
lngMyEmpID = Me.cboEmployee.Value
End If
'Close logon form and open FrontSheet
 
DoCmd.Close acForm, "frmLogon", acSaveNo
 
'Open correct form
Dim strAccessLevel As String
 
strAccessLevel = DLookup("[strAccess]", "tblEmployees", "[strEmpName]=" & Me.cboEmployee.Value)
 
If strAccessLevel = "Admin" Then
DoCmd.OpenForm "Frontsheet"
Else
If strAccessLevel = "User" Then
DoCmd.OpenForm "frmAdvisorSummary"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
Exit Sub
End If
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 your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub

It looks to me like you are referencing an employees ID# in line 21 to the Me.cboEmployee.Value, but in line 32 you are referencing the employees' name to the same value. Change line 32 to reference the ID, and it should work.

Regards,
Scott

[stumo]

Private Sub cmdLogin_Click()
 
'Check to see if data is entered into the UserName combo box
 
If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployee.SetFocus
Exit Sub
End If
 
'Check to see if data is entered into the password box
 
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
 
'Check value of password in tblEmployees to see if this matches value chosen in combo box
 
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
 
lngMyEmpID = Me.cboEmployee.Value
End If
'Close logon form and open FrontSheet
 
DoCmd.Close acForm, "frmLogon", acSaveNo
 
'Open correct form
Dim strAccessLevel As String
 
strAccessLevel = DLookup("[strAccess]", "tblEmployees", "[strEmpName]=" & Me.cboEmployee.Value)
 
If strAccessLevel = "Admin" Then
DoCmd.OpenForm "Frontsheet"
Else
If strAccessLevel = "User" Then
DoCmd.OpenForm "frmAdvisorSummary"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
Exit Sub
End If
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 your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub

It looks to me like you are referencing an employees ID# in line 21 to the Me.cboEmployee.Value, but in line 32 you are referencing the employees' name to the same value. Change line 32 to reference the ID, and it should work.

Regards,
Scott

Thanks Scott That's really helplfull. much appreciated

Regards
Stuart

[Scott Price]

Thanks Scott That's really helplfull. much appreciated

Regards
Stuart

Glad it worked for you!

Regards,
Scott