INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Choosing the Correct Forum

Transaction Log for Ms. Access by MichaelRed
Posted: 20 Nov 00 (Edited 26 Nov 02)

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

Back to Microsoft: Access Other topics FAQ Index
Back to Microsoft: Access Other topics Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close