writing control names on a form to a table

[focosi]

I need to create a table with names of controls on my forms.
I'll use that table to apply user rules.


Any solution to write the table automatically using VBA code ?
I know how to populate a combobox...

DoCmd.OpenForm YourFormName, acNormal, , , , acHidden
Dim c As Control
For Each c In YourFormName.Controls
[Control-Name].RowSource = [Control-Name].RowSource & c.Name & ";"
Next c

..., but not how to write a table.
Thanks!

[apr pillai]

Most probably this may be a one time operation and can be done manually without going for VBA.

Since, you asked for it here is a sample routine:

Public Function SaveFldNames(ByVal frmName As String)
Dim db As Database, ctrl As Control, sctn As Section
Dim rst As Recordset, frmActive As Form
Dim frmctrlName

Set db = CurrentDb
Set frmActive = Forms(frmName)
Set sctn = frmActive.Section(acDetail)
Set rst = db.OpenRecordset("Table1Fields") 'with 2 fields: ID, FieldNames

For Each ctrl In sctn.Controls
   frmctrlName = ctrl.Name
   ctrlType = ctrl.Properties(3).Name
   If ctrlType = "ControlSource" Then
       rst.AddNew
       rst![FieldNames] = frmctrlName
       rst.Update
   End If
Next
rst.Close


Set rst = Nothing
Set db = Nothing
Set frmActive = Nothing
Set sctn = Nothing

End Function

Keep the Form open and call the above Function with the Form Name as Parameter, from a Command Button click.

Before that create a table with the fields: ID, FIELDNAMES for writing out the field names from the Form.

The controls on the Details Section of the Form only checked. You may have different type of controls on the Details Section of the Form like Labels. These are ignored and only controls with ControlSource property are written into the target table.

I have used a Table with the name Table1Fields for writing out field names and ID field is added as an Autonumber field.

If all the fields from the Record Source Table/Query are present on the Form then it is easier to open the TableDef of the SourceTable/Query, read and transfer the Field Names into the target table rather than scanning and finding required controls from the jungle of other controls on the form.