Solved Log what field was changed on a form (1 Viewer)

[Number11]

So i need to log what a user changed within a form, I have the date stamp and user stamp working, but would like to just record what field was updated/amended or changed is this possible

Address Line 1
Address Line 2
Address L:ine 3
Town
Post code
Telephone No
Account Number

ect

 

[Gasman]

See if the code here helps

http://www.databasedev.co.uk/audit_trail.html

 

[Isaac]

Check out the idea of testing for ControlName.OldValue vs. Controlname.Value, in the form's BeforeUpdate event.

 

[The_Doc_Man]

Just adding to the comments of Gasman and Isaac, search this forum for "Auditing" to see dozens of threads on the subject. That is the name of the feature you seek.

Sometimes our relatively new users aren't bad programmers at all, but they don't know the correct name of the feature they would like to research. If you know the right name, it becomes easy to find. So your keyword is "Auditing."

 

[Number11]

So i am using this method...

EDITED BY THE_DOC_MAN to add code tags. NO OTHER EDITS PERFORMED.

Function WriteChanges()

Dim f As Form
Dim c As Control
Dim frm As String
Dim user As String
Dim sql As String
Dim changes As String
Dim db As DAO.Database

Set f = Screen.ActiveForm
Set db = CurrentDb

frm = Screen.ActiveForm.Name
user = Application.CurrentUser
changes = ""

   sql = "INSERT INTO AuditTrail " & _
         "([FormName], [User], [ChangesMade]) " & _
         "VALUES ('" & frm & "', '" & user & "', "

    For Each c In f.Controls

      Select Case c.ControlType
          Case acTextBox, acComboBox, acListBox, acOptionGroup
               If IsNull(c.OldValue) And Not IsNull(c.Value) Then
                  changes = changes & _
                  c.Name & "--" & "BLANK" & "--" & c.Value & _
                  vbCrLf
               ElseIf IsNull(c.Value) And Not IsNull(c.OldValue) Then
                  changes = changes & _
                  c.Name & "--" & c.OldValue & "--" & "BLANK" & _
                  vbCrLf
               ElseIf c.Value <> c.OldValue Then
                  changes = changes & _
                  c.Name & "--" & c.OldValue & "--" & c.Value & _
                  vbCrLf
               End If
      End Select

    Next c

   sql = sql & "'" & changes & "');"

db.Execute sql, dbFailOnError

Set f = Nothing
Set db = Nothing

End Function

however this is only recording the last field change made not all the fields updated any ideas?[/CODE]

 

[Isaac]

What type of control is it, the one that's being recorded?

 

[The_Doc_Man]

"Last field change" meaning what? Last changed field in the list? Only the most recent changes? Something else?

Also, when/where/how are you calling this? Calling it from the wrong place or the wrong time could have that effect.

Finally, one of your IF/ELSEIF steps can't happen (I think). I don't believe it is possible under normal circumstances that .OldValue would not be null but the new .Value would be. Now, it might be 0 or "" (zero-length string) - but I don't recall that a form would normally drop a NULL as a new value.

@Isaac - the code is a little hard to read because of indentation issues, but he has a "for each c in frm.controls" so ... all of them.

 

[Isaac]

"Last field change" meaning what? Last changed field in the list? Only the most recent changes? Something else?

Also, when/where/how are you calling this? Calling it from the wrong place or the wrong time could have that effect.

Finally, one of your IF/ELSEIF steps can't happen (I think). I don't believe it is possible under normal circumstances that .OldValue would not be null but the new .Value would be. Now, it might be 0 or "" (zero-length string) - but I don't recall that a form would normally drop a NULL as a new value.

@Isaac - the code is a little hard to read because of indentation issues, but he has a "for each c in frm.controls" so ... all of them.

I meant they said "however this is only recording the last field change made"

I agree, it's awful to read code without code tags and indentation. Have no idea why people do that to themselves!

@Number11 what event do you have this in? Post code please

@The_Doc_Man

Finally, one of your IF/ELSEIF steps can't happen (I think). I don't believe it is possible under normal circumstances that .OldValue would not be null but the new .Value would be. Now, it might be 0 or "" (zero-length string) - but I don't recall that a form would normally drop a NULL as a new value.

Maybe a date field? They can't be ZLS I don't think?

 

[The_Doc_Man]

Separate post to discuss a separate issue.

When you call this, you go through a ton of gyrations to fix a problem that only should take one line. The gyrations you go through are not always guaranteed to give you the right form if your app is not maximized and other app could theoretically be active momentarily.

Make the call a subroutine: WriteChanges(f as string) and skip all of those steps to figure out the form name.
EDIT: Make it a routine in a general module as a Public Sub.

Then where you call it, use WriteChanges(me.name) to pass in the name of the form.

Then call it from the Form_BeforeUpdate event routine on each form that you wanted to audit.

 

[The_Doc_Man]

@Isaac - The code WAS indented - but the problem was the lack of code tags. I fixed that.

 

[Isaac]

@Isaac - The code WAS indented - but the problem was the lack of code tags. I fixed that.

Oh! Ok, I take half of it back then. :|

 

[Number11]

Separate post to discuss a separate issue.

When you call this, you go through a ton of gyrations to fix a problem that only should take one line. The gyrations you go through are not always guaranteed to give you the right form if your app is not maximized and other app could theoretically be active momentarily.

Make the call a subroutine: WriteChanges(f as string) and skip all of those steps to figure out the form name.
EDIT: Make it a routine in a general module as a Public Sub.

Then where you call it, use WriteChanges(me.name) to pass in the name of the form.

Then call it from the Form_BeforeUpdate event routine on each form that you wanted to audit.

So i have the code to call the function "=WriteChanges()" called already on the form BeforeUpdate.


SO Lets say i updated address line 1, and then address line 2 and finally Postcode the autotrail is only capturing the first change made which was Address Line 1

 

[Isaac]

So i have the code to call the function "=WriteChanges()" called already on the form BeforeUpdate.


SO Lets say i updated address line 1, and then address line 2 and finally Postcode the autotrail is only capturing the first change made which was Address Line 1

Doesn't make sense to me. Can you post a copy.

 

[Number11]

Doesn't make sense to me. Can you post a copy.

Function WriteChanges()

Dim f As Form
Dim c As Control
Dim frm As String
Dim user As String
Dim sql As String
Dim changes As String
Dim db As DAO.Database

Set f = Screen.ActiveForm
Set db = CurrentDb

frm = Screen.ActiveForm.Name
user = get_user()
Record = Screen.ActiveForm.ID
Account = Screen.ActiveForm.[Account No]
changes = ""

   sql = "INSERT INTO AuditTrail " & _
         "([Record],[Account],[FormName],[User], [ChangesMade]) " & _
         "VALUES ('" & Record & "','" & Account & "','" & frm & "', '" & user & "', "
      
    For Each c In f.Controls

      Select Case c.ControlType
          Case acTextBox, acComboBox, acListBox, acOptionGroup
               If IsNull(c.OldValue) And Not IsNull(c.value) Then
                  changes = changes & _
                  c.Name & "--" & "BLANK" & "--" & c.value & _
                  vbCrLf
               ElseIf IsNull(c.value) And Not IsNull(c.OldValue) Then
                  changes = changes & _
                  c.Name & "--" & c.OldValue & "--" & "BLANK" & _
                  vbCrLf
               ElseIf c.value <> c.OldValue Then
                  changes = changes & _
                  c.Name & "--" & c.OldValue & "--" & c.value & _
                  vbCrLf
               End If
      End Select

    Next c

   sql = sql & "'" & changes & "');"

db.Execute sql, dbFailOnError

Set f = Nothing
Set db = Nothing

End Function

 

[Isaac]

Can you post copy of db? just leave enough to make this form work?

 

[Gasman]

Time to start walking through the code with F8 ?

 

[isladogs]

See if the code here helps

http://www.databasedev.co.uk/audit_trail.html

Just in case anyone cares, the author of that link article written back in 2011 was YKW.

 

[Isaac]

@Number11

Please test this:

Open your AuditTrail table. Once open in datasheet mode, adjust the Row Height of the records.
Check out the values.

 

[The_Doc_Man]

Is there a chance that the field that IS posted successfully contained either an unbalanced right parenthesis or a quote mark? Do any of the field names contain special characters?

I'm with Gasman. I don't see anything leaping out at me in the code. It is time to set up a test exactly as you described with three fields changed. Set a breakpoint on the SELECT CASE statement, then single-step via F8 to see what happens. If necessary, have a second breakpoint where you concatenate the "changes" variable into your SQL statement. Do a DEBUG.PRINT of either the SQL statement or CHANGES just after the concatenation and before the DB.EXECUTE. For the case that doesn't work you need to see in detail what it is doing because to be honest, that code should work OK.

Just a nit-pick and this is more for completeness than programming necessity, but you SHOULD have a CASE ELSE with no actions under it, placed just before the END SELECT. It is a matter of style as a visual way for you to - at some indefinite point in the future - come back to the code and see that you intentionally did nothing else to any of the other control-type cases. The code should work fine without it. But with it, you get a couple of extra style points.

 

[The_Doc_Man]

Another style pointer... why did you choose to make this a function? There are only two times when you need to make something a function. First, when you intended to return a value - but you don't return a value here. Second, if you intended to run this in MACRO context, because a MACRO requires a function for its RunCode entry-point name. Any other time, what you wrote might as well be a SUB.