Unanswered: Does the name of a form change when you embed it as a subform?
I've already posted this question a few days ago but didn't get a response. So sorry, but I'm reposting.
Basically, I have a function that tracks changes for each control on a form. To call it, I used to just do the following:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'fill in missing field values
Call basLogTrans(Me, "PID", PID)
The ME is the actual form. As an independant form everything works ok. But when I embed certain forms as subforms, this no longer works. I get the error message that the object doesn't exist.
Public Function basLogTrans(frm As Form, MyKeyName As Variant, mykey As Variant) As Boolean
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"
Hist = "tblHist"
Call basAddHist(Hist, frm.Name, mykey.Name, MyCtrl)
basLogTrans = True 'Let User know sucess
And the basAddHist method (which is where it's failing) looks like this:
Public Function basAddHist(Hist As String, frm As String, MyKeyName As String, MyCtrl As Control)
'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)
Debug.Print frm & " : " & MyKeyName
!mykey = Forms(frm).Controls(MyKeyName) *********
!MyKeyName = MyKeyName
!frmName = frm
!FldName = MyCtrl.ControlSource
!dtChg = Now()
!UserId = Environ("Username") 'Orlando's Way
'!UserId = CurrentUser() 'Mike's way, example of current user would be Admin
!OldVal = MyCtrl.OldValue
!NewVal = MyCtrl
When you put a subform on a form it becomes a control on the main form. I think you will need to check to see if a control is a subform and if so, start looping through its controls looking for changes.
It would be similar to your check for a Memo field. But then you would add another For Each loop. I think the syntax would be:
For Each SubfromCOntrol In frm.Controls(MyCtrl.Name).Form.Controls