Transaction / history tables

[scorpio1769]

I'm attempting to insert Michael Red's transaction code, faq181-291, and forgive my ignorance, but how do I initiate this code? I've created the tables as described, pasted the code into the db, set the DAO reference as describerd and compiled to make sure no errors.

What event do I tie this to? Form after update? Each field on the form before update?

Thanks,

 

[MichaelRed]

I generally called this from the OnDirty event of the controls.




MichaelRed

 

[PHV]

Or simply the BeforeUpdate event of the form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[scorpio1769]

Thanks for the heads. I don't know VB at all and am trying to figure out how to call the functions. Do I create a sub on the before update and then call Michael's functions from within it as below? Any tips on how do this? Sorry to be "that guy"....just have no idea what I'm doing with VB.

Ever so humble thanks.

Private Sub Form_BeforeUpdate(Cancel As Integer)

'Do I need to call basActiveCtrl
'This is where I need to call basLogTrans?


End Sub

Option Compare Database

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

'Also, note that these functions are designed to work in
'conjunction with Ms. Access Bo und 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


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
'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

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

 

[scorpio1769]

I'm attempting to call the function like this:


Private Sub Form_BeforeUpdate(Cancel As Integer)

Call basLogTrans(Frm, MyKeyName, MyKey)

End Sub

When I do, I get a type mismatch at Frm in the parameter list. Can someone let me know what I'm doing wrong?

Many thanks.

 

[MichaelRed]

what do the arguments resolve to?




MichaelRed

 

[scorpio1769]

Not sure. How do I know?

 

[CaptainD]

Private Sub Form_BeforeUpdate(Cancel As Integer)

Call basLogTrans(Frm, MyKeyName, MyKey)

End Sub

Is the name of your form "Frm"?
Is the value of "MyKeyName" the true value?
Is the value of "MyKey" the true value?

The point being, when you call "BasLogTrans" you are supposed to pass the information to the function, so if your Form's name is frmMain then you would pass:

Call BasLogTrans(frmMain, MyKeyName, MyKey)

and of course the same for MyKeyName and MyKey.

 

[scorpio1769]

Ahhhh. I get it now. Thanks for your help and patience Michael. The transaction history works beautifully.