How to view a collection as a recordset

[Seth Schrock]

Is there a way to view all the items in a collection in a form? For example, lets say that I wanted to have a list of all the controls on a given form and be able to set properties for those controls through the use of a form. Do I just need to loop through the collection and add the names of the controls to a temp table and then find the control name through a variable when I need to get to its properties? I kind of think that I know how to do this, but I've never done it before.

[ADezii]

Seth, I am a little confused as to the exact nature of your question, but I will give it a shot. The following Code will:

1. Loop through all Controls on a Form.
2. Filter out only Command Buttons.
3. Dynamically adjust their Widths based on a Scale Factor contained within a Text Box on a Form.

Dim ctl As Control
Dim sngScaleFactor As Single
 
If IsNull(Me![txtScale]) Then Exit Sub
 
sngScaleFactor = Me![txtScale]
 
'Need to set some Ranges on the Scale Value and if they are out of
'a specified Range, maintain the existing Width
If sngScaleFactor < 0.25 Or sngScaleFactor > 2 Then
  sngScaleFactor = 1        'Maintain existing Width
End If
 
For Each ctl In Me.Controls
  If ctl.ControlType = acCommandButton Then
    ctl.Width = ctl.Width * sngScaleFactor
  End If
Next

You can also manipulate a Control's Properties using a Variable referencing the Control:

Dim strControlName As String
 
strControlName = "cmdSearch"
 
'Increase Control's Width by 50%
Me.Controls(strControlName).Width = Me.Controls(strControlName).Width * 1.5

Am I even close? (LOL).

[Seth Schrock]

You are sort of close :) What sparked this question is in learning SQL Server, I have learned that it has default system views that lets you query all kinds of information that is similar to information that Access has in collections. For example, I could run a query to find all the table names or the fields in a given table. Because I can query this, I can view this information in a form (continuous or datasheet). I looked at the system tables for Access and I can't find any information like a list of controls for a given form. So I was wondering if I could somehow convert a collection (such as Me.Controls) to recordset so that I can then view the data that is in the collection in a form and have a field that would allow me to change the tag property for instance. This would allow my users (who I have set as administrators) to edit some properties without having to touch VBA.

I hope this makes it clearer.

[Oralloy]

Seth,

The quick way of converting is to simply create a temporary table and insert every value of the collection into the table.

Of course, if I am reading what you are writing correctly, it seems that your intent is to have a list of existing controls, with a one-to-many relationship to the attributes for each of the controls. Perhaps it is best not to bother with a temporary table; instead, have two perminant tables with the associated forms already in the database - then when you want to edit properties for a specific control(s), simply insert the information into the existing tables.

What do you think?

Regards,
Oralloy

[Seth Schrock]

Okay. So it sounds looping through the collection and writing the names to a table is the way to go. I think that I can do that.

Thanks Oralloy.

[ADezii]

You can write Code that will will populate a Table with the Names of all Forms as well as all the Controls associated with those Forms.

[Seth Schrock]

That is what I plan on doing.