422,530 Members | 1,051 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,530 IT Pros & Developers. It's quick & easy.

Audit Trail That Captures Change To Null

P: 82
Hi, I found code on the Internet for creating an audit log. Works great except it does not record if you edit a value to become blank. I tried a few things and can't get it to work. Can anyone help out? Thanks in advance.
Expand|Select|Wrap|Line Numbers
  1. Sub AuditChanges(IDField As String, UserAction As String)
  2.     On Error GoTo AuditChanges_Err
  3.     Dim cnn As ADODB.Connection
  4.     Dim rst As ADODB.Recordset
  5.     Dim ctl As Control
  6.     Dim datTimeCheck As Date
  7.     Dim strUserID As String
  8.     Set cnn = CurrentProject.Connection
  9.     Set rst = New ADODB.Recordset
  10.     rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
  11.     datTimeCheck = Now()
  12.     strUserID = Environ("USERNAME")
  13.     Select Case UserAction
  14.         Case "EDIT"
  15.             For Each ctl In Screen.ActiveForm.Controls
  16.                 If ctl.Tag = "Audit" Then
  17.                     If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
  18.                         With rst
  19.                             .AddNew
  20.                             ![DateTime] = datTimeCheck
  21.                             ![UserName] = strUserID
  22.                             ![FormName] = Screen.ActiveForm.Name
  23.                             ![Action] = UserAction
  24.                             ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
  25.                             ![FieldName] = ctl.ControlSource
  26.                             ![OldValue] = ctl.OldValue
  27.                             ![NewValue] = ctl.Value
  28.                             .Update
  29.                         End With
  30.                     End If
  31.                 End If
  32.             Next ctl
  33.         Case Else
  34.             With rst
  35.                 .AddNew
  36.                 ![DateTime] = datTimeCheck
  37.                 ![UserName] = strUserID
  38.                 ![FormName] = Screen.ActiveForm.Name
  39.                 ![Action] = UserAction
  40.                 ![RecordID] = Screen.ActiveForm.Controls(IDField).Value
  41.                 .Update
  42.             End With
  43.     End Select
  44. AuditChanges_Exit:
  45.     On Error Resume Next
  46.     rst.Close
  47.     cnn.Close
  48.     Set rst = Nothing
  49.     Set cnn = Nothing
  50.     Exit Sub
  51. AuditChanges_Err:
  52.     MsgBox Err.Description, vbCritical, "ERROR!"
  53.     Resume AuditChanges_Exit
  54. End Sub 
Aug 14 '13 #1
Share this Question
Share on Google+
19 Replies

Expert 100+
P: 1,218
This code looks pretty good. You don't really tell us anything about the nature of the problem. Does it fail to make an audit record when you change any field at all to be blank? The problem is presumably occurring at
Expand|Select|Wrap|Line Numbers
  1. If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
but we don't have enough information to say that for sure.

Do you know how to run debug in the code? If you go to that line and hit the F9 key you'll set a debug stop. Then run the code and when that line is to be executed the debugger will come up (current line highlighted in yellow). You can then get variable values by pointing at a variable or in the immediate window (control/g) - type ?variablename to get the value. F8 to move to the next line in the code (or F10, depending on what version you're in). Use the Debug menu to guide you and look at debugging in Help. Educate yourself about debug, try a few things and come back with some more specific questions.

Aug 14 '13 #2

Expert Mod 5K+
P: 5,056
I recommend against using the [F9] stop point.
It has been documented to cause issues with the compiler.
Instead, insert the STOP command in-between lines 7 and 8 (just before the first SET command).
From there proceed as jimatqsi has suggested.

Please take a look at the trouble shooting section here:
> Before Posting (VBA or SQL) Code

AND take a look at the trouble shooting articles in the Microsoft Access / VBA Insights Sitemap listing (open the page and then use <ctrl><f> to open the page search to find "debugging in vba" or look for 129 thru 139...
Aug 14 '13 #3

P: 82

The code works fine when I change any field from "Value 1" to "Value 2". The change gets recorded properly in the audit table. If I change "Value 2" to be null (blank), the change is not recorded in the table. I believe it is the line jimatqsi suggested and I have tried
Expand|Select|Wrap|Line Numbers
  1. If Nz(ctl.Value, "") <> Nz(ctl.OldValue, "") Then 
but that didn't do anything. I'm having a hard time doing anything with debug as it won't let me run the code for some reason. Any other thoughts?
Aug 14 '13 #4

Expert Mod 5K+
P: 5,056
How are you calling the code?
Aug 14 '13 #5

Expert 100+
P: 1,218
What do you mean by "it won't let me run the code "? Are you able to set the stop point with F9 (line of code turns brown)? Then when you run the code (open the form if it's not open, make a change to a field, do whatever event is required to call Sub AuditChanges) it should start the process but stop with the line highlighted in yellow. At what point are you prevented from running your code?

Aug 14 '13 #6

P: 82
I can make the stop point and have done so. I then keep the code open but minized and on the form, remove a value from a field and save the record. I don't see anything that is happening with the code. Sorry but I am self taught.
Aug 14 '13 #7

Expert 100+
P: 1,218
Perhaps if you switch windows to the code you'll see it is waiting at a yellow highlighted line. Or else your Sub is not being called in this instance.

If the program is not sitting at the break point then we have to find out what calls your auditing logic. If you can tell us that (preferably post the code that calls AuditChanges). It could be from within the code running when you save a record.

Do the reading that zmbd listed for you. You'll learn a lot and be glad for it.

Aug 14 '13 #8

P: 82
When I try what you suggested, I do not see a yellow line in the code. The code is called from the BeforeUpdate Eventof the form.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.    Call AuditChanges("StuffID", "EDIT")
  3. End Sub
I'll be reading that tonight and really appreciate all that I learn here.
Aug 14 '13 #9

Expert Mod 5K+
P: 5,056

1) Before you do anything else to your project, please, please, please, please complete all of the trouble shooting and configuration steps given in: > Before Posting (VBA or SQL) Code. If you will not do this, then we will be chasing our tails for a 100 posts!

2) Please do !not! use the [F9] method of setting the stop/break point. I understand that many people do; however, it somtimes fails and if certain conditions are met, the VBA code can be corrupted. If you have used this method, please go back into your code and clear any of those "red" dots.
2a) Place the STOP command as the very FIRST command in your "Form_BeforeUpdate" so that you get something like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. '>> Debug Stop
  3.    STOP
  4. '>>
  5.    Call AuditChanges("StuffID", "EDIT")
  6. End Sub
if this does not cause the code to enter the debug mode and highlight the call to the AuditChanges then we need to start checking your program settings... once again, if you will follow all of the provisions in the > Before Posting (VBA or SQL) Code link for trouble shooting most of this will fix itself.
Aug 14 '13 #10

P: 82
Hi. Sorryy if I seem like a dolt. I read all on the link and then added a stop command. When I saved my edit on the record, debug kicked in and highlighted stop. The code works fine except it will not record deleting a value in a field that previously had data. The only changes the code keeps are when you replace one value with another value that is not blank. Thanks and sorry but I am trying my best.
Aug 14 '13 #11

Expert Mod 5K+
P: 5,056
YEA... we're in the debug mode!
You are not a dolt.
You are a puppet with broken strings and a half crazy puppeter trying to crack a walnut using a feather. }:)

in the code that is supposed to be recording your changes, lets take a look and see at what is happening:

Startin at Line 15 your current code reads as:
Expand|Select|Wrap|Line Numbers
  1. For Each ctl In Screen.ActiveForm.Controls 
  2.                 If ctl.Tag = "Audit" Then 
  3.                     If Nz(ctl.Value) <> Nz(ctl.OldValue) Then 
  4.                         With rst 
Let's insert a debug print to see what is happening:

Expand|Select|Wrap|Line Numbers
  1. For Each ctl In Screen.ActiveForm.Controls 
  2.                 If ctl.Tag = "Audit" Then 
  3. '>>> Debug Code>>>
  4. debug.print ctl.name, nz(ctl.value,"null"), nz(ctl.oldvalue,"null")
  5. '<<< Debug Code<<<
  6.                     If Nz(ctl.Value) <> Nz(ctl.OldValue) Then 
  7.                         With rst 
now go ahead and run this code in the debug mode.
What I suspect is that your controls are returning a null value in the case that you are explaining for the current value, and for some reason the old value is also null. We should be able to confirm this in the debug window when you press <ctrl><g> to open it (if not already open).

Please don't forget to do the debug steps as outlined in the link in my last post :)
Aug 14 '13 #12

P: 82
Hi. I really appreciate the help and the fasst response. You will think I am a morong but nothing happens. The form has a field called PracticeName. The current value is: Freedom. I remove Freedom then save the record. It does not get recorded. If I reopen the form and go to the same record and change Freedom to Freeless is does get recorded. This is a split access sql DB so is the Nz what is causing the problem perhaps? Thanks again.
Aug 14 '13 #13

P: 82
That was it! I removed the Nz and no problems. Thanks for all the help.
Aug 14 '13 #14

P: 82
Nope, spoke too soon. Still not working.
Aug 14 '13 #15

Expert Mod 5K+
P: 5,056
I didn't ask you to remove the NZ() function.
Did you insert the debug print as I did ask?
If so, what was the result?
Aug 14 '13 #16

P: 82
I did what you suggested and nothing happened but I got it to work. Thanks for your suggestions and your time.
Aug 15 '13 #17

Expert Mod 5K+
P: 5,056
That is very strange that nothing was printed in the immediate window. Did you press <ctrl><g> to open the immediate window up?

Also please post back how you fixed the issue so that others may benefit from your efforts.
Aug 15 '13 #18

P: 1
Bumping in case anyone still has this issue.

The below works for me:
Expand|Select|Wrap|Line Numbers
  1. If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
3 Days Ago #19

Expert Mod 15k+
P: 30,680
Responding positively to old threads is perfectly acceptable J Rober. It's only extra questions that aren't allowed - and they never are anyway :-)
3 Days Ago #20

Post your reply

Sign in to post your reply or Sign up for a free account.