RE: Tracking Log In and Log Out times of Users

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

It needs to be whatever the name of your table is.

"Dan" wrote:

I made the change and rename my Fucnciton "Logger" and my Module
"AuditLogger". Now I'm getting an error when I run my forms that say it
can't find my table "AuditLog". Then it goes to the debuger and hightlights
Set rst = CurrentDb.OpenRecordset("AuditLog") in the code you gave me for the
auditlogger module which is listed below. Even if I use tblAuditLog as you
did, which I assume is the name you gave the table you created, I get the
same message. I'm assuming that because I did not use tbl to distinquish my
table in its name I do not need to use the tbl in the code or is tlb part of
the code?

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


"Klatuu" wrote:

AuditLogger is a Sub, not a module. Modules conain Subs and Functions.
There are 3 types of modules.
A Form module is an object attached to a form object that contains code for
that form. It loads with the form.
A Report module is the same, except it is specific to that report.
A Standard module is not associated with any specific form or report, but is
a collection of Subs and Functions visible to any object. This is where you
put code you want to use throughout the application.

Now, there is one important point. A module cannot have a Sub or Function
in it that has the same name as the module. For example, you nave a Function
name AuditLogger; therefore, you can't name the module AuditLogger. It will
confuse Access.

"Dan" wrote:

Sorry but I'm just learning how to use modules.
On my startup form, (that opens every time the database is open, but is
visible) I went to design and clicked on upper left box of the design form
and click on properties for the form. I then went to the load event and
clicked on the 3 dots and chose code builder. When it the code builder comes
up I have "form" in the left box and "Load" in the right dropdown box with
the following in the lower pane

Private Sub Form_Load()

End Sub

I then put AuditLogger("Logged In") as follows

Private Sub Form_Load()
AuditLogger("Logged In")
End Sub

I also did the same for my Closed Event except I put AuditLogger('Logged Out")

Then on the form that everyone accesses for this database I did the same
thing. I open it in designe view clicked on the box in the upper left
corner and then went to properties for the form then to Load event and did
the same as above but used

AuditLogger(me.Name)

However I keep getting an error message that says that AuditLogger is not a
module. I'm I doing this right. I have a icon in my Modules for AuditLogger
with the code you had given me before. Thanks for the help, I'm just
learning so I'm sorry for the repeted questions.

"Klatuu" wrote:

You don't need to use Call. Call is for Subs, this is a function. Subs and
Functions are very similar, but there are some differences.

To repeat the instructions I posted earlier:
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.

Once you have selected Code Builder, the VBA editor will show with the sub
declaration already done. This is where you put the call to the function,
which is:

AuditLogger(Me.Name)

"Dan" wrote:

I sorry this is what I dont understand how to put the call to the function in
the event
procedure. I have never worked with modules before and this is all new to
me. I'm assuming that this a command line that goes like "Call AuditLogger"
then "end sub"


"Klatuu" wrote:

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.
.

Quantcast