Login form problem

[samy]

Hi all

I need help with my Video rental store project the problem is that I want a security login method works with the my requirements. The manager has his own form with all the information he need and the other employees has another form.

I used this tutorial http://www.databasedev.co.uk/login.html to make the login form

the employee tables has ID, Password, Access Level

The VB code is like this

Option Compare Database
Private intLogonAttempts As Integer
Private Sub Form_Open(Cancel As Integer)
'On open set focus to combo box
Me.cboEmployeeLogOn.SetFocus
End Sub

Private Sub cboEmployeeLogOn_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub

Private Sub btnLogin_Click()

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

If IsNull(Me.cboEmployeeLogOn) Or Me.cboEmployeeLogOn = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployeeLogOn.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("EmployeePassword", "tblEmployee", "[EmployeeID]=" & Me.cboEmployeeLogOn.Value) Then

lngMyEmpID = Me.cboEmployeeLogOn.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmMainMenu"

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 admin.", vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub

I am asking for two things. First I want the Manger go to his form after the login completed and all the other employees go to another form I specify.

Second if there is a way that I show the name of the Employee in the destination form by knowing his ID (passing the ID to the other form).

That’s all if any one can help please

 

[Birdy]

When you call the form to load after entering the User ID, you need something like :

If Me.cboEmployeeLogOn = <Manager UserID> then
docmd.openform <Managers Form>
else
docmd.openform <Employees Form>
end if

For the second question, you need to set the UserID to be able to be picked up later. Something like :

dim UserName as String

UserName = Me.cboEmployeeLogOn

Then you need to have a GetUserName function

Public Function GetUserName()
GetUserName = UserName

End Function

On the Employee's form, add an Unbound Text Box and set the control source to =GetUserName()



Hope this helps

 

[samy]

Thanks Birdy thats was helpful it work now

I did a search on the forum also and use one of the sample DBs

This is my code now

The basEmpInfo module

Option Compare Database

Option Explicit

Public Type EmployeeInfo
    lngMyEmpID As Long
    EmployeeName As String
    EmployeePassword As String
    EmployeeLevel As Long
    
End Type

Public Employee As EmployeeInfo

The Logon Form code

Option Compare Database
Option Explicit

Public lngLogAttempts As Long
Const Mssg1 = "  Please Enter User Name and Password.   "
Const Mssg2 = "Logon Details Incorrect... User Not Found.    "
Const Title1 = " Logon Error"
Private Sub cmdQuit_Click()
On Error GoTo Err_cmdQuit_Click
    DoCmd.Quit
    DoCmd.Close acForm, Me.Name
Exit_cmdQuit_Click:
Exit Sub
Err_cmdQuit_Click:
    MsgBox Err.Description, , " LogOn Error"
    Resume Exit_cmdQuit_Click
End Sub
Private Sub Form_Load()
On Error GoTo Err_Form_Load
    Me.cboEmployeeLogOn.SetFocus
    lngLogAttempts = 0
Exit_Form_Load:
    Exit Sub
Err_Form_Load:
    MsgBox Err.Description, , " LogOn Error"
    Resume Exit_Form_Load
End Sub

Private Sub cboEmployeeLogOn_AfterUpdate()

    Employee.EmployeeLevel = Me.cboEmployeeLogOn.Column(3)

'After selecting user level set focus to password field
Me.txtPassword = Null
Me.txtPassword.SetFocus

Exit_cboEmployeeLogOn_AfterUpdate:
    Exit Sub
    
Err_cboEmployeeLogOn:
    
    Select Case Err.Number
    
    Case 13
    Resume Next
    
    Case 94
    Resume Exit_cboEmployeeLogOn_AfterUpdate
    
    Case Else
    MsgBox Err.Description
    Resume Exit_cboEmployeeLogOn_AfterUpdate
    
    End Select
    
End Sub
Private Sub btnLogin_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Set db = CurrentDb()
    strSQL = "SELECT * FROM tblEmployee WHERE [EmployeeID] = " & Me.cboEmployeeLogOn.Value
'CHECKING FOR NULLS
        If IsNull(Me.cboEmployeeLogOn) Then
            MsgBox Mssg1, vbCritical, Title1
            Me.cboEmployeeLogOn.SetFocus
            Call CheckLogAttempts
        ElseIf IsNull(Me.txtPassword) Then
            MsgBox Mssg1, vbCritical, Title1
            Me.txtPassword.SetFocus
            Call CheckLogAttempts
'VALIDATING PASSWORD
        Else
            If Me.txtPassword.Value = DLookup("EmployeePassword", "tblEmployee", "[EmployeeID]=" & Me.cboEmployeeLogOn.Value) Then
            
'PASSING VARIABLES TO DB
                Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
                With Employee
                    .EmployeeName = rst.Fields("EmployeeName")
                End With
                rst.Close
'LEVEL LOGON FORM
                
                
                  Select Case Me.cboEmployeeLogOn.Column(3)
                  
                    Case 1      'Administrator Logged On
                    
                       DoCmd.OpenForm "frmManagerMainMenu"
                
                    Case 2      'Manager Logged On
                    
                       DoCmd.OpenForm "frmMainMenu"
                    
                    Case 3      'User Logged On
                    
                       DoCmd.OpenForm "frmMainMenu"
                    
                    End Select
                
                       DoCmd.Close acForm, "frmLogon", acSaveYes
                
            Else
                MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
                Me.txtPassword.SetFocus
                Call CheckLogAttempts
            End If
        End If
End Sub
 
Private Sub CheckLogAttempts()
    If lngLogAttempts >= 2 Then
        MsgBox "     It appears you are having trouble logging on.    " & _
                Chr(13) & "Please contact your System Administrator " & _
                "for assistance.       ", vbCritical, Title1
    DoCmd.Quit
    DoCmd.Close acForm, Me.Name
    Else
        lngLogAttempts = lngLogAttempts + 1
    End If
End Sub

Each Main Menu code

Option Compare Database

Public Function GetUserName()
GetUserName = Employee.EmployeeName

End Function

Private Sub Form_Load()
'GETTING VARIABLE FOR FORM

    Dim strWelcome As String
    strWelcome = "Welcome, " & GetUserName()
        DoCmd.Maximize
        Me.txtWelcomeMssg = strWelcome
End Sub

Private Sub btnOpenCustomerMainMenu_Click()
On Error GoTo Err_btnOpenCustomerMainMenu_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmCustomer"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnOpenCustomerMainMenu_Click:
    Exit Sub

Err_btnOpenCustomerMainMenu_Click:
    MsgBox Err.Description
    Resume Exit_btnOpenCustomerMainMenu_Click
    
End Sub

Thanks again