Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Milton keynes, UK.

    Unanswered: Referencing Sub form from function

    Hi All,

    First Post....

    I have used the following code successfully for recording the value of a form field. This does not work if the field is within a subform as it thinks the field is with


    instead of


    Can anyone help??

    Function History()

    Dim frmCurrent As Form
    Dim strFormName As String
    Dim X As Integer
    Dim MyControl As String
    Dim BUP As String
    Dim MyID As Long
    Dim AUP As String

    On Error GoTo errHandler:

    Set frmCurrent = Application.Screen.ActiveForm
    strFormName = frmCurrent.Name

    MyControl = frmCurrent.ActiveControl.Name ' Control Name
    On Error Resume Next
    BUP = frmCurrent.ActiveControl.OldValue ' ' Old Value
    On Error GoTo errHandler:
    AUP = frmCurrent.ActiveControl.Value ' ' NEW Value
    MyID = frmCurrent.RecordID ' Line Number
    DoCmd.OpenForm "frmHistory", acNormal, , , acFormAdd, acWindowNormal
    Forms!frmhistory!RecordID = MyID
    Forms!frmhistory!BUP = BUP
    Forms!frmhistory!changefield = MyControl
    Forms!frmhistory!AUP = AUP
    DoCmd.Close acForm, "frmHistory", acSaveYes

    Exit Function
    Select Case Err.Number
    Case 94
    Resume Next
    Case 438
    Resume Next
    Case Else
    msgbox Err.Number & vbCrLf & Err.Description
    End Select

    End Function

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Syntax for referencing controls on a subform:


    Welcome to the forum!
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jul 2006
    Milton keynes, UK.
    Thanks Missingling. Unfortunately this is not quite what I am after.

    I am trying to get the

    Field Name
    Old Value
    New Value

    from updated field which may be in the form or subform.

    The function below does work with subforms. I think I will need to
    Dim frmForm as form
    Dim frmSubform as subform

    but can not work out the requirements?

Posting Permissions

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