By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,496 Members | 1,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,496 IT Pros & Developers. It's quick & easy.

Access Login Form problems

P: 2
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
Sep 6 '07 #1
Share this Question
Share on Google+
3 Replies


Scott Price
Expert 100+
P: 1,384
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogin_Click()
  2.  
  3. 'Check to see if data is entered into the UserName combo box
  4.  
  5. If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
  6. MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
  7. Me.cboEmployee.SetFocus
  8. Exit Sub
  9. End If
  10.  
  11. 'Check to see if data is entered into the password box
  12.  
  13. If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
  14. MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
  15. Me.txtPassword.SetFocus
  16. Exit Sub
  17. End If
  18.  
  19. 'Check value of password in tblEmployees to see if this matches value chosen in combo box
  20.  
  21. If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
  22.  
  23. lngMyEmpID = Me.cboEmployee.Value
  24. End If
  25. 'Close logon form and open FrontSheet
  26.  
  27. DoCmd.Close acForm, "frmLogon", acSaveNo
  28.  
  29. 'Open correct form
  30. Dim strAccessLevel As String
  31.  
  32. strAccessLevel = DLookup("[strAccess]", "tblEmployees", "[strEmpName]=" & Me.cboEmployee.Value)
  33.  
  34. If strAccessLevel = "Admin" Then
  35. DoCmd.OpenForm "Frontsheet"
  36. Else
  37. If strAccessLevel = "User" Then
  38. DoCmd.OpenForm "frmAdvisorSummary"
  39. Else
  40. MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
  41. Me.txtPassword.SetFocus
  42. Exit Sub
  43. End If
  44. End If
  45.  
  46. 'If User Enters incorrect password 3 times database will shutdown
  47.  
  48. intLogonAttempts = intLogonAttempts + 1
  49. If intLogonAttempts > 3 Then
  50. MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
  51. Application.Quit
  52. End If
  53. 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
Sep 8 '07 #2

P: 2
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogin_Click()
  2.  
  3. 'Check to see if data is entered into the UserName combo box
  4.  
  5. If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
  6. MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
  7. Me.cboEmployee.SetFocus
  8. Exit Sub
  9. End If
  10.  
  11. 'Check to see if data is entered into the password box
  12.  
  13. If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
  14. MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
  15. Me.txtPassword.SetFocus
  16. Exit Sub
  17. End If
  18.  
  19. 'Check value of password in tblEmployees to see if this matches value chosen in combo box
  20.  
  21. If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
  22.  
  23. lngMyEmpID = Me.cboEmployee.Value
  24. End If
  25. 'Close logon form and open FrontSheet
  26.  
  27. DoCmd.Close acForm, "frmLogon", acSaveNo
  28.  
  29. 'Open correct form
  30. Dim strAccessLevel As String
  31.  
  32. strAccessLevel = DLookup("[strAccess]", "tblEmployees", "[strEmpName]=" & Me.cboEmployee.Value)
  33.  
  34. If strAccessLevel = "Admin" Then
  35. DoCmd.OpenForm "Frontsheet"
  36. Else
  37. If strAccessLevel = "User" Then
  38. DoCmd.OpenForm "frmAdvisorSummary"
  39. Else
  40. MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
  41. Me.txtPassword.SetFocus
  42. Exit Sub
  43. End If
  44. End If
  45.  
  46. 'If User Enters incorrect password 3 times database will shutdown
  47.  
  48. intLogonAttempts = intLogonAttempts + 1
  49. If intLogonAttempts > 3 Then
  50. MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
  51. Application.Quit
  52. End If
  53. 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
Sep 20 '07 #3

Scott Price
Expert 100+
P: 1,384
Thanks Scott That's really helplfull. much appreciated

Regards
Stuart
Glad it worked for you!

Regards,
Scott
Sep 20 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.