Add Audit Trail to Table Modification

[philmgron]

Hello

I have been hitting my head against the wall on this problem for a day
now. I have a simple table that stores cities, on of the fields on the
table is modified_by. I am trying to write the user who modifed the
table, in to the column modified_by. Code for getting the user that i
am using is environ("username"), it works fine; however for the life of
me i cannot figure out one which even to do this.

This is how the form is displayed

City:
modified_by:

What happens is that on Change event, i set focus to the modified_by
field and write the user name to it. Problem with that is that on
Change activates anytime i type one letter into the city field.

I also tried before update, change focus to modified_by field, however
it is as if the update never happens, when i try to advance the record
using navigation buttons.

I also tried dirty event; however, i just get run out of stack space
errors, any help or suggestions would be greatly appreicated.

THanks.

[Allen Browne]

Use the BeforeUpate event procedure of the *form* (not control) to assign
the value to the field.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ph*******@hotmail.com> wrote in message
news:11*********************@r2g2000cwb.googlegrou ps.com...

Hello

I have been hitting my head against the wall on this problem for a day
now. I have a simple table that stores cities, on of the fields on the
table is modified_by. I am trying to write the user who modifed the
table, in to the column modified_by. Code for getting the user that i
am using is environ("username"), it works fine; however for the life of
me i cannot figure out one which even to do this.

This is how the form is displayed

City:
modified_by:

What happens is that on Change event, i set focus to the modified_by
field and write the user name to it. Problem with that is that on
Change activates anytime i type one letter into the city field.

I also tried before update, change focus to modified_by field, however
it is as if the update never happens, when i try to advance the record
using navigation buttons.

I also tried dirty event; however, i just get run out of stack space
errors, any help or suggestions would be greatly appreicated.

THanks.

[philmgron]

Function getCurrentUser() As String
getCurrentUser = Environ("username")
End Function

Function runBeforeUpdate()
ModifiedBy.SetFocus
ModifiedBy.Text = getCurrentUser

End Function
Private Sub Form_BeforeUpdate(Cancel As Integer)
runBeforeUpdate
End Sub


This is my code here, i am using the beforeupdate on the form, the text
field in the form updates properly with the user who is modifing it,
but it the record does not advance when i use the record navigation
buttons to go to the next record. Thanks for your reply

[Allen Browne]

Drop the ".Text" bit, and you don't need to SetFocus.

Unlike pure Visual Basic, where Text is the default property of a text box,
Access is a data-centric program, so Value is the default property of a text
box. The Text property applies only while a control has focus, and refers to
the text in the control that has not yet been accepted as its Value.

So all you need is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.ModifiedBy = Environ("username")
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ph*******@hotmail.com> wrote in message
news:11**********************@p79g2000cwp.googlegr oups.com...

Function getCurrentUser() As String
getCurrentUser = Environ("username")
End Function

Function runBeforeUpdate()
ModifiedBy.SetFocus
ModifiedBy.Text = getCurrentUser

End Function
Private Sub Form_BeforeUpdate(Cancel As Integer)
runBeforeUpdate
End Sub


This is my code here, i am using the beforeupdate on the form, the text
field in the form updates properly with the user who is modifing it,
but it the record does not advance when i use the record navigation
buttons to go to the next record. Thanks for your reply

[philmgron]

Thanks alot worked like a charm:) you ve been a great help.

One other question if i may, what is the event on Dirty, as i have been
reading the forums and i think i have come to an understanding that it
means that the form has had a change made to it?

[Allen Browne]

The form's Dirty event fires when you *begin* to make a change to a record.
It does not exist in older versions of Access, and in some of the more
recent versions, the event does not fire if the record is dirtied
programmatically. It is therefore less than ideal for this kind of task.

The form's BeforeUpdate event fires at the last possible moment before the
record is saved. This event works consistently in all versions of Access. It
is most commonly used for record-level validation, but is also idea for this
kind of task. Particularly if you want to save the date and time of the last
edit, using the last possible moment before it is saved makes the best
sense.

It is possible to use the form events to write an audit trail of all
inserts, edits, and deletes. This involves using lots of the form events,
but if you ever need to do that, the details are here:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ph*******@hotmail.com> wrote in message
news:11**********************@f6g2000cwb.googlegro ups.com...

Thanks alot worked like a charm:) you ve been a great help.

One other question if i may, what is the event on Dirty, as i have been
reading the forums and i think i have come to an understanding that it
means that the form has had a change made to it?

[Ron2006]

You got it right....

That is what it means.

And if you set it to false, that causes Access to save the record.