Recording User Useage

[DALIEN51]

Any help most appreciated!

I have a Access 2003 database I am developing which has a table called "tbl_users" in which I have the fields:
user name(text), network id(text), status(yes/no), long on time (date/time), log on date(date/time), log off time(date/time), log off date(date/time).

When a user logs on my autoexec macro calls a prodcedure which sets that users status to "yes" or ticked and stamps the log on time and date. Is it possible to call this procedure even if they bypass the autoexec by holding down the shift key?

In reverse to this when they click the "Exit System" on the Main Menu form there is a call to a procedure which unticks there status field and stamps the log off time and date. Again, can I trigger this procedure even if they exit the database without using my "Exit System" button (ie click the "x" to close the database)?

Regards,

Dalien51

 

[M8KWR]

your first point i do not know how to do, but your second point the way around this would be to put the procedure into a form on_close procedure.

But the best form to put this in is the form that loads up first, so a menu form. So even if they exit access via another way this form has still got to be closed by access and the procedure will run. But what i would do as well is disable to x in the top right corner of the form, and only have an exit application button on the form, this way the user can not just close this form down, and still use the application, which would not update the table according to your rules so to speak.

I've been thinking about your first point, if they hold shift down.... why would they do you have advance users?????

You could always write the procedure into each form when it loads, and simple if statement, so even if they load 2 forms the procedure would not run on the second form opening due to status field been Yes..

I hope this helps...

 

[gold007eye]

On the first issue. Unless needed I would disable the shift key on startup. If you need the developers to gain access to the database to modify anything; then create a macro that would run either with a hidden shortcut key (such as holding ctrl+o) that would run the macro or a "maintenance" button on your main form that only people with appropriate user and password information could get into. I have this set up on all of my databases and it works great.

 

[Mile-O]

I have the fields:
user name(text), network id(text), status(yes/no), long on time (date/time), log on date(date/time), log off time(date/time), log off date(date/time).

You can throw two of those fields away. All you need is one field for LoggedOn and one field for LoggedOff. Two simple Date/Time fields.

You can set the LoggedOn field's DefaultValue to Now() so that it records both the date and time the user logged on. The LoggedOff field would be datestamped with an UPDATE query using the Now() function.

When you do what you currently have you don't just store either the date or time in the field. If you put a date in a Date/Time field then you get a date and a time anyway. Thus #05/21/2005# is actually #05/21/2005 00:00:00#. Similarly, if you put a time (say #21:34:08# into the field without a date you actually get #12/31/1899 21:34:08#.

Of course, if you put both into the same field then you would get #05/21/2005 21:34:08# which you can actually use. With this you can work out a lot of numbers revolving around your users' logging in and out habits.

 

[boblarson]

What I do, is to create a hidden form that is basically just a very, very small form that I use the Load and Unload events to write to my user log table. If someone opens the database, regardless of method, the hidden form opens first, I have it set as the default form (Access 2000). And, then when the database closes, regardless of method, the hidden form's Unload event has the code to find the open logged off date/time and writes the exit date/time to the table.

 

[gold007eye]

Actually maybe you can help me at the same time.. Could you send me a copy of the procedure you use to capture this information. I have a database my associates log in to and it would be good if I could capture all the information above you listed for audit and workflow purposes

 

[DALIEN51]

This Is My Log On Code.........

This is my log on code and there is a similar on for logging off. In built is a check to see if the system still thinks a user is logged on when the log on date is not today (ie they crashed out!) as each user should shut down each night. It utilises a further function called USERNAME which generates the users network id which it references to my users table to get the users name.

Public Function CHECKUSERON()

Dim MYDB As Database
Dim MYTABLE As Recordset
Dim ACTIVITY As Recordset
Dim APPROVED As Long

Set MYDB = CurrentDb()
Set MYTABLE = MYDB.OpenRecordset("TBL_USERS")
Set ACTIVITY = MYDB.OpenRecordset("TBL_ACTIVITY LOG")
APPROVED = 0

MYTABLE.MoveFirst
Do Until MYTABLE.EOF

If IsNull(MYTABLE![LOG ON DATE]) Then
Else
If (MYTABLE![Status] = True) And (MYTABLE![LOG ON DATE] <> Format$(Now(), "dd/mm/yyyy")) Then
MYTABLE.Edit
MYTABLE![Status] = False
MYTABLE.Update

ACTIVITY.AddNew
ACTIVITY![User] = MYTABLE![User]
ACTIVITY![Name] = MYTABLE![Name]
ACTIVITY![Time] = Null
ACTIVITY![Date] = MYTABLE![LOG ON DATE]
ACTIVITY![Action] = "Abnormal System Exit"
ACTIVITY![Category] = 2
ACTIVITY.Update
End If
End If

If MYTABLE![User] = UserName Then
APPROVED = 1
MYTABLE.Edit
MYTABLE![Status] = True
MYTABLE![LOG ON TIME] = Mid(Now(), 12, 5)
MYTABLE![LOG ON DATE] = Left(Now(), 10)
MYTABLE.Update

ACTIVITY.AddNew
ACTIVITY![User] = MYTABLE![User]
ACTIVITY![Name] = MYTABLE![Name]
ACTIVITY![Time] = MYTABLE![LOG ON TIME]
ACTIVITY![Date] = MYTABLE![LOG ON DATE]
ACTIVITY![Action] = "User Log On"
ACTIVITY![Category] = 1
ACTIVITY.Update
End If

MYTABLE.MoveNext
Loop

If APPROVED = 0 Then
MsgBox ("You are not authorised to log on to this system"), , "Rentals System"

ACTIVITY.AddNew
ACTIVITY![User] = UserName
ACTIVITY![Name] = Null
ACTIVITY![Time] = Mid(Now(), 12, 5)
ACTIVITY![Date] = Left(Now(), 10)
ACTIVITY![Action] = "Unauthorised Log On Attempt"
ACTIVITY![Category] = 3
ACTIVITY.Update

DoCmd.Quit
End If

End Function