Problems with tracking users

[sk88]

I have a table called 'tblEmployee' that holds userID, username and password

A second table called 'METRICS' that holds all the information that is asked in a customer form. Like - first name, last name etc.


When I first enter my database, the user/employee will be asked for its login name and password.

When signed in, a form MAIN MENU will pull up and the user will have an option to click and fill out the customer form or to export all the data saved in METRICS.

Now, I need Access to track as to which user filled the customer form so that when exported to excel, I would know which employee filled which customer form. The time is not important to track but the name of the user is something I really need.

How do I go about doing this? HELP PLEASE!

Thank you.

[dsatino]

There's a lot of ways to go about this, but you need to supply some additional info such as:

Is the application on shared workstations? If so, do the users have to logon to the workstation first?

Is the application split into a Front-end back end?

[NeoPa]

You're asking about logging transactions.

What you need to do is to copy the relevant information to a separate log table every time you perform one of the transactions you're interested in.

That's the basic concept, and that's all your question warrants at this time. It should be enough for you to take on and make progress with though.

[TheSmileyCoder]

I personally use this approach:
User has a local frontend, on his own PC, connected to a server on the local network.

I have a LOCAL(Only in frontend) table, tbl_CurrentUser which is hidden. It contains only 1 field, lng_CurrentUserID (number,Long), and always contains only 1 record, the ID of the user logged into the frontend. I have a login form, that upons succesfull user validation (Typing password) updates this record.

In a public module I have the following:

Option Compare Database
Option Explicit
private lngCurUserID as long
 
Public Function UserID() As Long
    'Returns the id of the current user
    If lngCurUserID = 0 Then
        'Means its not set, so set it
        lngCurUserID = DLookup("lng_CurrentUserID", "tbl_CurrentUser")
 
    End If
    UserID = lngCurUserID 
 
 
End Function

The userID is basicly stored both in a variable, and in the table. As long as everything runs fine, the userID is simply returned as the variable, but in case an unhandled error occurs, the UserID function will check for 0, and if so, get the correct value. (The default value of a long is 0, so in case of error, the value of lngCurUserID will be 0.) In your case the ID to store would come from your tbl_Employees.


Now this just tells me who is logged in but we still need to record that information along with the record. In each of my tables, I have the fields ID_CreatedBy, dt_Created, ID_ChangedBy,dt_Created, bound to controls on the form tb_CreatedBy, tb_DateCreated, tb_ChangedBy,tb_DateChanged. These fields are usually hidden from the user, but you can choose to show them, allthough you would need to convert the ID_CreatedBy into a username.

In the Before_Update event of my forms I use the following (after validation of record):

If me.NewRecord Then
  Me.tb_CreatedBy=UserID()
  Me.tb_DateCreated=Now()
End IF
  Me.tb_ChangedBy=UserID()
  Me.tb_DateChanged=Now()

I don't know if something similar could be applied for your design. I personally work in an environment where we dont do much in terms of security in the particular applicaiton. Any user with a sufficient level of access knowledge could easily change his ID, but instead of spending alot of resources on making the application more secure we feel its better spent on "real" development of the application. Im just saying this, as its something you need to consider, how high a degree a level of security you wish to have. This is what works for us.