 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / June 2006Add Audit Trail to Table Modification
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. Use the BeforeUpate event procedure of the *form* (not control) to assign the value to the field.  SignatureAllen 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. > Hello > [quoted text clipped - 22 lines] > > THanks. 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 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 SignatureAllen 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. > Function getCurrentUser() As String > getCurrentUser = Environ("username") [quoted text clipped - 14 lines] > but it the record does not advance when i use the record navigation > buttons to go to the next record. Thanks for your reply 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? 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 SignatureAllen 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. > 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? You got it right.... That is what it means. And if you set it to false, that causes Access to save the record. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.