RE: Tracking Log In and Log Out times of Users

We have too many nested answers and it is getting hard to follow. I apologize
if I missed something.

If you copied the code I posted into a function, then you don't need to
worry abount an append query. The code I sent uses the recordset way of
doing it.

Since you are trying to learn to use VBA, we will use that method, although
it is not the only way to do it. With your form or report open in design
view, open the properties dialog and select the Events tab. Click in the text
box for the Load event. Click the command button with the 3 dots when it
appears. select code builder. Now put the call to the function in the event
procedure.

"Dan" wrote:

see below

"Klatuu" wrote:

See answers below, I hope this is useful.

"Dan" wrote:



"Klatuu" wrote:

It will take a little work, but you can do it. First, you will need to be
able to capture the userid of the logged on user. Here is a link to an API
routine that does that:
http://www.mvps.org/access/api/api0008.htm

I have copy this code and placed it in my modules

You're app will need a form that is opened when assess opens. You identify
that form from the menu bar Tools, Startup and put the name of the form in
Display Form/Page:

I have a start-up form that opens when the database opens.

This form should always be open. You can make it invisible, but it is the
easiest way to handle closing the application without having to put code in
every form's close event.

My startup form is always open and is the only way to navigate within the
database

You will need a function in a standard module that can be accessed from any
form or report that will write an entry to an audit log table.

I have a audit log tabel, however what fields should I put in this table.
What would this function look like. I'm new to module and I'm trying to
learn how to write code and how this works.

What fields you put in the table are up to you. You probably want UserId,
TimeStamp, and Object Accessed. (that would be the form or report opened, or
Logged In or Logged Out).

I set up my AuditLog with 4 fields, autoNumber, UserId, TimeStamp and Object
Accessed


As to the code, it could be done a couple of ways.
One would be an append query that would add a row to the audit log

c>
and the
other would be using a recordset.

Im not sure what a recordset is or how to use it

To call the function, you would put something like this in the Load event of
each form or report:
AuditLogger(Me.Name)
and in the Load event of your main form
AuditLogger("Logged In")
and in the Close event of your main form
AuditLogger("Logged Out")

I'm not sure how to call the function. What do I do to call it? Do I need
to go to Expression, Macro or Code builder or do I just type in
AuditLogger("Logged In") in the box next to on load.

Now the function (which must be in a standard module)
Public Function AuditLogger(strObject as String)
Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("tblAuditLog")
With rst
.AddNew
![User] = Me.UserId
![TimeStamp] = Now()
![OjbectAccessed] = strObject
.Update
End Wtih
Set rst = Nothing
End Function

I created a Module called AuditLogger with the code above.



Then in the Load event of each form and report, call the function and pass
it the information you want captured in your audit log.

Are you talking about the function you mentioned above? I'm assuming that
what you mean is on each form on my start-up screen I should write code to
the on load of the properties of the form that will refer to the function
created above and this will write to the audit log

In the Load event of every form and report
AuditLogger(Me.Name)
Will cause an entry to be written to the Log. It can also be used in a
Macro, except you will have to hard code the Macro's name in place of
Me.Name. Now, you could also track any other activity you want by adding a
second argument to the function.
AuditLogger(Me.Name, "Open") - Call in the Load event to know the object
was opened.
AuditLogger(Me.Name, "Close") - Call in the Close event to know the
object was closed.
Now let's say you want to record that a user exported data to Excel. You
could make calls in your VBA code to record that:
DoCmd.TransferSpread***....
AuditLogger("qselSomeData", "Exported")


Tracking tables and queries is not really necessary. If the app is
structured so that users cannot get directly to these objects (that is the
way it should be), you will know what data the user is working with from the
forms and reports they open.

And when the use closes the app, our famous invisible statup form's Close
event should capture that fact and update the audit table.

"Dan" wrote:

I need to track the log in and log out tie of users. I would like to store
this information in a table and be able to refer back to it, if needed. I
was also wondering if the log could track what forms, queries or table each
indivudal opened while online.
.