login form

[jagnval]

I am working on a project database, I would like for each person to login and after the login open to a work entry form that is under their name only,instead of choosing from a drop down box. I have a login form and table of employees, and what i want to happen is when the person logs in to the login form I need something to look at who the person is on the login screen and then open a form to there name where they can enter their work information, any suggestions, (a form for each person?, query?, Code?)
Thanks

 

[lameid]

Assuming you are in a network environment...

environ("Username")

The above will return the username. If you add username to your employee table, you can easily set criteria based on the function and no one has to log in twice.

An alternate way to go would be to use a modal form that runs at startup (startup properties or autoexec macro) to set a global variable for the user/employee. Then use a function for criteria that returns the global variable.
This has the flexibility of allowing other users to do a quick entry on someone elses session but the other method pushes towards the strength of Windows authentication.

A third option which I would not recommend would be to use access security (at least through version 2003, I understand things change in 2007?) then you could use the CurrentUser() function to identify the user after the user has logged in.

 

[dhookom]

I don't think the Environ() variables are as reliable as the API function at Get Login name.

Duane
Hook'D on Access
MS Access MVP

 

[Pennmaker]

Here's what I do on a lot of projects.
I set up a number of tables and a query, tblEmployee, LookUpTableSecurity, LookUpTableWorkerLevel, query LogOnLookUp, and module.
For the tblEmployee I put in the following Fields and data type
Employee_ID AutoNumber
UserName Text
PassWord Text
EmployeeFirstName Text
EmployeeLastName Text
SecurityLevel Number
WorkerLevel Number
Supervisor

For LookUpTableSecurity I put in the following fields and data type
SecurityLevel_ID Number
SecurityLevel Text

For LookUpTableWorkerLevel I put in the following fields and data type
WorkerLevel_ID Number
WorkerLevel Text

The SQL for my Query looks like this
SELECT [tblEmployee].[EmployeeFirstName] & " " [tblEmployee].[EmployeeLastName]AS LogOn, [SecurityLevel_ID]& " "&[SecurityLevel] AS Security, [WorkerLevel_ID] & " "&[WorkerLevel] AS WorkerGroup, tblEmployee1.employee_ID & " "& tblEmployee1.EmployeeLastName & " "& tblEmployee1.EmployeeLastName AS Supervisor,tblEmployee1.EmployeeLastName & " "& tblEmployee1.EmployeeLastName AS Manager,[tblEmployee].[EmployeeLastName]AS LogOnName, tblEmployee.Employee_ID FROM
(((tblEmployee INNER JOIN tblEmployee AS tblEmployee1 ON tblEmployee.EmployeeSupervisor=tblEmployee1.Employee_ID) INNER JOIN LookUpTableWorkerLevel ON tblEmployee.WorkerLevel= LookUpTableWorkerLevel.WorkerLevel_ID) INNER JOIN LookUpTableSecurityLevel ON tblEmployee.SecurityLevel=LookUpTableSecurityLevel.SecurityLevel_ID) INNER JOIN tblEmployee AS tblEmployee2 ON tblEmployee1.EmployeeSupervisor=tblEmployee2.employee_ID WHERE (((tblEmployee.Employee_ID)=FunctionEmployee_ID()));

The Module is simple
Public gEmployee_ID As Long
Public gSecurityLevel As Long
Public gWorkLevel As Long

Public Function FunctionEmployee_ID() As Long
FunctionEmployee_ID = gEmployee_ID
End Function

I make a form with a combo box for the name and a text box for the password.
The code behind the form looks like this
Private Sub comEnter_Click()
On Error GoTo Err_Msg
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

'Check if a User or Password has been entered
Select Case Me.txUserNm
Case Is > "" 'something was entered
Select Case Me.txUserNm
Case Is > "" 'something was entered
Case Else
MsgBox "A password must be entered"
GoTo Exit_Now
End Select
Case Else
GoTo Exit_Now
End Select

'Now open the tblSecurity table to see if you have a match
Set db = CurrentDb
strSQL = "SELECT EmployeeFirstName, EmployeeLastName, UserName, Password, Employee_ID, SecurityLevel, WorkLevel " & _
"FROM tblEmployee where UserName = " & """" & Me.txUserNm & """" & " And " & _
"Password = " & """" & Me.txPassWrd & """"

Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

With rst
.MoveFirst
gEmployee_ID = !Employee_ID
gSecurityLevel = !SecurityLevel
gWorkLevel = !WorkLevel

End With

Set db = Nothing


Set rst = Nothing
DoCmd.Close
DoCmd.OpenForm "switchboard"

Exit_Now:
Set db = Nothing
Set rst = Nothing
Exit Sub

Err_Msg:
Select Case Err
Case 3021 'Did not fine anything in security table
MsgBox "Wrong Password"
Case Else
MsgBox "Error: " & Err.Number & vbCrLf & Err.Description, Title:="Error on Form F_UserLogOn"
End Select
Resume Exit_Now
End Sub

I can use the FunctionEmployeeID and pass it from Form to Form to make sure only the forms I want that user to access are allowed.