By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,478 Members | 1,526 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,478 IT Pros & Developers. It's quick & easy.

Problems with tracking users

P: 11
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.
Dec 16 '11 #1
Share this Question
Share on Google+
3 Replies

P: 393
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?
Dec 16 '11 #2

Expert Mod 15k+
P: 31,709
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.
Dec 16 '11 #3

Expert Mod 100+
P: 2,321
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. private lngCurUserID as long
  5. Public Function UserID() As Long
  6.     'Returns the id of the current user
  7.     If lngCurUserID = 0 Then
  8.         'Means its not set, so set it
  9.         lngCurUserID = DLookup("lng_CurrentUserID", "tbl_CurrentUser")
  11.     End If
  12.     UserID = lngCurUserID 
  15. 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):
Expand|Select|Wrap|Line Numbers
  1. If me.NewRecord Then
  2.   Me.tb_CreatedBy=UserID()
  3.   Me.tb_DateCreated=Now()
  4. End IF
  5.   Me.tb_ChangedBy=UserID()
  6.   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.
Dec 16 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.