Login Form to Specific Form

[matthewnsarah07]

Hopefully an easy one to sort out

I am using a basic login screen from databasedev.co.uk which suit my security needs.

However at the moment it simply checks user and password and sends those entering recognised details to a single main screen. How can I adopt the code below to send the user to a specific form for them only by the details given at login.

Would it be easier to add a field in the employees table which had the name of their form on or should it just all be within the code

Any suggestions?
Thanks


Code:

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
'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmSplash_Screen"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
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

 

[missinglinq]

One way would be to use the Select Case construct like this:

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
'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
 [B]Select Case Me.cboEmployee
   Case "Peter Pan"
     DoCmd.OpenForm "frmNeverNeverLand"
   Case "Alice"
     DoCmd.OpenForm "frmWonderland"
   Case "BatMan"
     DoCmd.OpenForm "frmGothamCity"
   Case Else
     DoCmd.OpenForm "frmSplash_Screen"
 End Select
[/B]'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
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

Alternatively, in the same spot in your code where I placed the Select Case, you could use DLookUp() to check the Employee table to retrieve "their" form and open accordingly. This latter approach would probably work better if you frequently add users and/or you frequently change the form a given user needs to be sent to, because you could simply add/edit data in a table instead of having to go in to modify code to hardwire the data.

Something like:

MyForm = DLookup("strEmpForm", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value)

DoCmd.OpenForm MyForm

 

[matthewnsarah07]

Thanks for your help with this one, the second option works perfectly.

Very much appreciated

Matt

 

[missinglinq]

Glad you got it working!

Linq

 

[matthewnsarah07]

Need some help again, thought all was well with my form but I am getting a runtime error now and its pointing towards Me.cboEmployee.Value on the DLookup line. I have tried using SelectCase aswell and it also halts at cbo.Employee for some reason.

Any ideas why this might happen?

 

[missinglinq]

Need some help again, thought all was well with my form but I am getting a runtime error now and its pointing towards Me.cboEmployee.Value on the DLookup line. I have tried using SelectCase aswell and it also halts at cbo.Employee for some reason.

Any ideas why this might happen?

This is probably due to an incorrect name (an object name in code, such as your combobox name, that doesn't exist. In your last post you have cbo.Employee which should be cboEmployee without the dot between cbo and Employee. Perhaps that's the problem?

 

[feinj]

Tried logon to Myform - getting error

I tried using the alternative approach in this logon form which directs the user to a specific form based on a column in the tblEmployees table and got the following error message;

Run-time error '2467'

The expression you entered refers to an object that is closed or doesn't exist.

When I hit the Debug Button, I am taken to the portion of the code shown below:

MyForm = DLookup("strEmpForm", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value)

Note: I have created test forms to correspond to the logon names, I have added a text field column to the tblEmployees table named "strEmpForm", and have entered the test form names in this column in the table.

Please help!

 

[xmann]

Correct me if I'm wrong but I think there's a field in the table called strAccess for Employee Access Level that should be used to determine which form will open next. Can somebody help to incorporate this field into the code?

Many Thanks for your selfless deeds

 

[manojsikar1]

Thanx...ur command helped me to complete my project...Thumps Up...

 

[janeyg]

Hi

I am having trouble with a line of this code to. error 2471 with this part of the code

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



This is the full code I have used as noted earlier in this post.

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
'Close logon form and open splash screen
DoCmd.Close acForm, "frmLogon", acSaveNo
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
MyForm = DLookup("strEmpForm", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value)
DoCmd.OpenForm MyForm
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
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


I have a table named tbleEmployees with fields EmpID EmpName EmpPassword EmpForm
Can anyone advise what is wrong?
thanks

 

[cwald]

I'm using the Dlookup and it's not checking the case sensitivity, Upper or lower case doesn't matter. What else do I need to do to the table or form in order to check the case?