Who's in and who made changes

[Tara]

We have a database that has a custom-made login form. It works great, but I
want to take it a step further and make a log of who was in the database,
when and what changes they may have made. I'm not even sure how to begin
with this though. I assume I can do a timestamp for a sort of clock in/clock
out thing for tracking who was in and when and I can start playing around
with that now, but how do I track changes they made??

Any help is greatly appreciated!

 

[Tara]

So, now I'm at the point that I can retrieve - and save into a table - the
users ID and the time they opened the front-end db. But, I'm at a loss for
how to approach getting the time they close the front-end. The recordset
that is open when I first retrieve the user ID is closed as soon as the login
is complete so I'm unsure how to carry that user ID throughout the session.

 

[Bob Quintal]

We have a database that has a custom-made login form. It works
great, but I want to take it a step further and make a log of who
was in the database, when and what changes they may have made.
I'm not even sure how to begin with this though. I assume I can
do a timestamp for a sort of clock in/clock out thing for tracking
who was in and when and I can start playing around with that now,
but how do I track changes they made??

Any help is greatly appreciated!

Yes, one needs a Loggins table that stores the user's id and now() to
the UsierID and dtLogin, populated from an insert query triggered by
your login form. The login form must remain open until the database
closes, (you can make it invisible after the user is logged in) When
the database closes you update the record created above with now() in
the dtLogout field.

Logging changes uses the same idea, but gets much more complex.
You need a new table, that contains UserId, txtformName,
txtControlName, txtValueFrom, txtValueTo, dtChangeStamp.

In each form, you need to add code to each control's afterupdate
event that puts the above data into a array. You also have to add
code to each form's after update event to write the array as a series
of records to the new table. You must also check for the case where
the user backs out of the changes and does not save the form, and
clear the array when this happens.

You also should test that the changes are not on a new record, if you
use the same form for entry and editing.

Note that memo fields are a problem if they exceed the 255 character
limit of a text field, Using memo fields in the new table instead of
text fields creates as many or more problems than it solves.

 

[Tara]

Thanks for getting back with me Bob. I've now set the Login form to
invisible once the user logs in. Now, about running the code upon
closing...for some reason it's prompting me for the UserID. Could it be
because the form is not visible at the time?

 

[Damon Heron]

Here is the way I do it.
First, I have a global variable in my global module:
Public curUser As Long
On log in, (which is a combobox with usernames and IDs), I assign the userID
in column(0) to the variable:
[curUser] = Me.cboLogin.Column(0)
From the logon form, the program goes to an opening form. There I run the
sql statement:

strsql = "INSERT INTO tblLog ( User, LogonTime) values('" & [curUser] & "' ,
Now() )"
DoCmd.RunSQL (strsql)

On the exit button that leaves the program:

strsql = "INSERT INTO tblLog ( User, LogonTime) values('" & [curUser] & "' ,
Now() )"
DoCmd.RunSQL (strsql)

Damon

 

[Bob Quintal]

Thanks for getting back with me Bob. I've now set the Login form
to invisible once the user logs in. Now, about running the code
upon closing...for some reason it's prompting me for the UserID.
Could it be because the form is not visible at the time?

No, that's not the problem.It could be that your userID is in a
variable which is scoped only to the on_open sub, and not the whole
module, or that you are running your code too late in the process,
Access may have already cleared the textbox. Try moving the logout
code to the ON_Unload event.

 

[Tara]

I tried moving the code to the Unload event as you suggested. It's no longer
prompting me for the User ID, but it is inserting the TimeOut data into a
different record, not the record that has the TimeIn data. It inserts the
TimeOut data into a new record with no UserId at all.

I really appreciate your help with this. I'm pretty much a novice at
coding...

 

[Rick A.B.]

I tried moving the code to the Unload event as you suggested.  It's no longer
prompting me for the User ID, but it is inserting the TimeOut data into a
different record, not the record that has the TimeIn data.  It inserts the
TimeOut data into a new record with no UserId at all.

I really appreciate your help with this.  I'm pretty much a novice at
coding...

Tara,
If your just looking to track who makes changes to your data I would
suggest you check out Allen Browne's Audit log. You can find it here.

http://allenbrowne.com/AppAudit.html
Hope that helps
Rick

 

[Bob Quintal]

I tried moving the code to the Unload event as you suggested.
It's no longer prompting me for the User ID, but it is inserting
the TimeOut data into a different record, not the record that has
the TimeIn data. It inserts the TimeOut data into a new record
with no UserId at all.

I really appreciate your help with this. I'm pretty much a novice
at coding...

You need to change the query to an update query, and this update
query needs to be passed the criterai, which are the userId and
TimeOut is null. I'd create the query in code as follows (make
necessary corrections to table and field names)

Dim strSQL as String
strSQL =" UPDATE table_name " _
& " SET Timeout = #" & now() & "#" _
& " Where UserID = " & me.UserID _
& " AND Timeout is null;"

CurrentDB.Execute strSQL

I will get flames from some here who insist on adding dbFailOnError.
Tough. It's not desireable here.

 

[Tara]

I tried the solution you suggested Bob, but I'm still not having any luck.
The code didn't do anything at all. I'm sure I'm doing something wrong, but
I'm not sure what. So I decided to try something else. I'm using a
make-table query that runs when the user logs in to create a temporary place
to store the UserId, the TimeIn, and a blank field for TimeOut. Then I run
an update query when the user closes out of the application. It updates the
TimeOut field in that temporary table to Now(). Then, an append query
appends all the info in that table to tblAudit, which is a table shared by
all users. This is the only way I could think of to get the data I need.
I'm sure there's a much better way though and would love to hear any other
ideas you might have. In the meantime though, do you see any problems with
doing it the way I currently have it set up?

 

[Tara]

Thanks Rick. Tracking changes is the next step I need, so I'll be sure to
look at that link soon.

 

[Bob Quintal]

I tried the solution you suggested Bob, but I'm still not having
any luck. The code didn't do anything at all. I'm sure I'm doing
something wrong, but I'm not sure what. So I decided to try
something else. I'm using a make-table query that runs when the
user logs in to create a temporary place to store the UserId, the
TimeIn, and a blank field for TimeOut. Then I run an update query
when the user closes out of the application. It updates the
TimeOut field in that temporary table to Now(). Then, an append
query appends all the info in that table to tblAudit, which is a
table shared by all users. This is the only way I could think of
to get the data I need. I'm sure there's a much better way though
and would love to hear any other ideas you might have. In the
meantime though, do you see any problems with doing it the way I
currently have it set up?

Does each user have a separate copy of the front end .mdb locally as
opposed to a shared one on the network? A temp file in the shared
..mdb will give you problems when many people log in simultaneously.

You also may find that the make-table query leads to the database
growing in size if people are logging in and out frequently, but
other than that, if it works it's ok.

 

[Tara]

Yes, everyone has their own copy of the front end. Is there anything I can
do to avoid the db growing too much?

Thanks so much for all your feedback and help with this!

 

[Tony Toews [MVP]]

We have a database that has a custom-made login form. It works great, but I
want to take it a step further and make a log of who was in the database,
when and what changes they may have made. I'm not even sure how to begin
with this though. I assume I can do a timestamp for a sort of clock in/clock
out thing for tracking who was in and when and I can start playing around
with that now, but how do I track changes they made??

I also log the msaccess.exe and msjet40.dll version. Helps with
troubleshooting. See the Verify Appropriate Jet Service Pack is
installed page at my website for more details including sample code:
www.granite.ab.ca\access\verifyjetsp.htm

Tony