Tracking Changes In Database

[data808]

I just need something simple. I don't need a full on tracking system of all the changes. Right now I have it where the user logs in and then the log in form then becomes hidden. Then the user will go on to open the form to enter a record. This form will already be blank and ready for a new record. In other words, its on a new record when it opens. I have a field that has this as the default value:

=Mid([Forms]![Login]![txtUsername],InStrRev([Forms]![Login]![txtUsername],"_")+1)

What this does is it takes the last few letters that are after an underscore "_" in the username which is the clerk's initials. It takes this from the hidden login form and puts it into this field called Clerk Initials. So upon opening this record entry form, it is already set on a new record and since this Clerk Initials field is defaulted to take the username's initials and auto fill, it already has the clerks initials entered even before the clerk even types anything into this form.



I was wondering if anyone knew how to make it so that this Clerk Initials field pulls the username's initials ONLY IF the form is dirty. Meaning, only if the user types something in. I also would want it to update the Clerk Initials field on existing records if they are changed or updated. Meaning, if a user logs in then goes to an old record and changes something in that record which was created by another user, the Clerk Initials field would be updated to the most recent user who made the change. So I guess that would be on a before update event or something.

Any help would be appreciated. Thanks.

[June7]

Just moving to a new record row does not initiate a new record. Record is initiated by user entry into a control or by code populating a control. Setting DefaultValue property does not commit a record. Can navigate to previous record and new record will not be committed to table.

BeforeUpdate event could be appropriate.

[data808]

Thanks June7.

I figured out a way to do it. Thanks to your expression from a while back. I'm going to post the solution I used to help anyone that may be having this problem.

First, in the table, create the field that you want the username to transfer to from the login form when the user saves or edits a record. Then paste this code in each field of the form in the before update event:

Private Sub txtPermNum_BeforeUpdate(Cancel As Integer)

If Not IsNull(Me.txtPermNum) Then
Me.txtRecordedBy = [Forms]![Login]![txtUsername]
End If

End Sub

After any of the fields you paste this code in is updated, it will pass the username from the login form to the field called Recorded By. You also need to make the login in form hidden so it stays open for reference.