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