History Tables 2

[MelissaKT]

I have found a wonderful FAQ written by Michael Red. This is exactly what I need to do. HOWEVER, it was written for bound forms. Mine are unbound. My question is: How could I rewrite this to use it on my unbound forms? And CAN it be rewritten so that I can use it? The faq is faq181-291:

The following three functions and table definitions are (should be) sufficient to establish a 'poor mans' transaction log for Ms. Access bound forms where Ms. A. Secutirt is implemented.  This code has been (BRIEFLY)tested Under Ms. A. ver 2K, but it relys on the DAO object model, so A reference needs to be in any the app to utilize it.  If there are any problems, please let me know.

As noted in basAddHist, you will need to create the tables for History (general fields) and Memo History (history of JUST memo fields).

ÿ
Public Function basLogTrans(Frm As Form, MyKeyName As Variant, MyKey As Variant) As Boolean

    'Michael Red    8/1/2002    Transaction log for Ms. Access BOUND forms.

    'The following three functions and table definitions are
    '(should be) sufficient to establish a 'poor mans' transaction
    'log for Ms. Access bound forms.

    'As noted in basAddHist, you will need to create the tables
    'for History (general fields) and Memo History (history of
    'JUST memo fields).

    'Also, note that these functions are designed to work in
    'conjunction with Ms. Access Bound forms where the view is
    'set to single form.  Further, the history will only capture
    'the user if the db App is "secured".

    'In the following, "MyKey" refers to the Record ID (Unique value)
    'for the single record which the form is 'tied' to.

    Dim MyDb As DAO.Database
    Dim MyCtrl As Control
    Dim MyMsg As String
    Dim Hist As String


    'In the Statement block below, basFlgValidRec is a routine which
    'includes validation of all fields necessary to 'define' a record
    'is valid.  Commented out here, as I do not know what validation
    'rules need to be checked/enforced.

'    If (Not basFlgValidRec) Then
'        basLogTrans = False            'Let User know of FAILURE!!
'        Exit Sub
'    End If


    For Each MyCtrl In Frm.Controls
        If (basActiveCtrl(MyCtrl)) Then             'Consider replacing w/ test for "ControlSource"
            If ((MyCtrl.Value <> MyCtrl.OldValue) _
               Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
               Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then
                    If (MyCtrl.ControlType = dbMemo) Then
                            Hist = "tblHistMemo"
                        Else
                            Hist = "tblHist"
                    End If
                    Call basAddHist(Hist, Frm.Name, MyKey.Name, MyCtrl)
            End If
        End If
    Next MyCtrl

    basLogTrans = True      'Let User know sucess

End Function
Public Function basActiveCtrl(Ctl As Control) As Boolean
    Select Case Ctl.ControlType
        Case Is = acLabel
        Case Is = acRectangle
        Case Is = acLine
        Case Is = acImage
        Case Is = acCommandButton
        Case Is = acOptionButton
        Case Is = acCheckBox
            basActiveCtrl = True
        Case Is = acOptionGroup
        Case Is = acBoundObjectFrame
        Case Is = acTextBox
            basActiveCtrl = True
        Case Is = acListBox
            basActiveCtrl = True
        Case Is = acComboBox
            basActiveCtrl = True
        Case Is = acSubform
        Case Is = acObjectFrame
        Case Is = acPageBreak
        Case Is = acPage
        Case Is = acCustomControl
        Case Is = acToggleButton
        Case Is = acTabCtl
    End Select
End Function
Public Function basAddHist(Hist As String, Frm As String, MyKeyName As String, MyCtrl As Control)

    'tblHist
    'FrmName        Text                80      Name of the form where change Occured
    'FldName        Text                80      Field Name of the changed value
    'dtChg          Date/Time           8       Date/Time of Change (MACHINE value!!)
    'OldVal         Text                255     Field Value BEFORE change
    'NewVal         Text                255     Field Value after change
    'UserId         Text                50      User who Made Change
    'MyKey          Variant             ??      KeyField as Indentified by Caller
    'MyKeyName      Text                80      'Key Field Contents

    'tblHistMemo is the same structure except the "type" for the fields
    'OldContents and NewContents are Memo (and therfore the length is "??")

    Dim dbs As DAO.Database
    Dim tblHistTable As DAO.Recordset

    Set dbs = CurrentDb
    Set tblHistTable = dbs.OpenRecordset(Hist, dbOpenDynaset)

    With tblHistTable
        .AddNew
            !MyKey = Forms(Frm).Controls(MyKeyName)
            !MyKeyName = MyKeyName
            !frmName = Frm
            !FldName = MyCtrl.ControlSource
            !dtChg = Now()
            !UserId = CurrentUser()
            !OldVal = MyCtrl.OldValue
            !NewVal = MyCtrl
        .Update
    End With

End Function

 

[Tarnish]

Hi Melissa,

Isn't a 'transaction log' of an unbound form just the same thing as binding the form to a table? If you set up your form to only allow additions (read: don't show navigation buttons and open to a new record...set the allow additions to yes and the others to no), I think that's exactly what you would have. I'm just guessing that you are going to input values in the form, then use the values you input to do some operation, and what you want to be able to do is to 'look back' at the values you used to do that operation after the fact. How is that different from just saving the values to a table as a record where the records can't be changed....only new records added?

I could be way off base though. I've used a similar thing by Allen Browne (for a bound form of course) but the idea is to be able to track changes made to a record. That means you need several entries per record (the 'current' one in the main table and one for every change made including deletions in an 'audit' table).

Maybe you could explain what you're trying to accomplish?

T

 

[MelissaKT]

I may have completely misunderstood what you said (it is Monday, after all! LOL) but I have an unbound form. When changes are made to that form and the save button is clicked, the values are written back to a table - tblPropertyInfo. Because I want to make sure that I can keep track of historical values, I want a history table to keep track of what changes were made and who made those changes. Without getting into it too much, I probably could bind the form to my table but I'd rather not if I can help it. I experimented each way (bound and unbound) and then decided that unbound was the best way for me.

The problem that I see is that I'm going to have to run the routine after update/before update of each control instead of on the after update /before update of the form. I'm not sure what kind of performance hit that would be?

By the way, I really appreciate your input!

 

[JoeAtWork]

Well, I can't agree with Tarnish that a bound add-only form is equivalent to creating an audit table. First of all, that could only work if there was only one record in the table. If you had 100 Customer records, and you open that form, which of the 100 are you "changing"? Secondly, in order to make it user-friendly you would want to pre-populate the fields of a new record with the values from the previous record (e.g. the user just wants to change the customer's middle name - you're not going to make him retype all the fields just for that one change, right?). So you're not saving youself much coding by using an add-only form.

Now, if I remember correctly, I don't think the OldValue property works with unbound controls (don't quote me on that though). So that prohibits Michael Red's function.

However, I think it shouldn't be hard to adapt what you already have. Since you are using an unbound form, that means you must be loading the data when you load the form (or whenever you navigate to a different record). Let's assume you open a recordset, and then populate the controls with values from the recordset. Now, instead of closing your recordset, just keep it open and handy.

When the user presses the "Save" button, do a field by field comparison between the values in the controls and the respective value in the recordset. For every one that's changed, make an entry in the audit table.

 

[MelissaKT]

Joe
That makes sense! That would also take care of the performance hit that I was worried about. Thanks a bunch!

 

[MichaelRed]

hmmmmmmmmmmmmmmmmmmmm ... mmmmmmmmmmmmmmmmmmm

mayhap you could try and tinker with an idea ...

since you are currently using an unbound form ... but loading it from a table ... you must be locating the record to load hmmmmmmmm ... you could have a 'temp' table with the same structure as your source recordset, to get the record, clear the temp table, use a simple append query from the original source to the temp table BIND the form to the temp table open/requery the form as ye may ... edit at will the code as is to document the changes.

IF you are interested in such an approach, be sure that you check that the form is not using any control types which are not enumerated (as you mentioned, the faq is a bit long in the tooth).




MichaelRed

 

[MelissaKT]

Boy, great minds think alike! LOL! That is kinda what I finally did. I didn't create a temp table. After working with comparing the two recordsets, I decided that it would just be easier to bind the form. This form starts out as a search form. They locate the property number that they are interested in, and then the record loads. I just bound the form to a stored procedure, bound the controls to the different fields and WAH-LAH....Bound form. Then I added in your code and it worked brilliantly!

This isn't the first one of your faqs that I've used. Thanks for all you do!

 

[MichaelRed]

thanks,

I appreciate the feedback - especially when it is positive although several have questioned wheather there even IS a mind here ...



MichaelRed