Tracking User Changes

B

BARKAROO

I have a database where I need to be able to track the last user that
modified a record. I don't need to know what specific info was modified,
just which user did it.
I tried following Allen Browne's "Creating an Audit Lot" page, however, I
was unsuccessful. (Because I am inexperienced with VBA, and because, the
procedure gets further complicated, almost impossible, when using tab
controls on a form.) My Database uses a form, with Tab controls for entering
and editing data for each record in the database.

Does anyone know of some simple way to tell which user last modified a
record? Any assistance would be greatly appreciated.

Barkaroo
 
R

Rick B

I would simply add an event in the Before Update of my form to place the
UserID and current Date/Time in a locked or hidden timestamp field.

Rick B
 
B

BARKAROO

Rick,

This sounds like it could work for me... In your scenario, would the data be
tracked in a separate table, or would it just "show" in the field?... If it
were to just "show" in the field, when I entered the form, it would take my
username and delete the one I want to view??? Please elaborate.

Thanks for the quick response! AWESOME! Much appreciated.
 
R

Rick B

It would not store it in a different table. You would add a "last changed"
field to your existing table. By putting your code in the "before update"
event, I believe it would only change the timestamp if you modify the
record. Viewing the record would not affect your timestamp. You may want
to go to the form property, place your cursor in the "before update" event
and read the status bar text. It will tell you when that event fires. The
code would really just be one line...


Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!timestamp = CurrentUser() & Now()
End Sub




Hope that helps,
Rick B
 
B

BARKAROO

Rick,

I NEED HELP!
Please bear with me, I'm new to all of this... in my "before update" event,
I already have a procedure to "remind" the users to save they're information
(just to be safe):

This procedure checks to see if the data on the form has
' changed. If the data has changed, the procedure prompts the
' user to continue the save operation or cancel it. Then the
' action that triggered the BeforeUpdate event is completed.

On Error GoTo Err_BeforeUpdate

' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
Me.Undo
End If
End If

Exit_BeforeUpdate:
Exit Sub

Err_BeforeUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub

As I mentioned, I'm new to this... Can I add the event (showing last user
etc...) in this as well? If so, do I add it before or after the existing code?

I've also added the "LastChanged" field to the table as you suggested.
Where do I go from here?
[I have two very large books in front of me.. they don't seem to be helping
much. LOL]

Thanks again for all your help.
 
B

BARKAROO

Rick,

One more thing i'm confused about... If I add the field to the table, and
then I add the add the event to the "before update" of my form, how does it
know to populate the field? I know, this must sound so trivial to you.
However, I'm trying very desperatly to figure this out. I really appreciate
your help.

Thanks.

Bark

BARKAROO said:
Rick,

I NEED HELP!
Please bear with me, I'm new to all of this... in my "before update" event,
I already have a procedure to "remind" the users to save they're information
(just to be safe):

This procedure checks to see if the data on the form has
' changed. If the data has changed, the procedure prompts the
' user to continue the save operation or cancel it. Then the
' action that triggered the BeforeUpdate event is completed.

On Error GoTo Err_BeforeUpdate

' The Dirty property is True if the record has been changed.
If Me.Dirty Then
' Prompt to confirm the save operation.
If MsgBox("Do you want to save?", vbYesNo + vbQuestion, _
"Save Record") = vbNo Then
Me.Undo
End If
End If

Exit_BeforeUpdate:
Exit Sub

Err_BeforeUpdate:
MsgBox Err.Number & " " & Err.Description
Resume Exit_BeforeUpdate
End Sub

As I mentioned, I'm new to this... Can I add the event (showing last user
etc...) in this as well? If so, do I add it before or after the existing code?

I've also added the "LastChanged" field to the table as you suggested.
Where do I go from here?
[I have two very large books in front of me.. they don't seem to be helping
much. LOL]

Thanks again for all your help.
Rick B said:
It would not store it in a different table. You would add a "last changed"
field to your existing table. By putting your code in the "before update"
event, I believe it would only change the timestamp if you modify the
record. Viewing the record would not affect your timestamp. You may want
to go to the form property, place your cursor in the "before update" event
and read the status bar text. It will tell you when that event fires. The
code would really just be one line...


Private Sub Form_BeforeUpdate(Cancel As Integer)
Me!timestamp = CurrentUser() & Now()
End Sub




Hope that helps,
Rick B
Click to expand...
Click to expand...
 
C

cnguyen

Rick,

I want to be able to track who made the changes and what changes were made.
Is there a way to do this?
 
Top