Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Tracking changes (XP 2K)

    I have written the following code as a generic change tracking system that can be attached to the before update of any form. Obviously there is a table called tblchanges in the database.
    This could definitely be improved. One idea is to check if the control has a bound column, and only then track its changes, but I could not figure that one out yet.
    Also, this is a one user database, you could add a field that would log who made the change.
    Any ideas or improvements would be appreciated.

    Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim ctl As Control
    Dim rst As ADODB.Recordset

    Set rst = New Recordset
    rst.Open "tblChanges", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

    On Error GoTo Form_BeforeUpdate_Error

    For Each ctl In Me.Controls

    If ctl.OldValue = ctl.Value Then

    Else

    If ctl.OldValue > "" Or ctl.Value > "" Then
    With rst

    .AddNew
    .Fields("TxtFormName") = Me.Name
    .Fields("txtControlName") = ctl.Name
    .Fields("intBookID") = Me.BookID
    .Fields("dtTime") = Now
    .Fields("txtOldValue") = ctl.OldValue
    .Fields("txtnewValue") = ctl.Value
    .Update
    End With
    End If





    End If

    Next ctl
    On Error GoTo 0
    rst.Close
    Set rst = Nothing
    Exit Sub

    Form_BeforeUpdate_Error:
    If Err.Number = 2427 Then ' this is the error number for something which does not have any data, and therefore does not have an "oldvalue".
    Resume Next
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_BeforeUpdate of VBA Document Form_frmBooks"
    End If
    End Sub

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Tracking changes (XP 2K)

    What do you expect to accomplish with this line?
    If ctl.OldValue > "" Or ctl.Value > "" Then

    To me it looks completely superfluous.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tracking changes (XP 2K)

    Hi Mark,

    I can't speak for Zave, of course, but I supect that the post as typed contained "not equals" < > (without a space in between) and that the < was "eaten" by a parser somewhere along the line.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tracking changes (XP 2K)

    You might use the following functions to check if a control is bound. They are modified from some Microsoft example; I forget where exactly I have taken them from.

    Function hasControlSource(ctl As Control) As Boolean
    ' Returns TRUE if the control has a ControlSource property value, or FALSE otherwise.
    Dim strTemp As String

    On Error Resume Next
    strTemp = ctl.ControlSource
    hasControlSource = (Err = 0)
    End Function

    Function isBound(ctl As Control) As Boolean
    ' Returns TRUE if the control is bound to a field, or FALSE otherwise.
    If hasControlSource(ctl) Then
    If Len(ctl.ControlSource) > 0 Then
    isBound = (Left(LTrim(ctl.ControlSource), 1) <> "=")
    End If
    End If
    End Function

    To get the (computer) user name, you can use

    Private Declare Function GetUserName Lib "advapi32.dll" _
    Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

    Function GetUser() As String
    Dim lpBuff As String * 255
    Dim ret As Long
    MsgBox GetUserName(lpBuff, 255)
    GetUser = Left(lpBuff, InStr(lpBuff, vbNullChar) - 1)
    End Function

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Tracking changes (XP 2K)

    Whether or not it should have been <>"" vs >"", I still think it's superfluous. There is already a check for .oldvalue=.newvalue; which means both values can't be ""; so why bother checking to make sure that at least of them isn't ""?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tracking changes (XP 2K)

    It wouldn't be superfluous to check for a null value though, although that can't be done like this with regular operators.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tracking changes (XP 2K)

    Don't forget that null values need special handling in comparison - do something like

    if nz(ctl.oldvalue)=nz(ctl.newvalue) ...

    One thing I did in my logging was have two tables with a one-many relation between them, so that stuff that was form-related (form name, who did it, when did they do it) wasn't repeated for every control.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tracking changes (XP 2K)

    thanks for all the input.
    Marty that line is because it was the only way that for some reason it did not log all the null values. I could not figure out why that worked, but it did.

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tracking changes (XP 2K)

    exactly.
    Thanks

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Tracking changes (XP 2K)

    >>It wouldn't be superfluous to check for a null value though, although that can't be done like this with regular operators.<<

    I was thinking that he was already handling nulls (although perhaps not intentionally). He had these lines:<pre> If .oldvalue = .new value then
    'nothing here
    Else
    'code here
    End if</pre>


    So, if either value was null, it would fail the test and the "Else" code would be executed.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tracking changes (XP 2K)

    You're right, Mark. That should provide for nulls, although I would <img src=/S/bash.gif border=0 alt=bash width=35 height=39> any programmer who worked for me and handled it implicitly like that. It's too easy to miss things when they aren't crystal clear.
    Charlotte

  12. #12
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Tracking changes (XP 2K)

    I know what you mean! I had to test that myself to make sure I was right that it would handle Nulls that way. I guess that it was the use of ELSE is why it worked (which implies 'Else another other condition'), whereas checking for a NOT TRUE would have failed if null was involved. Nulls are such fun!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  13. #13
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Tracking changes (XP 2K)

    Dear members of the board,
    doesn't anybody know any other existing add-ins, articles or white papers applying/discussing such functionality (tracking changes, additions and deletions)?
    It might be a useful addition to this thread...
    (I have a vague memory but unfortunately I can't trace where it comes from anymore.)
    Hasse

  14. #14
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Tracking changes (XP 2K)

    O-oh... I seem to have overlooked a thread. Here's the cross reference:

    Re: History or Find/Replace (Access 97 and 2000)
    Post: <post#=179291>post 179291</post#> re: 177328 from aspiemom
    I came across the following on "The Access Web" site, http://www.mvps.org/access/modules/mdl0021.htm (...)

    But all other tips remain welcome!

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Tracking changes (XP 2K)

    The contents of that thread are still accurate. The bottom line is that unless you decide to rewrite the Jet engine, it isn't capable of tracking changes when they are made at the table level. On the other hand the MSDE and SQL Server have that inate ability through the use of triggers.
    Wendell

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •