Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Provided Answers: 5

    Unanswered: Universal code to update underlying table subform

    I want to create a module that will rool through the controls on a subform and do a rst.update after ading the control data to the underlying table. the control names on the form match the fieldnames they are bound to

    I am so close to having this

    Public Function duplicatemydata(tblname As Variant)

    Dim ctl As Control, fld As Field, rst As Recordset, fldName, fsub As Subform
    'Set fsub.Name = "managereporttemplates_imagesub"

    Set rst = CurrentDb.OpenRecordset("select * from " & tblname, dbOpenDynaset, dbSeeChanges)
    For Each fsub. In Me.Form ****Error out here
    If fsub.Name = "managereporttemplates_imagesub" Then
    For Each ctl In Me.managereporttemplates_imagesub
    fldName = ctl.Name
    If fldName = "ID" Or Left(ctl.Name, 5) = "Label" Then
    rst(fldName) = ctl.VALUE
    End If
    exit function
    end if
    Next fsub
    End Function

    This would be a great adddition too anybodys library - and my deadline is today for completing the module (this is the last procedure)

    The only other acceptable workaround would be code to duplicate the record in the subform in the same table (recordset)

    Geo , PK , Trekker - I need a boost here
    Dale Houston, TX

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    There is no Form collection representing the subforms of a form, this is why the line "For Each fsub..." produces an error. Here is an example showing how you can explore the controls of each subform on a form:
    Function ExploreSubForms()
        Dim ctlParent As Control
        Dim ctlChild As Control
        Dim frm As Form
        For Each ctlParent In Me.Controls
            If ctlParent.ControlType = acSubform Then
                Set frm = Me(ctlParent.Name).Form
                For Each ctlChild In frm.Controls
                    Debug.Print ctlChild.Name
            End If
    End Function
    Have a nice day!

Posting Permissions

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