Transaction Log for Ms. Access

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
code]