Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Identifying a control as a s ubform in VBA

    I have created an analysis tool that among other things iterates through all of the forms and records the recordsource.

    I also bubble-up the control_type (rectangle, label, commandbutton) but i cannot seem to differentiate when a control is a subform/subreport.

    does anyone have a snippet of code to roll through the controls and if it is a subform - give me the object name? (not what the user named the control but the name of the actual control in the navigation pane that the user is using as a subform)

    Below is the code I am using

    On Error Resume Next
    Dim ctl As Control
    Dim frm As Form
    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("select * from tblFormData", dbOpenDynaset)
    CurrentDb.Execute "delete * from tblformdata"

    Dim AccObj As AccessObject
    Dim AppObj As Object

    Set AppObj = Application.CurrentProject
    For Each AccObj In AppObj.AllForms
    If AccObj.Name <> "frmtool" Then 'name of the form i am using - do not want to error here on my open form
    DoCmd.OpenForm (AccObj.Name), acDesign
    rst.AddNew
    rst!frmname = AccObj.Name
    rst!FrmRecordSource = Forms(AccObj.Name).RecordSource
    rst.Update
    For Each ctl In Forms(AccObj.Name)
    ' Debug.Print TypeName(ctl)
    If TypeName(ctl) = "Subform/subreport" Then
    rst.AddNew
    rst!frmSubForm = ctl.Name
    rst!frmname = AccObj.Name
    rst.Update
    End If
    Next ctl
    End If
    DoCmd.Close acForm, AccObj.Name
    Next AccObj
    CurrentDb.Execute "UPDATE tblFormData SET tblFormData.FrmRecordSource = 'Unbound Form' " _
    & "WHERE FrmRecordSource Is Null Or FrmRecordSource=''"
    Dale Houston, TX

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    fixed it

    If TypeName(ctl) = "Subform"
    Dale Houston, TX

Posting Permissions

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