Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003

    Exclamation 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 CheckForMissingValues
    'log it
    Call basLogTrans(Me, "PID", PID)
    End Sub

    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.

    The basLogTrans looks like this:

    ================================================== ==

    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"
    End If
    Call basAddHist(Hist, frm.Name, mykey.Name, MyCtrl)
    End If
    End If
    Next MyCtrl

    basLogTrans = True 'Let User know sucess

    End Function
    ================================================== ==

    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)

    With tblHistTable
    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
    End With

    End Function

  2. #2
    Join Date
    May 2005
    Nevada, USA
    Provided Answers: 6
    The syntax used to refer to it does change:

  3. #3
    Join Date
    Feb 2003

    sorry... I'm a little lost.

    Since I'm trying to reference the Before_Update method vs. a control, what option would I choose in this chart?
    I've tried playing around a bit with no success.
    tried the following:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
    'Call basLogTrans(Me, "PID", PID) ' standalone mode.
    Call basLogTrans(Forms!InclusionExam!frmIESection1.Form , "PID", PID)

    End Sub

    but... the name comes up empty.

  4. #4
    Join Date
    Feb 2004
    Chicago, IL
    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

    I haven't tested it, but I think that is right.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts