User Login Form (1 Viewer)

[Sunny Boy 1318]

Greetings,

I have been :banghead::banghead: for several hours with a code here.

I have a table with EmpID, EmpName and EmpPassword. I want to set up user login form to call up other forms.

With the below code, if a user enters a username and password, it looks if it matches the table and calls out Mainfrm.

I want to enable different user to call out different forms.

E.g; UserA will call out MainfrmA; UserB will call out MainfrmB and UserC will call out MainfrmC.

Can anyone help out with this? See below my codes for now




Private Sub cmdEXIT_Click()
DoCmd.Quit
End Sub
Option Compare Database
Private intLogonAttempts As Integer
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("EmpPassword", "tblEmployees", "[EmpID]=" & Me.cboEmployee.Value) Then
MyEmpID = Me.cboEmployee.Value
'Close logon form and open splash screen
DoCmd.Close acForm, "frmLogin", acSaveNo
DoCmd.OpenForm "MainFrmD"
Else
MsgBox "Password Invalid. Please Try Again", vbCritical + 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

 

[pr2-eugin]

Sunny, Create another field in the table that will have the User Level Access to the form name, which it can lookup to and open that Form..

PS: Could you Please use Code Tags when posting VBA Code

 

[Sunny Boy 1318]

Hi Paul,

How to I go about creating another field in the table that will have the User Level Access to the form name and how to call that by using the below code... Sorry for that... I have pasted the codes correctly now!!!

Private Sub cmdEXIT_Click()
DoCmd.Quit
End Sub
  Option Compare Database
  Private intLogonAttempts As Integer
  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("EmpPassword", "tblEmployees", "[EmpID]=" & Me.cboEmployee.Value) Then
      MyEmpID = Me.cboEmployee.Value
  'Close logon form and open splash screen
      DoCmd.Close acForm, "frmLogin", acSaveNo
      DoCmd.OpenForm "MainFrmD"
      Else
      MsgBox "Password Invalid. Please Try Again", vbCritical + 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

 

[pr2-eugin]

I am sure that is a typo or just copy/paste error, but Option statement should be the very first line of the VBA code.. Moving on..

Now you have EmpID, EmpName and EmpPassword fields in your table, so create another field type text; maybe call it formAccess.. Then for each user just add the form you want to direct them to.. Example..

EmpID    EmpName    EmpPassword    formAccess
1        Paul        sample        FormA
2        Sunny       beach         FormB

Then lookup for the form name associated with the user, and open that..

DLookup("formAccess", "tblEmployees", "[EmpID]=" & Me.cboEmployee)

Thus, the complete code would be..

Option Compare Database

Private intLogonAttempts As Integer

Private Sub cmdLogin_Click()
    [COLOR=Blue][B]Dim strFrmName As String[/B][/COLOR]
   [COLOR=Green] 'Check to see if data is entered into the UserName combo box[/COLOR]
    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
    
   [COLOR=Green] 'Check to see if data is entered into the password box[/COLOR]
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
        MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    
   [COLOR=Green] 'Check value of password in tblEmployees to see if this matches value chosen in combo box[/COLOR]
    If Me.txtPassword = DLookup("EmpPassword", "tblEmployees", "[EmpID]=" & Me.cboEmployee) Then
       [COLOR=Blue][B] strFrmName = DLookup("formAccess", "tblEmployees", "[EmpID]=" & Me.cboEmployee)[/B][/COLOR]
        [COLOR=Green]'Close logon form and open splash screen[/COLOR]
        DoCmd.Close acForm, "frmLogin", acSaveNo
        DoCmd.OpenForm [COLOR=Blue][B]strFrmName[/B][/COLOR]
    Else
        MsgBox "Password Invalid. Please Try Again", vbCritical + vbOKOnly, "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If
    
  [COLOR=Green]  [/COLOR][COLOR=Green]'If User Enters incorrect password 3 times database will shutdown[/COLOR]
    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
  
Private Sub cmdEXIT_Click()
    DoCmd.Quit
End Sub

 

[Sunny Boy 1318]

Works Perfectly Fine -