Logon problem

[Toolpusher]

I have a logon screen on my switchboard, user selects his name inserts his password presses command button and if its right he get access to a form. I have that working fine. His name and password are stored in the employee table along with the department hes in.

What I really want to happen is when he logins , dependant on the department hes in a macro will run which opens the form in a certain manner for his department. I have written the macros ok and they work fine on the other form. Just no idea how I can get this running from the event procedure.

 

[harmankardon]

A quick way of doing this would be to take the username and lookup the department of that employee using DLookup, store that in a variable, then do a select case on that variable for each department.

Here's a rough incomplete example of what I'm talking about:

strDept = DLookup(lookup syntax here)

Select Case strDept
     Case Sales
          Docmd.openform "mySalesForm"
     Case Accounting
          Docmd.openform "myAccountingForm"
End Select

Can you post the code you have now so I can be more specific?

 

[Toolpusher]

Hi thanks for the help heres my code so far it fails at Maintenance Dept no idea whats wrong not very good with VBA

'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

        strDepartment = DLookup(strDepartment, tblDepartment)
        
        Select Case strDepartment
        
        Maintenance Dept
        DoCmd.RunMacro "Maint"
        
        Production Dept
         DoCmd.RunMacro "Prod"
         
         Marine Dept
         DoCmd.RunMacro "Mar"
        

        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

 

[harmankardon]

There is a problem with the syntax in your department DLookup code and your case statement.

First of all, in the employees table, is there a column for department? How do we know which department an employee is in? You DLookup for department should follow the syntax of your password DLookup.

For the case statement, the syntax should more be along the lines of:

Select Case strDepartment
     Case "Maintenance Dept"
          DoCmd.RunMacro "Maint"
     Case "Production Dept"
          DoCmd.RunMacro "Prod"
     Case "Marine Dept"
         DoCmd.RunMacro "Mar"
End Select

Also as a suggestion, it would be better if each department had a department ID (autonumber or number), makes lookups quicker.

 

[Toolpusher]

harmankardon thanks for putting up with me changed code as below makes sense to me but stops at Me.strDepartment.value. Yes the strDepartment field is in the tblEmployee's table

'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) And Me.strDepartment.Value = DLookup("strDepartment", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then

        lngMyEmpID = Me.cboEmployee.Value
      
      
        Select Case strDepartment
     Case "Maintenance"
          DoCmd.RunMacro "Maint"
     Case "Production"
          DoCmd.RunMacro "Prod"
     Case "Marine"
         DoCmd.RunMacro "Mar"
End Select
        
 
      
        

        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

 

[harmankardon]

Well there is a problem with the logic in your code.

The steps that you need to take are roughly as follows:

1. 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

2. 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

3. Check to see if inputted password matches the username

If Me.txtPassword.Value <> DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
     MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
     Me.txtPassword.SetFocus
     Exit Sub
End If

4. If both username and password fields are filled out and password is correct then open the form for the dept which the employee belongs to

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

Select Case strDepartment
     Case "Maintenance"
          DoCmd.RunMacro "Maint"
     Case "Production"
          DoCmd.RunMacro "Prod"
     Case "Marine"
         DoCmd.RunMacro "Mar"
End Select

Now I'm not sure about the use of Me.controlName.value, I'm not sure if you need the .value part or not. Also, I'm not quite sure how to incorporate your "3 password tries" check into the code.

Maybe someone else could offer some insight on the above questions?

 

[Toolpusher]

Harmankardon

Your a star code works perfect thanks so much in taking the time to help dimwits like me.

Removed the three strikes and your out, dont need that anyway.

Now I can move on with this DB reinvigorated thanks again

 

[harmankardon]

Awesome, glad to hear