Problem with code for History Table

[Sandspirit]

I have this code that I received from RandyMyers in another post, but I keep getting an error message that the information is undefined. I have pasted it nearlt exactly as it was online. Please help. Yes, I am a newbie.

The module is:

Option Compare Database


Public Function basActiveCtrl(Ctl As Control) As Boolean
Select Case Ctl.ControlType
Case Is = acTextBox
basActiveCtrl = True
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 = 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)

Dim dbs As DAO.Database
Dim tblAuditTrail As DAO.Recordset

Set dbs = CurrentDb
Set tblAuditTrail = dbs.OpenRecordset(Hist, dbOpenDynaset, dbSeeChanges)

With tblAuditTrail
.AddNew
!MyKey = Forms(frm).Controls(MyKeyName)
!MyKeyName = MyKeyName
!frmName = frm
!FldName = MyCtrl.ControlSource
!dtChg = Now()
!UserId = Environ("Username")
!OldVal = MyCtrl.OldValue
!NewVal = MyCtrl
.Update
End With

End Function



Public Function basLogTrans(frm As Form, MyKeyName As Variant, MyKey As Variant) As String

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
If ((MyCtrl.Value <> MyCtrl.OldValue) _
Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then
Hist = "dbo_AuditTrail"
Call basAddHist(Hist, frm.NAME, MyKey.NAME, MyCtrl)
End If
End If
Next MyCtrl

basLogTrans = True

End Function


I pasted the call at the BeforeUpdate for the Form as:

Private Sub Form_BeforeUpdate(cancel As Integer)

Call basLogTrans(Me, "Key Filed Name", KeyField)


End Sub




When I go to make a change on the form and then press the close button I receive a compile error : User-defined type not defined.

Please help.

Thank You





--Sandspirit

 

[Golom]

Look at the field KeyField in your call to basLogTrans. The function is expecting an object that has a "Name" property.

You do reference MyKey[COLOR=red yellow].[/color]NAME. Is it possible that it should be MyKeyName (without the dot)? MyKeyName is currently an unreferenced calling argument in that function.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[Sandspirit]

This is where it is highlighted from the error message:



Public Function basLogTrans(frm As Form, MyKeyName As Variant, MyKey As Variant) As String

Dim MyDb As DAO.Database

---I tried changing the MyKey.name and the error persists. Is there something on the highlighted area that is not defined well?

Thanks!

-S-

--Sandspirit

 

[Golom]

You need a reference to Microsoft DAO 3.x Object Library

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]